SQL Server turn Series into a Range Has anyone found a great way to convert individual rows to date ranges for a specific value. In this case, I'm looking to get a date range for each code in my given table.

Date        Code
1/1/2017    F
1/2/2017    F
1/3/2017    F
1/4/2017    R
1/5/2017    P
1/6/2017    P
1/7/2017    P
1/8/2017    P


My result should be similar to the following.

From        To          Code
1/1/2017    1/3/2017    F
1/4/2017    1/4/2017    R
1/5/2017    1/8/2017    P
Assuming there are no gaps in the range of dates for a particular code, a simple aggregation query would solve this:

DECLARE @TestData TABLE (
    DateInfo DATE
    ,Code VARCHAR(1)
    )

insert into @TestData (DateInfo,Code)
values
('1/1/2017',    'F'),
('1/2/2017' ,   'F'),
('1/3/2017' ,   'F'),
('1/4/2017' ,   'R'),
('1/5/2017' ,   'P'),
('1/6/2017' ,   'P'),
('1/7/2017' ,   'P'),
('1/8/2017' ,   'P')

;
WITH TestData
AS (
    SELECT min(DateInfo) AS 'From'
        ,max(DateInfo) AS 'To'
        ,Code
    FROM @TestData
    GROUP BY code
    )
SELECT convert(VARCHAR(10), [From], 101) AS 'From'
    ,convert(VARCHAR(10), [To], 101) AS 'To'
    ,Code
FROM TestData
ORDER BY [From]


If the dates for a particular 'code' in your example are not consecutive, then you have what is known as a 'Gaps and Island' problem.  I took this example and tweaked it for your situation.  I added a new row for code 'F' with a date of 1/9/2017 so you could see how the same code could have multiple date ranges.

DECLARE @TestData TABLE (
    DateInfo DATE
    ,Code VARCHAR(1)
    )

insert into @TestData (DateInfo,Code)
values
('1/1/2017',    'F'),
('1/2/2017' ,   'F'),
('1/3/2017' ,   'F'),
('1/4/2017' ,   'R'),
('1/5/2017' ,   'P'),
('1/6/2017' ,   'P'),
('1/7/2017' ,   'P'),
('1/8/2017' ,   'P'),
('1/9/2017' ,   'F')
;
;

WITH mycte
AS (
    SELECT *
        ,DATEADD(DAY, - ROW_NUMBER() OVER (
                PARTITION BY code ORDER BY [DateInfo]
                ), [DateInfo]) AS grp
    FROM @TestData
    )


--select * from mycte       --Uncomment to see the data from mycte


SELECT Code
    ,min([DateInfo]) AS [From]
    ,max([DateInfo]) AS [To]
FROM mycte
GROUP BY Code
    ,grp
ORDER BY [From]


The ROW_NUMBER() window function in this example creates 'partitions' of each code and assigns a row number for each row in each 'partition'.  A new 'code' starts the row number back to 1 for that partition.

The DateAdd logic takes the source date from your table, subtracts 'its' row number as 'days' and creates a 'grp' column that we can use to GROUP BY when we select from mycte.  

To get a better understanding of what the code with the ROW_NUMBER() window function is doing, you can uncomment the line that says uncomment to see mycte and just run down the that select statement.  It returns the following result - (Notice that the unbroken date sequences for each 'code' have the exact same 'grp' data.)



Now, it just a matter of selecting the MIN as [From] and MAX as [To] for each 'grp' and order appropriately to give you the final result.