Group By Date Range – Examples

Posted on : 02-05-2012 | By : Devi Prasad | In : SQL Server Basics, T-SQL Code Examples

0



In many real time scenarios data is needed grouped by the date range for date (or) datetime columns.

This post explains in detail as to how to group data over date range in sql server.

Steps to group over different date ranges.

1. Identify the different date ranges

For example:

Data can be grouped by the below date ranges.(In YEAR-MONTH-DATE)

2011-01-01 TO 2011-05-31 Call it range 1

2011-01-01 TO 2011-12-31 Call it range 2

2012-01-01 TO 2012-05-31 Call it range 3

Other date ranges – Call it range 4

2. Build case statements for each date range
[sql]
CASE
WHEN DateTimeSold BETWEEN CAST (‘2011-01-01 00:00:00.000’ AS DATETIME) AND CAST (‘2011-05-31 00:00:00.000’ AS DATETIME) THEN 1
WHEN DateTimeSold BETWEEN CAST (‘2011-06-01 00:00:00.000’ AS DATETIME) AND CAST (‘2011-12-31 00:00:00.000’ AS DATETIME) THEN 2
WHEN DateTimeSold BETWEEN CAST (‘2012-01-01 00:00:00.000’ AS DATETIME) AND CAST (‘2012-03-31 00:00:00.000’ AS DATETIME) THEN 3
ELSE 4
END AS DateRange
[/sql]

3. Use the case statements in group by clause to group over different ranges.
[sql]
GROUP BY CASE
WHEN DateTimeSold BETWEEN CAST (‘2011-01-01 00:00:00.000’ AS DATETIME) AND CAST (‘2011-05-31 00:00:00.000’ AS DATETIME) THEN 1
WHEN DateTimeSold BETWEEN CAST (‘2011-06-01 00:00:00.000’ AS DATETIME) AND CAST (‘2011-12-31 00:00:00.000’ AS DATETIME) THEN 2
WHEN DateTimeSold BETWEEN CAST (‘2012-01-01 00:00:00.000’ AS DATETIME) AND CAST (‘2012-03-31 00:00:00.000’ AS DATETIME) THEN 3
ELSE 4
END
[/sql]

Below example (sample) query does the complete task in detail:
[sql]
CREATE TABLE #TestSales
(
CustomerName VARCHAR (100),
Product VARCHAR (100),
DateTimeSold DATETIME
)

GO
INSERT INTO #TestSales
SELECT ‘X’,
‘Product1’,
‘2011-05-01 00:00:00.000’
UNION ALL
SELECT ‘X’,
‘Product2’,
‘2011-06-01 00:00:00.000’
UNION ALL
SELECT ‘X’,
‘Product3’,
‘2011-06-01 00:00:00.000’
UNION ALL
SELECT ‘X’,
‘Product1’,
‘2012-01-01 00:00:00.000’
UNION ALL
SELECT ‘X’,
‘Product1’,
‘2012-02-01 00:00:00.000’
UNION ALL
SELECT ‘X’,
‘Product3’,
‘2012-02-01 00:00:00.000’
UNION ALL
SELECT ‘X’,
‘Product1’,
‘2012-03-01 00:00:00.000’
UNION ALL
SELECT ‘X’,
‘Product2’,
‘2012-03-01 00:00:00.000’
UNION ALL
SELECT ‘X’,
‘Product2’,
‘2012-03-01 00:00:00.000’
UNION ALL
SELECT ‘X’,
‘Product2’,
‘2012-04-01 00:00:00.000’

GO
SELECT count(*) AS NumberOfSales,
CASE
WHEN DateTimeSold BETWEEN CAST (‘2011-01-01 00:00:00.000’ AS DATETIME) AND CAST (‘2011-05-31 00:00:00.000’ AS DATETIME) THEN 1
WHEN DateTimeSold BETWEEN CAST (‘2011-06-01 00:00:00.000’ AS DATETIME) AND CAST (‘2011-12-31 00:00:00.000’ AS DATETIME) THEN 2
WHEN DateTimeSold BETWEEN CAST (‘2012-01-01 00:00:00.000’ AS DATETIME) AND CAST (‘2012-03-31 00:00:00.000’ AS DATETIME) THEN 3 ELSE 4
END AS DateRange
FROM #TestSales
GROUP BY CASE
WHEN DateTimeSold BETWEEN CAST (‘2011-01-01 00:00:00.000’ AS DATETIME) AND CAST (‘2011-05-31 00:00:00.000’ AS DATETIME) THEN 1
WHEN DateTimeSold BETWEEN CAST (‘2011-06-01 00:00:00.000’ AS DATETIME) AND CAST (‘2011-12-31 00:00:00.000’ AS DATETIME) THEN 2
WHEN DateTimeSold BETWEEN CAST (‘2012-01-01 00:00:00.000’ AS DATETIME) AND CAST (‘2012-03-31 00:00:00.000’ AS DATETIME) THEN 3 ELSE 4
END
[/sql]

sql server 2000,2005,2008,2008 r2,2012




Reference : Devi Prasad (sqlserverlearner.com)

Do you like my blog?

If you liked reading this blog, please help spread the word by sharing this blog with your friends.



Need Help On SQL Server?

Cannot Find Solution to your problem (or) If you are looking for some help on SQL Server. Dont worry Click Here to Post your question and solve your issue.




Your Ad Here


Tags: , , , , , , , , , , , ,