SQL Server: group dates by ranges

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.

3 Answers

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?

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

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