[SQL Server 2005] Group by between interval dates

cms9651 · August 29, 2012, 9:35am

Hi all, I have one question for you.

In SQL Server it’s possible GROUP BY between interval dates ?

I tried this, but I have error.

Thanks in advance for your help.

SELECT
	[DATE-E],
             [TYPE-V]
FROM
	dbo_40
WHERE
	1 = 1
AND [DATA-E] BETWEEN '2012-07-04'
AND '2012-07-31'
AND ([TYPE-V] = '5')
GROUP BY
	[DATE-E] BETWEEN '2012-07-04'
AND '2012-07-31,
[TYPE-V];

cpradio · August 29, 2012, 11:25am

I may be wrong, but I would think this is where you would want to use HAVING

CREATE TABLE #dbo_40
(
  [DATA-E] datetime,
  [DATE-E] datetime,
  [TYPE-V] varchar(1)
)

INSERT INTO #dbo_40 VALUES ('2012-07-03', '2012-07-03','4')
INSERT INTO #dbo_40 VALUES ('2012-07-04', '2012-07-04','4')
INSERT INTO #dbo_40 VALUES ('2012-07-31', '2012-07-31','4')
INSERT INTO #dbo_40 VALUES ('2012-08-01', '2012-08-01','4')
INSERT INTO #dbo_40 VALUES ('2012-07-03', '2012-07-03','5')
INSERT INTO #dbo_40 VALUES ('2012-07-04', '2012-07-04','5')
INSERT INTO #dbo_40 VALUES ('2012-07-05', '2012-07-05','5')
INSERT INTO #dbo_40 VALUES ('2012-07-06', '2012-07-06','5')
INSERT INTO #dbo_40 VALUES ('2012-07-07', '2012-07-07','5')
INSERT INTO #dbo_40 VALUES ('2012-07-08', '2012-07-08','5')
INSERT INTO #dbo_40 VALUES ('2012-07-09', '2012-07-09','5')
INSERT INTO #dbo_40 VALUES ('2012-07-10', '2012-07-10','5')
INSERT INTO #dbo_40 VALUES ('2012-07-11', '2012-07-11','5')
INSERT INTO #dbo_40 VALUES ('2012-07-12', '2012-07-12','5')
INSERT INTO #dbo_40 VALUES ('2012-07-13', '2012-07-13','5')
INSERT INTO #dbo_40 VALUES ('2012-07-14', '2012-07-14','5')
INSERT INTO #dbo_40 VALUES ('2012-07-15', '2012-07-15','5')
INSERT INTO #dbo_40 VALUES ('2012-07-16', '2012-07-16','5')
INSERT INTO #dbo_40 VALUES ('2012-07-17', '2012-07-17','5')
INSERT INTO #dbo_40 VALUES ('2012-07-18', '2012-07-18','5')
INSERT INTO #dbo_40 VALUES ('2012-07-19', '2012-07-19','5')
INSERT INTO #dbo_40 VALUES ('2012-07-20', '2012-07-20','5')
INSERT INTO #dbo_40 VALUES ('2012-07-21', '2012-07-21','5')
INSERT INTO #dbo_40 VALUES ('2012-07-22', '2012-07-22','5')
INSERT INTO #dbo_40 VALUES ('2012-07-23', '2012-07-23','5')
INSERT INTO #dbo_40 VALUES ('2012-07-24', '2012-07-24','5')
INSERT INTO #dbo_40 VALUES ('2012-07-25', '2012-07-25','5')
INSERT INTO #dbo_40 VALUES ('2012-07-26', '2012-07-26','5')
INSERT INTO #dbo_40 VALUES ('2012-07-27', '2012-07-27','5')
INSERT INTO #dbo_40 VALUES ('2012-07-28', '2012-07-28','5')
INSERT INTO #dbo_40 VALUES ('2012-07-29', '2012-07-29','5')
INSERT INTO #dbo_40 VALUES ('2012-07-30', '2012-07-30','5')
INSERT INTO #dbo_40 VALUES ('2012-07-31', '2012-07-31','5')
INSERT INTO #dbo_40 VALUES ('2012-08-01', '2012-08-01','5')
INSERT INTO #dbo_40 VALUES ('2012-07-03', '2012-07-03','6')
INSERT INTO #dbo_40 VALUES ('2012-07-04', '2012-07-04','6')
INSERT INTO #dbo_40 VALUES ('2012-07-31', '2012-07-31','6')
INSERT INTO #dbo_40 VALUES ('2012-08-01', '2012-08-01','6')


SELECT 
	[DATE-E],
    [TYPE-V]
FROM
	#dbo_40
WHERE
	1 = 1
AND [DATA-E] BETWEEN '2012-07-04'
AND '2012-07-31'
AND ([TYPE-V] = '5')
GROUP BY
         [DATE-E], [TYPE-V]
HAVING [DATE-E] BETWEEN '2012-07-04'
AND '2012-07-31';

DROP TABLE #dbo_40

r937 · August 29, 2012, 11:33am

cp. you don’t need that HAVING clause, since it merely repeats your WHERE clause

SELECT [DATE-E]
     , [TYPE-V]
  FROM dbo_40
 WHERE 1 = 1
   AND [DATA-E] BETWEEN '2012-07-04' AND '2012-07-31'
   AND [TYPE-V] = '5'
GROUP
    BY [DATE-E]
     , [TYPE-V]

cpradio · August 29, 2012, 11:38am

r937:

cp. you don’t need that HAVING clause, since it merely repeats your WHERE clause
SELECT [DATE-E]
     , [TYPE-V]
  FROM dbo_40
 WHERE 1 = 1
   AND [DATA-E] BETWEEN '2012-07-04' AND '2012-07-31'
   AND [TYPE-V] = '5'
GROUP 
    BY [DATE-E]
     , [TYPE-V]

I thought that too, until I ran the query on a temp table that only had DATA-E and TYPE-V, then I got the error, DATE-E does not exist. DATE-E is in the BETWEEN statement he had in the GROUP BY, so I think HAVING is indeed needed.

cms9651 · August 29, 2012, 11:41am

thanks for replies, but I cannot group by between interval dates…

SELECT [DATE-E]
     , [TYPE-V]
  FROM dbo_40
 WHERE 1 = 1
   AND [DATE-E] BETWEEN '2012-07-04' AND '2012-07-31'
   AND [TYPE-V] = '5'
GROUP
    BY [DATE-E]
     , [TYPE-V]
HAVING [DATE-E] BETWEEN '2012-07-04'
AND '2012-07-31';

cpradio · August 29, 2012, 11:49am

Can you provide sample data and the expected result?

cms9651 · August 29, 2012, 12:07pm

This is my output:

DATE-E			TYPE-V
2012-07-04 00.00.00	5
2012-07-05 00.00.00	5
2012-07-06 00.00.00	5
2012-07-08 00.00.00	5
2012-07-09 00.00.00	5
2012-07-10 00.00.00	5
2012-07-11 00.00.00	5
2012-07-12 00.00.00	5
2012-07-13 00.00.00	5
2012-07-14 00.00.00	5
2012-07-15 00.00.00	5
2012-07-16 00.00.00	5
2012-07-17 00.00.00	5
2012-07-18 00.00.00	5
2012-07-19 00.00.00	5
2012-07-20 00.00.00	5
2012-07-23 00.00.00	5
2012-07-24 00.00.00	5
2012-07-25 00.00.00	5
2012-07-26 00.00.00	5
2012-07-27 00.00.00	5
2012-07-28 00.00.00	5
2012-07-29 00.00.00	5
2012-07-30 00.00.00	5

I need this:

DATE-E-Start		DATE-E-End		TYPE-V	Total
2012-07-04 00.00.00	2012-07-30 00.00.00	5	120

thank you…

cpradio · August 29, 2012, 12:14pm

Okay, so something similar to this (may need to replace COUNT(*) with SUM([TYPE-V])

CREATE TABLE #dbo_40
(
  [DATE-E] datetime,
  [TYPE-V] varchar(1)
)

INSERT INTO #dbo_40 VALUES ('2012-07-03','4')
INSERT INTO #dbo_40 VALUES ('2012-07-04','4')
INSERT INTO #dbo_40 VALUES ('2012-07-31','4')
INSERT INTO #dbo_40 VALUES ('2012-08-01','4')
INSERT INTO #dbo_40 VALUES ('2012-07-03','5')
INSERT INTO #dbo_40 VALUES ('2012-07-04','5')
INSERT INTO #dbo_40 VALUES ('2012-07-05','5')
INSERT INTO #dbo_40 VALUES ('2012-07-06','5')
INSERT INTO #dbo_40 VALUES ('2012-07-07','5')
INSERT INTO #dbo_40 VALUES ('2012-07-08','5')
INSERT INTO #dbo_40 VALUES ('2012-07-09','5')
INSERT INTO #dbo_40 VALUES ('2012-07-10','5')
INSERT INTO #dbo_40 VALUES ('2012-07-11','5')
INSERT INTO #dbo_40 VALUES ('2012-07-12','5')
INSERT INTO #dbo_40 VALUES ('2012-07-13','5')
INSERT INTO #dbo_40 VALUES ('2012-07-14','5')
INSERT INTO #dbo_40 VALUES ('2012-07-15','5')
INSERT INTO #dbo_40 VALUES ('2012-07-16','5')
INSERT INTO #dbo_40 VALUES ('2012-07-17','5')
INSERT INTO #dbo_40 VALUES ('2012-07-18','5')
INSERT INTO #dbo_40 VALUES ('2012-07-19','5')
INSERT INTO #dbo_40 VALUES ('2012-07-20','5')
INSERT INTO #dbo_40 VALUES ('2012-07-21','5')
INSERT INTO #dbo_40 VALUES ('2012-07-22','5')
INSERT INTO #dbo_40 VALUES ('2012-07-23','5')
INSERT INTO #dbo_40 VALUES ('2012-07-24','5')
INSERT INTO #dbo_40 VALUES ('2012-07-25','5')
INSERT INTO #dbo_40 VALUES ('2012-07-26','5')
INSERT INTO #dbo_40 VALUES ('2012-07-27','5')
INSERT INTO #dbo_40 VALUES ('2012-07-28','5')
INSERT INTO #dbo_40 VALUES ('2012-07-29','5')
INSERT INTO #dbo_40 VALUES ('2012-07-30','5')
INSERT INTO #dbo_40 VALUES ('2012-07-31','5')
INSERT INTO #dbo_40 VALUES ('2012-08-01','5')
INSERT INTO #dbo_40 VALUES ('2012-07-03','6')
INSERT INTO #dbo_40 VALUES ('2012-07-04','6')
INSERT INTO #dbo_40 VALUES ('2012-07-31','6')
INSERT INTO #dbo_40 VALUES ('2012-08-01','6')


SELECT 
	MIN([DATE-E]) AS 'DATE-E Start',
	MAX([DATE-E]) AS 'DATE-E End',
    [TYPE-V],
    COUNT(*) AS 'Total'
FROM
	#dbo_40
WHERE
	1 = 1
AND [DATE-E] BETWEEN '2012-07-04'
AND '2012-07-31'
AND ([TYPE-V] = '5')
GROUP BY
	[TYPE-V]

DROP TABLE #dbo_40

Also, it will not necessary write 2012-07-04 and 2012-07-31 as the start and end dates, it will write the lowest date and the highest date it found in your between range, so if you don’t have entries for 2012-07-31, you may see 2012-07-30 as the end date (you can correct this by hard coding the dates like so)

SELECT 
	'2012-07-04' AS 'DATE-E Start',
	'2012-07-31' AS 'DATE-E End',
    [TYPE-V],
    COUNT(*) AS 'Total'
FROM
	#dbo_40
WHERE
	1 = 1
AND [DATE-E] BETWEEN '2012-07-04'
AND '2012-07-31'
AND ([TYPE-V] = '5')
GROUP BY
	[TYPE-V]

cms9651 · August 29, 2012, 12:17pm

thanks a lot!