SQL Server: group dates by ranges
group-by, sql, sql-server, stored-procedures
Solution
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?
Problem
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.