[SOLVED] SQL Server: group dates by ranges

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

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?