CODEWITHSUNDEEP
sqlsql-server-2008group-by
Diskdrive
Diskdrive

Reputation: 18825

How to group by date range in SQL

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?

Upvotes: 2

Views: 12995

Answers (2)

Natalia
Natalia

Reputation: 311

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)

Upvotes: 1

billinkc
billinkc

Reputation: 61201

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

Upvotes: 1

Related Questions