How to group by date range in SQL

Question

Drawing on from this other post of StackOverflow.com which I found useful.

I was wondering if it is possible to group by using date ranges that I don't explicitly define.

So to clarify, say I have a table called Person and a column called Date Of Birth.

I would like to group by and count the number of people born in a period.

So it would look something like this.

Born Count

Mar1980 25

Apr1980 452

May1980 42

...

Dec1993 452

The period could be weeks, months and quarters.

If I used the other solution, I would need to specify every single date range, even with months and years, it would take a long time as people's date of birth vary a lot.

select t.DOB as [dob], count(*) as [number of occurences]
from (
      select user_id,
         case when date >= '1980-01-01' and date < '1980-02-01 then 'Jan 1980'
         when date >= '1980-02-01' and date < '1980-03-01 then 'Feb 1980'
         ...
         when date >= '1990-03-01' and date < '1990-04-01 then 'Mar 1990'
        else 'Null' end as DOB
     from Person) t
group by t.DOB

Is there any solution you can think of that can allow me to specify just the size of the interval I want the data ranges to be and group by these date ranges?

Your example query makes no sense. What are `date` and `score`? What do the columns represent, and what are their datatypes? What you want is possible, but your question isn't answerable without some of the DDL and sample data. — Ken White, Jun 15 '11 at 00:45
@Ken : the thing I pasted there is an adaptation of the solution on the link. I left a few things out, I'll modify that. But look at the table above that for how I'm planning to display the data (the table with "Born" and "Count" as the columns. — Diskdrive, Jun 15 '11 at 00:59
I appreciate that you've shown the output. It's impossible to provide the solution, though, without knowing what the *input* data looks like - think of it this way: "Tell me how I can build a house that looks like mansion, but I'm not going to tell you what materials or resources I have to build it." Also, your question should be as stand-alone as possible, so it's not dependent on other links; that way it's searchable and can serve as a question with solutions on it's own. — Ken White, Jun 15 '11 at 01:13
@Ken : oh ok... The input is a table called Person, with a column called DateOfBirth which is in datetime2 format. Is that what you were asking? — Diskdrive, Jun 15 '11 at 01:15
How to Group By Date Range step by step using Examples can be found in this link http://sqlserverlearner.com/2012/group-by-date-range-examples — , May 02 '12 at 07:37

billinkc · Accepted Answer · 2011-06-15T01:40:43.443

Would something like this address what you're after? The trick is to use and abuse the CONVERT function to get your time periods to roll together nicely and then use DENSE_RANK to reduce it to a monotonically increase sequence .

WITH DUMMY_DATA AS
(
-- use a convert with a type of 112 to coerce dates to
-- YYYY-MM format and truncate days
-- Be creative, use to generate whatever bands you desire
SELECT CONVERT(char(7), '2011-06-01', 112) AS period, 'stuff' AS junk
UNION ALL SELECT CONVERT(char(7), '2011-05-01', 112) AS period, 'more stuf' AS junk
UNION ALL SELECT CONVERT(char(7), '2011-05-01', 112) AS period, 'more stuf1' AS junk
UNION ALL SELECT CONVERT(char(7), '2011-05-01', 112) AS period, 'more stuf2' AS junk
UNION ALL SELECT CONVERT(char(7), '2011-05-01', 112) AS period, 'more stuf3' AS junk
UNION ALL SELECT CONVERT(char(7), '2011-05-01', 112) AS period, 'more stu4f' AS junk
UNION ALL SELECT CONVERT(char(7), '2011-05-01', 112) AS period, 'more stuf5' AS junk
UNION ALL SELECT CONVERT(char(7), '2011-05-01', 112) AS period, 'more stuf7' AS junk
UNION ALL SELECT CONVERT(char(7), '2011-05-01', 112) AS period, 'more stuf8' AS junk
UNION ALL SELECT CONVERT(char(7), '2011-05-01', 112) AS period, 'more stuf9' AS junk
UNION ALL SELECT CONVERT(char(7), '2011-05-01', 112) AS period, 'more stuf0' AS junk
)
, BANDS AS
(
-- Using our data, create bands in case you want to look
-- at groupings 
SELECT
    D.*
,   DENSE_RANK() OVER (ORDER BY D.period ASC) AS banding
FROM
    DUMMY_DATA D
)
, ROLLUPS AS
(
-- ROll up based on our banding
SELECT
    B.period
,   B.banding
,   COUNT(1) AS row_count
FROM
    BANDS B
GROUP BY
    B.period
,   B.banding

) SELECT * FROM ROLLUPS R

Using the above, I get output like

[period]   [band]  [row_count]
2011-05    1       10
2011-06    2       1

that's fantastic! took me a while to understand how it worked, and I still have to look up what DENSE_RANK does, but it's exactly what I am looking for — Diskdrive, Jun 15 '11 at 01:33
Sorry, I will edit the response with a link to MSDN for DENSE_RANK but it generates a new number for each row where there thing specified in the OVER clause. Since the period is the same, they all get the same number associated to them with no gaps between. I find it's a handy way to handle questions like display data from 4-6 weeks ago. — billinkc, Jun 15 '11 at 01:38
ah ok, it increments 1 to every unique value it finds in the column specified in the OVER clause. Cool, learnt two new things today. — Diskdrive, Jun 15 '11 at 01:53

score 1 · Answer 2 · answered Jun 15 '11 at 06:44

You could probably use something like that and take advantage of function DATEPART

Please. don't forget to uncomment the statement you want to test.

WITH DummyTable AS (
SELECT '05/01/2011' AS DateOfBirth, 'Peter' AS CustomerName UNION ALL
SELECT '06/02/2011' AS DateOfBirth, 'Bill' AS CustomerName UNION ALL
SELECT '05/01/2011' AS DateOfBirth, 'Charles' AS CustomerName UNION ALL
SELECT '07/13/2010' AS DateOfBirth, 'Maria' AS CustomerName UNION ALL
SELECT '01/01/2009' AS DateOfBirth, 'Theresa' AS CustomerName UNION ALL
SELECT '05/01/2011' AS DateOfBirth, 'Steven' AS CustomerName UNION ALL
SELECT '06/02/2011' AS DateOfBirth, 'Matthew' AS CustomerName UNION ALL
SELECT '07/13/2010' AS DateOfBirth, 'Rachel' AS CustomerName UNION ALL
SELECT '05/01/2011' AS DateOfBirth, 'Molly' AS CustomerName UNION ALL
SELECT '07/13/2010' AS DateOfBirth, 'Alex' AS CustomerName UNION ALL
SELECT '08/01/2009' AS DateOfBirth, 'John' AS CustomerName UNION ALL
SELECT '07/13/2010' AS DateOfBirth, 'Ann' AS CustomerName UNION ALL
SELECT '05/01/2011' AS DateOfBirth, 'Jay' AS CustomerName
) 
--By Month

--SELECT DATENAME(month, DateOfBirth)+DATENAME(year, dateofBirth), COUNT(*)
--FROM DummyTable
--GROUP BY DATENAME(month, DateOfBirth)+DATENAME(year, dateofBirth)


--BY Quarter

--SELECT CAST(DATEPART(QUARTER, DateOfBirth) AS Varchar)+' quarter '+DATENAME(year, dateofBirth), COUNT(*)
--FROM DummyTable
--GROUP BY CAST(DATEPART(QUARTER, DateOfBirth) AS Varchar)+' quarter '+DATENAME(year, dateofBirth)

How to group by date range in SQL

2 Answers2