Group By Date Range – Examples

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

Share:

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

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

3. Use the case statements in group by clause to group over different ranges.

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

Below example (sample) query does the complete task in detail:

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 server 2000,2005,2008,2008 r2,2012

(Visited 417 times, 1 visits today)



References : Devi Prasad (sqlserverlearner.com)

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.


Do you like my blog?

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




Write a comment



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