Checking the existence of an entry in the immediate future date and counting in future date

ozarkram

Hi All,

Hope you are doing well!...I am trying to build a magic etl for getting the number of entries (combination of sno and mmodel) that are not present in the current date of reference in comparison to the immediate previous date of reference...This will help understand the number of entries that have gone off the list in the current date of reference in comparison to the previous date of reference... Please find the sample data below:

sno mmodel snapshot_date

r123 S1234 3/1/2022

r3421 S1234 3/1/2022

y123 D2123 3/1/2022

g21q D2123 3/1/2022

W321 G345 3/1/2022

E231 G345 3/1/2022

r123 S1234 3/2/2022

D891 S1234 3/2/2022

y123 D2123 3/2/2022

W321 G345 3/2/2022

K1231 J1231 3/2/2022

r123 S1234 3/3/2022

D891 S1234 3/3/2022

H213 V231 3/3/2022

121k N213 3/3/2022

date off Delay

3/1/2022 0

3/2/2022 3

3/3/2022 3

In the above example for date march 2nd 2022 the number of off delays is 3 because the entries (r3421,S1234) , (g21q,D2123) and (E231,G345) are present on March 1st but are not present in the data on March 2nd

Also the entries (y123,D2123), (W321,G345) and (K1231,J1231) are present on march 2nd but not present in the data on March 3rd so the number off delays for March 3rd is 3...

Can you please help me here...

ozarkram

Hi All, I was able to get this done myself!...I used a lead function with date operations and was able to get this done

ozarkram

Hi All, I was able to get this done myself!...I used a lead function with date operations and was able to get this done