SQL Server – Group Records by Different Date Range

sql server

I need to run a query against a database and group the records by different date ranges. The data type of the date field is varchar and in the format dd-MMM-yyyy e.g. 19-Aug-2016.

The date ranges are as follow: 13–18, 19–35, 36–55 and >55. This range is determined by subtracting the current date from the date stored in the field.
e.g. if current date is 19-Aug-2016 and date stored in the database field is 18-Aug-2003, the year difference will be 13. So all the date differences between 13 and 18 will be grouped together, all date differences between 19 and 35 will be grouped together etc.

Best Answer

First let me say storing dates in a character column is a horrible horrible idea. Sorts won't work correctly, any form of date calculations are going to require actually casting back to a date anyway, and best of all you probably have bad data in there.

But assuming your table isn't overly large (performance on this is going to kill you because of the cast) and you don't have any bad data. This will work

CREATE TABLE #temp (chardate varchar(11))
INSERT INTO #temp values ('19-Aug-2016'),('19-Mar-2010'),('10-Feb-1927'),
        ('10-Dec-2013'),('19-Mar-2000'),('10-Jan-1958')


SELECT CASE WHEN datediff(year,CAST(chardate as date),getdate()) < 13 THEN '<13' 
            WHEN datediff(year,CAST(chardate as date),getdate()) between 13 and 18 THEN '13-18'
            WHEN datediff(year,CAST(chardate as date),getdate()) between 19 and 35 THEN '19-35'
            WHEN datediff(year,CAST(chardate as date),getdate()) between 36 and 55 THEN '36-55'
            WHEN datediff(year,CAST(chardate as date),getdate()) > 55 THEN '>55' END AS [Range],
    COUNT(1)
FROM #temp
GROUP BY CASE WHEN datediff(year,CAST(chardate as date),getdate()) < 13 THEN '<13' 
            WHEN datediff(year,CAST(chardate as date),getdate()) between 13 and 18 THEN '13-18'
            WHEN datediff(year,CAST(chardate as date),getdate()) between 19 and 35 THEN '19-35'
            WHEN datediff(year,CAST(chardate as date),getdate()) between 36 and 55 THEN '36-55'
            WHEN datediff(year,CAST(chardate as date),getdate()) > 55 THEN '>55' END

V2.0 You'll probably get a bit better performance with this:

WITH FixDate AS (SELECT CAST(chardate as date) AS realdate
                FROM #temp),
        MyRange AS (SELECT CASE WHEN datediff(year,RealDate,getdate()) < 13 THEN '<13' 
                WHEN datediff(year,RealDate,getdate()) between 13 and 18 THEN '13-18'
                WHEN datediff(year,RealDate,getdate()) between 19 and 35 THEN '19-35'
                WHEN datediff(year,RealDate,getdate()) between 36 and 55 THEN '36-55'
                WHEN datediff(year,RealDate,getdate()) > 55 THEN '>55' END AS [Range]
                FROM FixDate)
SELECT [Range], count(1)
FROM MyRange
GROUP BY [Range]
Related Question