Sql Server: Group Dates by Ranges

Question

I have an SQL table with one column (dateRec) containing dates, format: yyyy-mm-dd.

Is there a way in SQL that I can define date ranges and then group all the items by these ranges ? I would need the following groups here:

group one = 0 - 7 days old
group two = 8 - 14 days old
group three = 15 - 30 days old
group four = 31 - 60 days old
group five = rest

My standard query to fetch all items from that table:

CREATE PROCEDURE [dbo].[FetchRequests]
AS
BEGIN
    SET NOCOUNT ON;
    SELECT      subject,
                dateRec,
                category
    FROM        LogRequests
    WHERE       logStatus = 'active'
    ORDER BY    dateRec desc, subject
    FOR XML PATH('items'), ELEMENTS, TYPE, ROOT('ranks')

END

Thanks for any help with this, Tim.

Accepted Answer

You need to do something like this

select t.range as [score range], count(*) as [number of occurences]
from (
  select case 
    when score between  0 and  9 then ' 0-9 '
    when score between 10 and 19 then '10-19'
    when score between 20 and 29 then '20-29'
    ...
    else '90-99' end as range
  from scores) t
group by t.range

Check this link In SQL, how can you "group by" in ranges?

Answer 2

Yes, you can do that by adding a new column which contains all the bands you require and then group by that column:

 SELECT      subject,
            dateRec,
            category
            ,case when datediff(day,dateRec,Getdate())<='7' then '0 - 7 days old'
                  when datediff(day,dateRec,Getdate()) between  '8' and '14' then '8 - 14 days old'
                   when datediff(day,dateRec,Getdate()) >60 then 'rest'
                   end Classes
       into #temp1
   FROM        LogRequests
   WHERE       logStatus = 'active'
   ORDER BY    dateRec desc, subject

I have missed couple of your ranges, but hopefully you got the logic

then Group by this column:

  select classes,
       Count(*)
      from #temp1

     begin drop table #temp1 end

Answer 3

WITH ranges AS (
    SELECT
        range_id,
        ISNULL(DATEADD(day,range_start,CAST(GETDATE() AS date)),'0000-01-01') range_start,
        ISNULL(DATEADD(day,range_end  ,CAST(GETDATE() AS date)),'9999-12-31') range_end
    FROM (VALUES
        (1,  -7,  0),
        (2, -14, -8),
        (3, -30,-15),
        (4, -60,-31),
        (5,NULL,-61)
    ) r(range_id,range_start,range_end)
)
SELECT      subject,
            range_id,
            category
            COUNT(*) AS c,
FROM        LogRequests
WHERE       logStatus = 'active'
INNER JOIN  ranges ON dateRec BETWEEN range_start AND range_end
GROUP BY    subject,category,range_id
ORDER BY    range_id desc, subject

Sql Server: Group Dates by Ranges

3 Answers

Must Read

Your Answer

Sophia Al-Mansoor

Sql Server: Group Dates by Ranges

3 Answers

Must Read

Your Answer

Sign up or log in

Post as a guest

Sophia Al-Mansoor