SQL Server 2012 - Lead/Lag not null value comparison

Question

My Table:

Date    Col1    Col2    Col3    flag
12/29/2014  AA  NULL    NULL    NULL
12/30/2014  AA  NULL    NULL    NULL
12/31/2014  AA  NULL    NULL    NULL
1/1/2015    AA  XX      CC      NULL
1/2/2015    AA  XX      CC      NULL
1/3/2015    AA  XX      CC      NULL
1/4/2015    AA  NULL    NULL    NULL
1/5/2015    AA  NULL    NULL    NULL
1/6/2015    AA  NULL    NULL    NULL
1/7/2015    AA  NULL    NULL    NULL
1/8/2015    AA  NULL    NULL    NULL
1/9/2015    AA  XX      CC      NULL
1/10/2015   AA  XX      CC      NULL
1/10/2015   AA  XX      CC      NULL
1/11/2015   AA  NULL    NULL    NULL
1/12/2015   AA  XX      CC      NULL
1/13/2015   AA  XX      CC      NULL
1/14/2015   AA  NULL    NULL    NULL
1/15/2015   AA  NULL    NULL    NULL
1/16/2015   AA  NULL    NULL    NULL
1/17/2015   AA  NULL    NULL    NULL
1/18/2015   AA  NULL    NULL    NULL
1/19/2015   AA  NULL    NULL    NULL
1/20/2015   AA  NULL    NULL    NULL
1/21/2015   AA  NULL    NULL    NULL
1/22/2015   AA  NULL    NULL    NULL
1/23/2015   AA  NULL    NULL    NULL
1/24/2015   AA  NULL    NULL    NULL
1/25/2015   AA  NULL    NULL    NULL
1/26/2015   AA  NULL    NULL    NULL
1/27/2015   AA  NULL    NULL    NULL
1/28/2015   AA  NULL    NULL    NULL
1/29/2015   AA  XX      DD      NULL
1/30/2015   AA  XX      DD      NULL
1/31/2015   AA  NULL    NULL    NULL

When Col3 changes for the combination of Col1 and Col2 for a date then I need to update/set the flag.

Ex: Between 1/3/2015 and 1/9/2015 the value of Col3 is same (CC) so I need to set flag to 0 for all days between 1/4/2015 till 1/8/2015.

Between 1/13/2015 and 1/29/2015 value of Col3 changes from CC to DD. So I need to set flag to 1 for all days between 1/14/2015 till 1/28/2015.

It has become a brain twister for me, any expert help please?

When you say "the combination of Col1 and Col2", what does that mean? In your example, you've got two such combinations - ('AA', 'XX') and ('AA', NULL). What you go on to say further about the flag value changing doesn't really jive with that definition. — Ben Thul, Apr 18 '15 at 05:54
Let me rephrase: When Col3 changes for the combination of Col1 and Col2 (Not NULL) for a date then I need to update/set the flag. — user4802245, Apr 18 '15 at 06:00
I assume that in your real data, you've got more than one distinct value for Col2. How do you assign rows that have a NULL value for Col2 to one of the groups that doesn't? To be more explicit, let's assume that there are some rows with (Col1, Col2) = ('AA', 'YY') as well. Of the rows that have a NULL in Col2, how do I know whether they belong to ('AA', 'XX') or ('AA', 'YY')? — Ben Thul, Apr 18 '15 at 06:10
I agree, but if you see, Date is unique for a combination of col1 and col2. All I need to find out is the way to populate rows with AA,NULL with the lag and lead values for Col3 for that day so that I can compare and then set flag. — user4802245, Apr 18 '15 at 06:43

score 0 · Answer 1 · answered Apr 18 '15 at 07:40

I think you need a newFlag like this:

SELECT *,   CASE 
                WHEN Col3 IS NULL AND (PreCol3 = NextCol3) THEN 1
                WHEN Col3 IS NULL AND (PreCol3 <> NextCol3) THEN 2
                ELSE Null
            END As newFlag 
FROM (
    SELECT *
        , (SELECT TOP 1 ci.Col3 FROM yourTable ci 
            WHERE ci.[Date] = (SELECT Max(cii.[Date]) FROM yourTable cii
                                WHERE cii.Col3 IS NOT NULL AND cii.[Date] < c.[Date])) As PreCol3
         , (SELECT TOP 1 ci.Col3 FROM yourTable ci 
            WHERE ci.[Date] = (SELECT Min(cii.[Date]) FROM yourTable cii
                                WHERE cii.Col3 IS NOT NULL AND cii.[Date] > c.[Date])) As NextCol3
    FROM yourTable c
    ) yt

SQL Server 2012 - Lead/Lag not null value comparison

1 Answers1