Solved Build departement query

[CJ_London]

the inner join worked for me - but the left join will bring out the 'current' department i.e. the last record but without an end date. With regards number of records, that would be reduced if you are only looking at a a restricted time period or range of batches which would normally be the case

 

[namliam]

The result is different because I am using different dates in my (new) table.

And yes, the last record is without enddate (in your query) but since it is contained in a (second) meta table, it is easy to join that and nvl(Thisdate, MetaDate) Enddate to get the right enddate.

Number of records indeed depends on the range, however the range here is going to be a convoluted process.
This is actually part of ETL that needs to happen on a daily basis, the batch gets touched on multiple levels in different ways some without a much needed change date in the source systems. Up to now we have chosen to refresh the entire table to make sure it reflects the current status in all ways possible. Yes this is postponing the inevitable, but for now it still works

 

[namliam]

I have adjusted my local test data to conform with the test data I put on the forum and then this is the output:

ID     BATCH  DEPT STARTDT    NEWDEPT  ENDDT       RIJNR    DEPT_ENDDT
5      X      1    2020-04-01 1        2020-04-02  1        2020-04-02
9      X      3    2020-04-02 1        2020-04-05  2        2020-04-05
15     X      4    2020-04-05 1        2020-04-07  3        2020-04-11
44     X      3    2020-04-11 1        2020-04-15  5        2020-04-22
99     X      1    2020-04-22 1        2020-04-25  8        2020-04-25
7      X      3    2020-04-25 1        2020-04-28  9        2020-05-01