I have a historical table with data like as bellow :
SK ID STATUS EFF_DT EXP_DT
1 486007909 APP 7/22/2009 8/22/2009
2 486007909 APP 8/22/2009 10/01/2009
3 486007909 CAN 10/01/2009 11/01/2009
4 486007909 CAN 11/02/2009 12/12/2009
5 486007909 APP 12/12/2009 NULL
The EXP_DT is null mean that the row is active.
I want to return a group of data each time Status changes
The expected result like as bellow :
SK ID STATUS EFF_DT EXP_DT GAP
1 486007909 APP 7/22/2009 8/22/2009 1
2 486007909 APP 8/22/2009 10/01/2009 1
3 486007909 CAN 10/01/2009 11/01/2009 2
4 486007909 CAN 11/02/2009 12/12/2009 2
5 486007909 APP 12/12/2009 NULL 3
Thanks for help !
This is classic gaps-and-islands problem.
We can solve it by using LAG to check for differences, then a windowed COUNT to get the GAP number.
You may want to add a partitioning clause, such as PARTITION BY ID
SELECT *,
GAP = COUNT(IsDiff) OVER (ORDER BY EFF_DT ROWS UNBOUNDED PRECEDING)
FROM (
SELECT *,
IsDiff = CASE WHEN LAG(STATUS, 1, '') OVER (ORDER BY EFF_DT) <> STATUS THEN 1 END
FROM YourTable t
) t
Эта статья взята из Интернета, укажите источник при перепечатке.
Если есть какие-либо нарушения, пожалуйста, свяжитесь с[email protected] Удалить.
я говорю два предложения