Lead and lag function

Question

Hi Expert,

I am facing issue in lead and lag function for getting active and inactive records which is 1,01,0 but at the end bottom, i am getting incorrect records here is the query
I am running the query order by contract id . please see the image which says the records are coming correctly but at the end its not follwing lead and lag condition

CREATE TABLE dbo.Table1 ( DivID INT, CustomerID INT, DivStatus INT, LogDate DATE )

INSERT dbo.Table1 (DivID, CustomerID, DivStatus, LogDate)
VALUES (-1, -1, 0, '1/18/22'),(-1, -1, 0, '1/19/22'),
(-1, -1, 0, '1/20/22'),(-1, -1, 0, '1/22/22'),
(-1, -1, 0, '1/23/22'),(-1, -1, 1, '1/18/22'),
(-1, -1, 0, '1/28/22'),(-1, -1, 0, '1/23/22'),
(-1, -1, 0, '1/19/22'),(2, -1,1, '1/25/22'),
(2, -1,0, '1/27/22'),(2, 1,1, '1/28/22'),
('495','-1' ,'1','02/14/2021'), ('495','-1' ,'1','

02/14/2021'), ('502' ,'-1', '1','

02/14/2021'), ('513', '-1','1','

02/14/2021'), ('538', '-1','1','

02/14/2021'), ('545', '-1','1','02/14/2021'), ('563', '-1','1','

02/14/2021'), ('577', '-1','1','

02/14/2021');I am expecting output for div_status as 1,0,1,0 for order by contract id but when i am running below mentioned lead and lag query getting different output in bottom

SELECT Div_ID,CustomerID,Div_Status,LogDate ,LogDateFROM ( SELECT Div_ID,CustomerID,Div_Status,LogDate,LAG(Div_Status,1,0) OVER (PARTITION BY Div_ID ORDER BY logdate) AS PreviousValue,LEAD (Div_Status,0,1) OVER (PARTITION BY Div_ID ORDER BY logdate) as NextValueFROM dbo.Table_1 ) AS tWHERE (PreviousValue = 0 AND Div_Status = 1 AND NextValue = 1 ) OR(PreviousValue = 0 AND Div_Status = 0 AND NextValue = 1 ) OR(PreviousValue = 0 AND Div_Status = 1 AND NextValue = 0 ) OR(PreviousValue = 1 AND Div_Status = 0 AND NextValue = 0 )

output is:

the above query gives bottom 1 values which is incorrectRegards,

Answer 1

Any Suggestion

Answer 2

Hi @Shambhu Rai
I find this LEAD (Div_Status,0,1) in your query ,where you set the offset = 0.This doesn't get you the next value.

Answer 3

Hi Expert,
this is expected results. div_id is contractid.. there should not be any other values apart from results

Answer 4

(1) Your question text is almost not readable! You could have put a bit more effort in posting the question

(2) As a result of point 1 I did not read the queries in death but in first glance I notice that you ae using PARTITION BY DivID and in all the last rows the DivID is different which is probably means they will not be grouped and this lead to the result

Please try to publish you scenario better with a well formatted code

IN addition we need the full expected result you want to get and please try to elaborate how the logic of how you get the expected result from the sample data

Answer 5

here is the information
I am trying to find active and inactive records in table by div_id i.e. 1 is active an 0 is inactive ....i want these records in divstatus column
There is column Divstatus which should show records with 1,0 (1 means active records and 0 means in active records

So i am using lag and lead function to get the expected records and its coming properly in top 4 rows but afterwords only record is coming in divstatus. i have added expected output in screenshot

CREATE TABLE dbo.Table1 ( DivID INT, CustomerID INT, DivStatus INT, LogDate DATE )

INSERT dbo.Table1 (DivID, CustomerID, DivStatus, LogDate)
VALUES (-1, -1, 0, '1/18/22'),(-1, -1, 0, '1/19/22'),
(-1, -1, 0, '1/20/22'),(-1, -1, 0, '1/22/22'),
(-1, -1, 0, '1/23/22'),(-1, -1, 1, '1/18/22'),
(-1, -1, 0, '1/28/22'),(-1, -1, 0, '1/23/22'),
(-1, -1, 0, '1/19/22'),(2, -1,1, '1/25/22'),
(2, -1,0, '1/27/22'),(2, 1,1, '1/28/22'),
('495','-1' ,'1','02/14/2021'), ('495','-1' ,'1','

02/14/2021'), ('502' ,'-1', '1','

02/14/2021'), ('513', '-1','1','

02/14/2021'), ('538', '-1','1','

02/14/2021'), ('545', '-1','1','02/14/2021'), ('563', '-1','1','

02/14/2021'), ('577', '-1','1','02/14/2021');

I am expecting output for div_status as 1,0,1,0 for order by Div_id but when i am running below mentioned lead and lag query getting different output in bottom

SELECT Div_ID,CustomerID,Div_Status,LogDate ,LogDateFROM ( SELECT Div_ID,CustomerID,Div_Status,LogDate,LAG(Div_Status,1,0) OVER (PARTITION BY Div_ID ORDER BY logdate) AS PreviousValue,LEAD (Div_Status,0,1) OVER (PARTITION BY Div_ID ORDER BY logdate) as NextValueFROM dbo.Table_1 ) AS tWHERE (PreviousValue = 0 AND Div_Status = 1 AND NextValue = 1 ) OR(PreviousValue = 0 AND Div_Status = 0 AND NextValue = 1 ) OR(PreviousValue = 0 AND Div_Status = 1 AND NextValue = 0 ) OR(PreviousValue = 1 AND Div_Status = 0 AND NextValue = 0 )

output is:enter code here

this is expected results. div_id is contractid.. there should not be any other values apart from 1,0 in divstatus column

176708-image.png