I have a dataset with ID, Date and Sts
ID SDate EDate Sts
101 7/18/2023 7/18/2023 01
101 7/19/2023 7/18/2023 01
101 7/20/2023 7/20/2023 02
101 7/21/2023 7/21/2023 03
I need to calculate the number of days between
When ‘SDate=‘Edate and STS ne ‘01’ -
When ‘SDate=‘EDate and STS=‘01’
In the above case it should be 7/20/2023-7/18/2023 = 3 days
I have created separate datasets for STS=‘01’ and STS ne ‘01’ and joining on ID and look for date difference > 0
Is there an efficient way using ‘Windows functions?
Thanks