{"id":186,"date":"2012-05-02T07:22:03","date_gmt":"2012-05-02T07:22:03","guid":{"rendered":"https:\/\/sqlserverlearner.azurewebsites.net\/2012\/05\/02\/group-by-date-range-examples\/"},"modified":"2012-05-02T07:22:03","modified_gmt":"2012-05-02T07:22:03","slug":"group-by-date-range-examples","status":"publish","type":"post","link":"https:\/\/sqlserverlearner.com\/2012\/05\/02\/group-by-date-range-examples\/","title":{"rendered":"Group By Date Range – Examples"},"content":{"rendered":"

In many real time scenarios data is needed grouped by the date range for date (or) datetime columns.<\/p>\n

This post explains in detail as to how to group data over date range in sql server.<\/p>\n

Steps to group over different date ranges.<\/p>\n

1. Identify the different date ranges<\/strong><\/p>\n

\nFor example:<\/p>\n

Data can be grouped by the below date ranges.(In YEAR-MONTH-DATE)<\/p>\n

2011-01-01 TO 2011-05-31 Call it range 1<\/p>\n

2011-01-01 TO 2011-12-31 Call it range 2<\/p>\n

2012-01-01 TO 2012-05-31 Call it range 3<\/p>\n

Other date ranges – Call it range 4\n<\/dd>\n

2. Build case statements for each date range<\/strong>
\n[sql]
\nCASE
\n 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
\n 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
\n 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
\n ELSE 4
\nEND AS DateRange
\n[\/sql]<\/p>\n

3. Use the case statements in group by clause to group over different ranges.<\/strong>
\n[sql]
\nGROUP BY CASE
\n 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
\n 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
\n 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
\n ELSE 4
\nEND
\n[\/sql]<\/p>\n

Below example (sample) query does the complete task in detail:<\/strong>
\n[sql]
\nCREATE TABLE #TestSales
\n(
\n CustomerName VARCHAR (100),
\n Product VARCHAR (100),
\n DateTimeSold DATETIME
\n)<\/p>\n

GO
\nINSERT INTO #TestSales
\nSELECT ‘X’,
\n ‘Product1’,
\n ‘2011-05-01 00:00:00.000’
\nUNION ALL
\nSELECT ‘X’,
\n ‘Product2’,
\n ‘2011-06-01 00:00:00.000’
\nUNION ALL
\nSELECT ‘X’,
\n ‘Product3’,
\n ‘2011-06-01 00:00:00.000’
\nUNION ALL
\nSELECT ‘X’,
\n ‘Product1’,
\n ‘2012-01-01 00:00:00.000’
\nUNION ALL
\nSELECT ‘X’,
\n ‘Product1’,
\n ‘2012-02-01 00:00:00.000’
\nUNION ALL
\nSELECT ‘X’,
\n ‘Product3’,
\n ‘2012-02-01 00:00:00.000’
\nUNION ALL
\nSELECT ‘X’,
\n ‘Product1’,
\n ‘2012-03-01 00:00:00.000’
\nUNION ALL
\nSELECT ‘X’,
\n ‘Product2’,
\n ‘2012-03-01 00:00:00.000’
\nUNION ALL
\nSELECT ‘X’,
\n ‘Product2’,
\n ‘2012-03-01 00:00:00.000’
\nUNION ALL
\nSELECT ‘X’,
\n ‘Product2’,
\n ‘2012-04-01 00:00:00.000’<\/p>\n

GO
\nSELECT count(*) AS NumberOfSales,
\n CASE
\nWHEN 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
\nWHEN 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
\nWHEN 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
\nEND AS DateRange
\nFROM #TestSales
\nGROUP BY CASE
\nWHEN 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
\nWHEN 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
\nWHEN 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
\nEND
\n[\/sql]<\/p>\n

sql server 2000,2005,2008,2008 r2,2012<\/p>\n","protected":false},"excerpt":{"rendered":"

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…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[41,1789],"tags":[1850,1851,1852,1853,1854,1855,1856,1857,1858,1859,1860,1861,1862],"class_list":["post-186","post","type-post","status-publish","format-standard","hentry","category-sql-server-basics","category-t-sql-code-examples","tag-group-by-date-range-in-detail-with-exaples","tag-group-by-date-range-example-sql-server-2000","tag-group-by-date-range-example-sql-server-2005","tag-group-by-date-range-example-sql-server-2008","tag-group-by-date-range-example-sql-server-2008-r2","tag-group-by-date-range-example-sql-server-2012","tag-group-by-date-range-examples","tag-how-to-group-by-date-range-in-sql-server","tag-how-to-group-by-date-range-in-sql-server-2008","tag-sql-group-by-date-range","tag-sql-group-by-date-ranges","tag-sql-query-group-by-date-range","tag-sql-server-group-by-date-range"],"_links":{"self":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/186","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/comments?post=186"}],"version-history":[{"count":0,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/186\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/media?parent=186"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/categories?post=186"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/tags?post=186"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}