locked
Find the NOT NULL Lead and LAG values in a table RRS feed

  • Question

  • I have a table with three columns date,sequence and Period. I need to calculate Lead and Lag of each sequence and return Period value, but period has NULL values in it . i need to return the nearest NOT NULL Lag and nearest NOT NULL Lead of the period column for each sequence for each date. Below is the sample data and output needed.

    Sample Data:

    Date                 Sequence       Period 

    01-01-2020           1                  M1

    01-01-2020           2                  M2

    01-01-2020           3                  NULL

    01-01-2020           4                  NULL

    01-01-2020           5                  M5

    01-02-2020           1                  M1

    01-02-2020           2                  NULL

    01-02-2020           3                  M3

    01-02-2020           4                  NULL

    01-02-2020           5                  M5

    Output Needed (Should Include extra two columns with LEAD AND LAG with nearest not null values to the sequence number for each date)

     

    Date                 Sequence       Period         LEAD        LAG

    01-01-2020           1                  M1             M2           M1

    01-01-2020           2                  M2             M5           M1

    01-01-2020           3                  NULL          M5           M2

    01-01-2020           4                  NULL          M5            M2

    01-01-2020           5                  M5             M5            M2

    01-02-2020           1                  M1             M3            M1

    01-02-2020           2                  NULL          M3            M1

    01-02-2020           3                  M3              M5           M1

    01-02-2020           4                  NULL           M5           M3

    01-02-2020           5                  M5              M5            M3


    • Edited by Abhinav530 Sunday, August 9, 2020 8:35 PM
    Sunday, August 9, 2020 8:35 PM

Answers

  • Create Table #Test(Date date, Sequence int, Period varchar(2));
    Insert #Test(Date, Sequence, Period) Values
    ('01-01-2020', 1, 'M1'),
    ('01-01-2020', 2, 'M2'),
    ('01-01-2020', 3, NULL),
    ('01-01-2020', 4, NULL),
    ('01-01-2020', 5, 'M5'),
    ('01-02-2020', 1, 'M1'),
    ('01-02-2020', 2, NULL),
    ('01-02-2020', 3, 'M3'),
    ('01-02-2020', 4, NULL),
    ('01-02-2020', 5, 'M5');
    
    Select t.Date, t.Sequence, 
      IsNull(LeadVal.Period, t.Period) As Lead,
      IsNull(LagVal.Period, t.Period) As Lag
    From #Test t
    Outer Apply (Select Top 1 Period From #Test t2 Where t2.Period Is Not Null And t.Date = t2.Date And t.Sequence < t2.Sequence Order By t2.Sequence) As LeadVal
    Outer Apply (Select Top 1 Period From #Test t3 Where t3.Period Is Not Null And t.Date = t3.Date And t.Sequence > t3.Sequence Order By t3.Sequence Desc) As LagVal
    Order By Date, Sequence;
    Tom
    • Marked as answer by Abhinav530 Monday, August 10, 2020 1:17 PM
    Sunday, August 9, 2020 10:54 PM
  • ;with mycte as (
    SELECT Date, Sequence, Period,
     Cast(SUBSTRING( Min(Cast(date  as  BINARY(8)) +   Cast(Sequence  as  BINARY(4)) + Cast(Period  as  BINARY(8)) ) 
     OVER(partition by Date ORDER BY Sequence  ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ),  13, 8) as varchar(2)) PeriodFilled1
    ,Cast(SUBSTRING( Max(Cast(date  as  BINARY(8)) +   Cast(Sequence  as  BINARY(4)) + Cast(Period  as  BINARY(8)) ) 
     OVER(partition by Date ORDER BY Sequence  ROWS  UNBOUNDED PRECEDING ),  13, 8) as varchar(2)) PeriodFilled2  
     from #Test
      )
    
    Select Date, Sequence, Period 
     ,ISNULL(lead(PeriodFilled1) Over(partition by Date ORDER BY  Date, Sequence),Period)  [LEAD]
     ,ISNULL(lag(PeriodFilled2)  Over(partition by Date ORDER BY  Date, Sequence),Period)  [LAG]
    From mycte
    ORDER BY  Date , Sequence 

    • Marked as answer by Abhinav530 Monday, August 10, 2020 1:17 PM
    Monday, August 10, 2020 12:36 AM
  • Hi Abhinav,

    Please also find below method:

    drop table if exists #Test

    Create Table #Test(Date date, Sequence int, Period varchar(2)); Insert #Test(Date, Sequence, Period) Values ('01-01-2020', 1, 'M1'), ('01-01-2020', 2, 'M2'), ('01-01-2020', 3, NULL), ('01-01-2020', 4, NULL), ('01-01-2020', 5, 'M5'), ('01-02-2020', 1, 'M1'), ('01-02-2020', 2, NULL), ('01-02-2020', 3, 'M3'), ('01-02-2020', 4, NULL), ('01-02-2020', 5, 'M5'); SELECT Date, Sequence, Period, COALESCE( LEAD(Period, 1) OVER (partition by Date ORDER BY Date, Sequence), LEAD(Period, 2) OVER (partition by Date ORDER BY Date, Sequence), LEAD(Period, 3) OVER (partition by Date ORDER BY Date, Sequence), LEAD(Period, 4) OVER (partition by Date ORDER BY Date, Sequence), LEAD(Period, 5) OVER (partition by Date ORDER BY Date, Sequence), LEAD(Period, 6) OVER (partition by Date ORDER BY Date, Sequence), LEAD(Period, 7) OVER (partition by Date ORDER BY Date, Sequence), LEAD(Period, 8) OVER (partition by Date ORDER BY Date, Sequence), LEAD(Period, 9) OVER (partition by Date ORDER BY Date, Sequence), LEAD(Period, 10) OVER (partition by Date ORDER BY Date, Sequence),Period ) As Lead, COALESCE( LAG(Period, 1) OVER (partition by Date ORDER BY Date, Sequence), LAG(Period, 2) OVER (partition by Date ORDER BY Date, Sequence), LAG(Period, 3) OVER (partition by Date ORDER BY Date, Sequence), LAG(Period, 4) OVER (partition by Date ORDER BY Date, Sequence), LAG(Period, 5) OVER (partition by Date ORDER BY Date, Sequence), LAG(Period, 6) OVER (partition by Date ORDER BY Date, Sequence), LAG(Period, 7) OVER (partition by Date ORDER BY Date, Sequence), LAG(Period, 8) OVER (partition by Date ORDER BY Date, Sequence), LAG(Period, 9) OVER (partition by Date ORDER BY Date, Sequence), LAG(Period, 10) OVER (partition by Date ORDER BY Date, Sequence),Period ) As Lag From #Test t /* Date Sequence Period Lead Lag 2020-01-01 1 M1 M2 M1 2020-01-01 2 M2 M5 M1 2020-01-01 3 NULL M5 M2 2020-01-01 4 NULL M5 M2 2020-01-01 5 M5 M5 M2 2020-01-02 1 M1 M3 M1 2020-01-02 2 NULL M3 M1 2020-01-02 3 M3 M5 M1 2020-01-02 4 NULL M5 M3 2020-01-02 5 M5 M5 M3 */

    Wishes

    Melissa


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.


    Monday, August 10, 2020 1:48 AM
  • Hi Abhinav,

    Please also find another method from below:

    drop table if exists #Test

    Create Table #Test(Date date, Sequence int, Period varchar(2)); Insert #Test(Date, Sequence, Period) Values ('01-01-2020', 1, 'M1'), ('01-01-2020', 2, 'M2'), ('01-01-2020', 3, NULL), ('01-01-2020', 4, NULL), ('01-01-2020', 5, 'M5'), ('01-02-2020', 1, 'M1'), ('01-02-2020', 2, NULL), ('01-02-2020', 3, 'M3'), ('01-02-2020', 4, NULL), ('01-02-2020', 5, 'M5'); SELECT t.Date, t.Period, t.Sequence, COALESCE(LEAD(t.Period, NULLIF((SELECT COUNT(*) FROM #Test WHERE Sequence BETWEEN t.Sequence AND (SELECT MIN(Sequence) FROM #Test WHERE Period IS NOT NULL AND Sequence > t.Sequence AND Date = t.Date) AND Date = t.Date),0)-1 ) OVER (PARTITION BY t.Date ORDER BY Date,t.Sequence),Period) AS LEAD ,COALESCE(LAG(t.Period, NULLIF((SELECT COUNT(*) FROM #Test WHERE Sequence BETWEEN (SELECT MAX(Sequence) FROM #Test WHERE Period IS NOT NULL AND Sequence < t.Sequence AND Date = t.Date) AND t.Sequence AND Date = t.Date),0)-1 ) OVER (PARTITION BY t.Date ORDER BY Date,t.Sequence),Period) AS LAG FROM #Test t /* Date Sequence Period Lead Lag 2020-01-01 1 M1 M2 M1 2020-01-01 2 M2 M5 M1 2020-01-01 3 NULL M5 M2 2020-01-01 4 NULL M5 M2 2020-01-01 5 M5 M5 M2 2020-01-02 1 M1 M3 M1 2020-01-02 2 NULL M3 M1 2020-01-02 3 M3 M5 M1 2020-01-02 4 NULL M5 M3 2020-01-02 5 M5 M5 M3 */

    Wishes

    Melissa


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.


    Monday, August 10, 2020 2:25 AM

All replies

  • Create Table #Test(Date date, Sequence int, Period varchar(2));
    Insert #Test(Date, Sequence, Period) Values
    ('01-01-2020', 1, 'M1'),
    ('01-01-2020', 2, 'M2'),
    ('01-01-2020', 3, NULL),
    ('01-01-2020', 4, NULL),
    ('01-01-2020', 5, 'M5'),
    ('01-02-2020', 1, 'M1'),
    ('01-02-2020', 2, NULL),
    ('01-02-2020', 3, 'M3'),
    ('01-02-2020', 4, NULL),
    ('01-02-2020', 5, 'M5');
    
    Select t.Date, t.Sequence, 
      IsNull(LeadVal.Period, t.Period) As Lead,
      IsNull(LagVal.Period, t.Period) As Lag
    From #Test t
    Outer Apply (Select Top 1 Period From #Test t2 Where t2.Period Is Not Null And t.Date = t2.Date And t.Sequence < t2.Sequence Order By t2.Sequence) As LeadVal
    Outer Apply (Select Top 1 Period From #Test t3 Where t3.Period Is Not Null And t.Date = t3.Date And t.Sequence > t3.Sequence Order By t3.Sequence Desc) As LagVal
    Order By Date, Sequence;
    Tom
    • Marked as answer by Abhinav530 Monday, August 10, 2020 1:17 PM
    Sunday, August 9, 2020 10:54 PM
  • ;with mycte as (
    SELECT Date, Sequence, Period,
     Cast(SUBSTRING( Min(Cast(date  as  BINARY(8)) +   Cast(Sequence  as  BINARY(4)) + Cast(Period  as  BINARY(8)) ) 
     OVER(partition by Date ORDER BY Sequence  ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ),  13, 8) as varchar(2)) PeriodFilled1
    ,Cast(SUBSTRING( Max(Cast(date  as  BINARY(8)) +   Cast(Sequence  as  BINARY(4)) + Cast(Period  as  BINARY(8)) ) 
     OVER(partition by Date ORDER BY Sequence  ROWS  UNBOUNDED PRECEDING ),  13, 8) as varchar(2)) PeriodFilled2  
     from #Test
      )
    
    Select Date, Sequence, Period 
     ,ISNULL(lead(PeriodFilled1) Over(partition by Date ORDER BY  Date, Sequence),Period)  [LEAD]
     ,ISNULL(lag(PeriodFilled2)  Over(partition by Date ORDER BY  Date, Sequence),Period)  [LAG]
    From mycte
    ORDER BY  Date , Sequence 

    • Marked as answer by Abhinav530 Monday, August 10, 2020 1:17 PM
    Monday, August 10, 2020 12:36 AM
  • Hi Abhinav,

    Please also find below method:

    drop table if exists #Test

    Create Table #Test(Date date, Sequence int, Period varchar(2)); Insert #Test(Date, Sequence, Period) Values ('01-01-2020', 1, 'M1'), ('01-01-2020', 2, 'M2'), ('01-01-2020', 3, NULL), ('01-01-2020', 4, NULL), ('01-01-2020', 5, 'M5'), ('01-02-2020', 1, 'M1'), ('01-02-2020', 2, NULL), ('01-02-2020', 3, 'M3'), ('01-02-2020', 4, NULL), ('01-02-2020', 5, 'M5'); SELECT Date, Sequence, Period, COALESCE( LEAD(Period, 1) OVER (partition by Date ORDER BY Date, Sequence), LEAD(Period, 2) OVER (partition by Date ORDER BY Date, Sequence), LEAD(Period, 3) OVER (partition by Date ORDER BY Date, Sequence), LEAD(Period, 4) OVER (partition by Date ORDER BY Date, Sequence), LEAD(Period, 5) OVER (partition by Date ORDER BY Date, Sequence), LEAD(Period, 6) OVER (partition by Date ORDER BY Date, Sequence), LEAD(Period, 7) OVER (partition by Date ORDER BY Date, Sequence), LEAD(Period, 8) OVER (partition by Date ORDER BY Date, Sequence), LEAD(Period, 9) OVER (partition by Date ORDER BY Date, Sequence), LEAD(Period, 10) OVER (partition by Date ORDER BY Date, Sequence),Period ) As Lead, COALESCE( LAG(Period, 1) OVER (partition by Date ORDER BY Date, Sequence), LAG(Period, 2) OVER (partition by Date ORDER BY Date, Sequence), LAG(Period, 3) OVER (partition by Date ORDER BY Date, Sequence), LAG(Period, 4) OVER (partition by Date ORDER BY Date, Sequence), LAG(Period, 5) OVER (partition by Date ORDER BY Date, Sequence), LAG(Period, 6) OVER (partition by Date ORDER BY Date, Sequence), LAG(Period, 7) OVER (partition by Date ORDER BY Date, Sequence), LAG(Period, 8) OVER (partition by Date ORDER BY Date, Sequence), LAG(Period, 9) OVER (partition by Date ORDER BY Date, Sequence), LAG(Period, 10) OVER (partition by Date ORDER BY Date, Sequence),Period ) As Lag From #Test t /* Date Sequence Period Lead Lag 2020-01-01 1 M1 M2 M1 2020-01-01 2 M2 M5 M1 2020-01-01 3 NULL M5 M2 2020-01-01 4 NULL M5 M2 2020-01-01 5 M5 M5 M2 2020-01-02 1 M1 M3 M1 2020-01-02 2 NULL M3 M1 2020-01-02 3 M3 M5 M1 2020-01-02 4 NULL M5 M3 2020-01-02 5 M5 M5 M3 */

    Wishes

    Melissa


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.


    Monday, August 10, 2020 1:48 AM
  • Hi Abhinav,

    Please also find another method from below:

    drop table if exists #Test

    Create Table #Test(Date date, Sequence int, Period varchar(2)); Insert #Test(Date, Sequence, Period) Values ('01-01-2020', 1, 'M1'), ('01-01-2020', 2, 'M2'), ('01-01-2020', 3, NULL), ('01-01-2020', 4, NULL), ('01-01-2020', 5, 'M5'), ('01-02-2020', 1, 'M1'), ('01-02-2020', 2, NULL), ('01-02-2020', 3, 'M3'), ('01-02-2020', 4, NULL), ('01-02-2020', 5, 'M5'); SELECT t.Date, t.Period, t.Sequence, COALESCE(LEAD(t.Period, NULLIF((SELECT COUNT(*) FROM #Test WHERE Sequence BETWEEN t.Sequence AND (SELECT MIN(Sequence) FROM #Test WHERE Period IS NOT NULL AND Sequence > t.Sequence AND Date = t.Date) AND Date = t.Date),0)-1 ) OVER (PARTITION BY t.Date ORDER BY Date,t.Sequence),Period) AS LEAD ,COALESCE(LAG(t.Period, NULLIF((SELECT COUNT(*) FROM #Test WHERE Sequence BETWEEN (SELECT MAX(Sequence) FROM #Test WHERE Period IS NOT NULL AND Sequence < t.Sequence AND Date = t.Date) AND t.Sequence AND Date = t.Date),0)-1 ) OVER (PARTITION BY t.Date ORDER BY Date,t.Sequence),Period) AS LAG FROM #Test t /* Date Sequence Period Lead Lag 2020-01-01 1 M1 M2 M1 2020-01-01 2 M2 M5 M1 2020-01-01 3 NULL M5 M2 2020-01-01 4 NULL M5 M2 2020-01-01 5 M5 M5 M2 2020-01-02 1 M1 M3 M1 2020-01-02 2 NULL M3 M1 2020-01-02 3 M3 M5 M1 2020-01-02 4 NULL M5 M3 2020-01-02 5 M5 M5 M3 */

    Wishes

    Melissa


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.


    Monday, August 10, 2020 2:25 AM