SQL DENSE_RANK not suitable because of existing table- how to get min/max dates in sequence when val changes

Question

I have an existing @table

ID  Date    Val
1   2014-10-01  1
2   2014-10-02  1
3   2014-10-03  1
4   2014-10-04  1
5   2014-10-05  1
6   2014-10-06  1
7   2014-10-07  1
8   2014-10-08  1
9   2014-10-09  1

The Date sequence is of importance. I need to see the first and last date for each Val sequence:

Select Val,MIN([Date]) as A, MAX([Date]) as B
        from @T
        Group by Val

   Val  From        To
    1   2014-10-01  2014-10-09

The Val column now changes for 3 of these entries:

Update @T set Val = 2 where [ID] between 3 and 5

and returns:

ID  Date    Val
1   2014-10-01  1
2   2014-10-02  1
3   2014-10-03  2
4   2014-10-04  2
5   2014-10-05  2
6   2014-10-06  1
7   2014-10-07  1
8   2014-10-08  1
9   2014-10-09  1

How do I get SQL to return the min/max dates per sequence? I need to show :
i.e.

1   2014-10-01  2014-10-02
2   2014-10-03  2014-10-05
1   2014-10-06  2014-10-09

If I run the normal Min/Max query, I get

1   2014-10-01  2014-10-09
2   2014-10-03  2014-10-05

Which does not show me that the val was 1 for 1st period,2 for 2nd period and again 1 for 3rd period

declare @T table(ID int,[Date] date,Val int)
Insert Into @T(ID,[Date],Val)
 values(1,'2014/10/01', 1),
(2,'2014/10/02',    1),
(3,'2014/10/03',    1),
(4,'2014/10/04',    1),
(5,'2014/10/05',    1),
(6,'2014/10/06',    1),
(7,'2014/10/07',    1),
(8,'2014/10/08',    1),
(9,'2014/10/09',    1)

ElPresidente ElPresidente · Accepted Answer · 2015-04-09T14:47:47

Try this

SELECT Val,Min(Date) Min_Date,Max(Date) Max_Date 
FROM
(
SELECT ID,Date,Val,SUM(NewVal) OVER (order by ID) AS NewVal
FROM
    (
    SELECT ID,Date,Val,CASE WHEN Val<>LAG(Val, 1) OVER (ORDER BY ID) THEN 1 ELSE 0 END NewVal
    FROM @T
    ) I
) O
GROUP BY NewVal,Val
ORDER BY Min_Date

I adopted the answer from a previous answer to one of my own questions https://stackoverflow.com/a/21635529/1181412

Basically you're using the LAG function to compare the value of the previous row. When it's different you make it a 1, otherwise you use 0. Then you wrap that in a running sum which only increments every time NewVal goes up by 1. The result of the query matches what you're looking for.

The order of the records is obviously very important to this answer. So depending on your specific needs you may need to adjust the OVER clauses.

Edit

Per the comments this is an alternative answer which doesn't use LAG so it works in SQL Server 2008.

SELECT Val,Min(Date) Min_Date,Max(Date) Max_Date 
FROM
(
SELECT ID,Date,Val,SUM(NewVal) OVER (order by ID) AS NewVal
FROM
    (
    SELECT M.ID,M.Date,M.Val,CASE WHEN M.Val<>L.Val THEN 1 ELSE 0 END NewVal
    FROM @T M
    LEFT JOIN
        (
        SELECT ID,Date,Val,ROW_NUMBER() OVER(order by ID)+1 NewRowId
        FROM @T
        ) L ON M.ID=L.NewRowId
    ) I
) O
GROUP BY NewVal,Val
ORDER BY Min_Date

SQL DENSE_RANK not suitable because of existing table- how to get min/max dates in sequence when val changes

1 Answers