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.

Answer 1

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