Find next row value using lead

Question

I am trying to calculate a validtodate using the lead function. My data can change when a new file is uploaded. This file includes every location which is active in a warehouse.

ValidFromDate	ValidToDate	Location	AdministrationNr	WarehouseNr	FileName
2-1-2022	31-12-9999	19-26-2C	1	22	Locaties_Administratie_001_02-01-2022.txt
11-10-2021	1-1-2022	19-26-2C	1	22	Locaties_Administratie_001_11-10-2021.txt
24-8-2021	10-10-2021	19-26-2C	1	22	Locaties_Administratie_001_24-08-2021.txt
4-5-2021	23-8-2021	19-26-2C	1	22	Locaties_Administratie_001_04-05-2021.txt
15-6-2022	31-12-9999	21-48-4C	1	22	Locaties_Administratie_001_15-06-2022.txt
4-1-2022	14-6-2022	21-48-4C	1	22	Locaties_Administratie_001_04-01-2022.txt
2-1-2022	3-1-2022	21-48-4C	1	22	Locaties_Administratie_001_02-01-2022.txt
11-10-2021	1-1-2022	21-48-4C	1	22	Locaties_Administratie_001_11-10-2021.txt
24-8-2021	10-10-2021	21-48-4C	1	22	Locaties_Administratie_001_24-08-2021.txt
4-5-2021	23-8-2021	21-48-4C	1	22	Locaties_Administratie_001_04-05-2021.txt
15-6-2022	31-12-9999	21-60-3D	1	22	Locaties_Administratie_001_15-06-2022.txt
4-1-2022	14-6-2022	21-60-3D	1	22	Locaties_Administratie_001_04-01-2022.txt
2-1-2022	3-1-2022	21-60-3D	1	22	Locaties_Administratie_001_02-01-2022.txt
11-10-2021	1-1-2022	21-60-3D	1	22	Locaties_Administratie_001_11-10-2021.txt
24-8-2021	10-10-2021	21-60-3D	1	22	Locaties_Administratie_001_24-08-2021.txt
4-5-2021	23-8-2021	21-60-3D	1	22	Locaties_Administratie_001_04-05-2021.txt

Within this file a date is extracted, the ValidFromDate. Based on this date I want to find the next ValidFromDate to determine till what date this location was active. I did this with the following function:

LEAD( DATEADD(DAY, -1, [ValidFromDate]) ,1, '9999-12-31' ) 
OVER (PARTITION BY [AdministrationNr], [WarehouseNr], [Location] 
ORDER BY [ValidFromDate] ASC    ) as ValidToDate

However, when a location completely dissapears within the file this results in 9999-12-31. This is correct when the location is in the latest file because this is the active at this moment but this is also the case when a location dissapears in the warehouse.

In this case for location '19-26-2C' I have to find the next file date where it dit not appear in.

By checking whether a ValidFromDate is equal to the maximum date in all rows I can determine if the location is active at the current moment. See code:

CASE 
    WHEN ValidFromDate = MAX ( ValidFromDate ) OVER ( PARTITION BY WarehouseNr, AdministrationNr )
    THEN '9999-31-12'
    ELSE -- Next file date

All that is left for me is to fill the else statement with the next file date after file: 2022-01-02.

This is date 2022-01-04.

Is there any way for me to find this date?

[Please do not upload images of code/data/errors when asking a question.](//meta.stackoverflow.com/q/285551) If you want to supply sample data, DDL and DML are by far the best way. For expected results, then a markdown table works well. — Thom A, Aug 17 '22 at 08:23
This would be much more clear if you would show us some sample data and expected outcome (images for this do not count) — GuidoG, Aug 17 '22 at 08:30
@GuidoG I Just added a data sample. The first row should not have 31-12-9999 but should get value 3-1-2022. — Freek Hoeijmans, Aug 17 '22 at 08:48
Then just change the value in the first row. Or what is your question ? — GuidoG, Aug 17 '22 at 09:25
@GuidoG The ValidToDate is calculated with this statement: `LEAD( DATEADD(DAY, -1, [ValidFromDate]) ,1, '9999-12-31' ) OVER (PARTITION BY [AdministrationNr], [WarehouseNr], [Location] ORDER BY [ValidFromDate] ASC ) as ValidToDate` For location '19-26-2C' the date 9999-31-12 is not correct. It should be the date 04-01-2022 because this is where the location dissapears in my files. I am looking for the next file date in this case. What formula can I use to get this date? — Freek Hoeijmans, Aug 17 '22 at 09:45
What does "next file date" mean? If there is no row then there is no row. Why would you use a different location altogether? — Charlieface, Aug 17 '22 at 20:46

GuidoG · Answer 1 · 2022-08-17T12:53:27.537

You could do this with an outer apply in stead of the lead

That would look like this

select t.ValidFromDate,
       isnull(d.ValidToDate, '99991231') as ValidToDate,
       t.Location,
       t.AdministrationNr,
       t.WareHouseNr,
       t.FileName
from   t
  outer apply ( select top 1
                       dateadd(day, -1, t2.ValidFromDate) as ValidToDate
                from   t as t2
                where  t2.AdministrationNr = t.AdministrationNr
                and    t2.WareHouseNr = t.WareHouseNr
                and    t2.Location = t.Location
                and    t2.ValidFromDate > t.ValidFromDate
                order by t2.ValidFromDate
              ) d
order by t.id

See this DBFiddle to see this in action

EDIT

If you want to use window function, I would use LAG in stead of LEAD, see this example

select t.ValidFromDate,
       dateadd(day, -1, 
         isnull(lag(t.ValidFromDate)
                  over (partition by t.AdministrationNr, t.WareHouseNr, t.Location
                  order by id)
               , '10000101')
              ) as ValidToDate,
       t.Location,
       t.AdministrationNr,
       t.WareHouseNr,
       t.FileName
from   t
order by t.id

And this updated DBFiddle

The result looks like this

ValidFromDate	ValidToDate	Location	AdministrationNr	WareHouseNr	FileName
2022-01-02	9999-12-31	19-26-2C	1	22	Locaties_Administratie_001_02-01-2022.txt
2021-10-11	2022-01-01	19-26-2C	1	22	Locaties_Administratie_001_11-10-2021.txt
2021-08-24	2021-10-10	19-26-2C	1	22	Locaties_Administratie_001_24-08-2021.txt
2021-05-04	2021-08-23	19-26-2C	1	22	Locaties_Administratie_001_04-05-2021.txt
2022-06-15	9999-12-31	21-48-4C	1	22	Locaties_Administratie_001_15-06-2022.txt
2022-01-04	2022-06-14	21-48-4C	1	22	Locaties_Administratie_001_04-01-2022.txt
2022-01-02	2022-01-03	21-48-4C	1	22	Locaties_Administratie_001_02-01-2022.txt
2021-10-11	2022-01-01	21-48-4C	1	22	Locaties_Administratie_001_11-10-2021.txt
2021-08-24	2021-10-10	21-48-4C	1	22	Locaties_Administratie_001_24-08-2021.txt
2021-05-04	2021-08-23	21-48-4C	1	22	Locaties_Administratie_001_04-05-2021.txt
2022-06-15	9999-12-31	21-60-3D	1	22	Locaties_Administratie_001_15-06-2022.txt
2022-01-04	2022-06-14	21-60-3D	1	22	Locaties_Administratie_001_04-01-2022.txt
2022-01-02	2022-01-03	21-60-3D	1	22	Locaties_Administratie_001_02-01-2022.txt
2021-10-11	2022-01-01	21-60-3D	1	22	Locaties_Administratie_001_11-10-2021.txt
2021-08-24	2021-10-10	21-60-3D	1	22	Locaties_Administratie_001_24-08-2021.txt
2021-05-04	2021-08-23	21-60-3D	1	22	Locaties_Administratie_001

Find next row value using lead

1 Answers1