[SOLVED] Get list of year ranges from list of years?

Get list of year ranges from list of years?

Is it possible to get a list of year ranges from a list of years?

Say there is a table like

Or like

Years
1990,1991,1992,1999,2001,2002,2015

I am not sure where to start; how can one get the ranges within those years? e.g.

1990-1992,1999,2001-2002,2015

Solution

Here is example:

SELECT *
INTO #years
FROM (VALUES 
(1990),
(1991),
(1992),
(1999),
(2001),
(2002),
(2015)) AS D(Year)

SELECT STUFF((
SELECT ',' +
    CASE 
        WHEN MIN(Year) = MAX(Year) THEN CAST(MIN(Year) AS VARCHAR(9))
        WHEN MIN(Year) <> MAX(Year) THEN CAST(MIN(Year) AS VARCHAR(4)) + '-' +CAST(MAX(Year) AS VARCHAR(4))
    END AS [text()]
FROM (
    SELECT Year
        ,Year-ROW_NUMBER() OVER (ORDER BY Year) AS rowID
    FROM #years) a
GROUP BY rowID
FOR XML PATH('')
),1,1,'');

The main idea is to find so called islands, which in this case is easy made by using ROW_NUMBER in this select:

SELECT Year ,Year-ROW_NUMBER() OVER (ORDER BY Year)

Years will be subtracted from row numbers, which will mark same "islands". Meaning, if every next year are increasing by one as row number does, we will get same result number:

YEAR  RowNR RESULT  
1999  1     1998  
2000  2     1998  
2015  3     2012

This result numbers can be later used for grouping and getting MAX and MIN values.