Solved Build departement query (1 Viewer)

CJ_London

Super Moderator
Staff member
Local time
Today, 21:41
Joined
Feb 19, 2013
Messages
16,553
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 Mailman - AWF VIP
Local time
Today, 22:41
Joined
Aug 11, 2003
Messages
11,696
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

The Mailman - AWF VIP
Local time
Today, 22:41
Joined
Aug 11, 2003
Messages
11,696
I have adjusted my local test data to conform with the test data I put on the forum and then this is the output:
Code:
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
 

Users who are viewing this thread

Top Bottom