I have a query built in Oracle 10 which produces a set of data like this:
EMP_ID WORK_DT TIME_TYPE
---------- --------- ---------
12345 19-DEC-14 10
12345 18-DEC-14 10
12345 17-DEC-14 10
12345 16-DEC-14 20
12345 15-DEC-14 20
12345 14-DEC-14 10
12345 13-DEC-14 10
54321 19-DEC-14 10
54321 18-DEC-14 20
[...]
and I'm looking for output like this, only showing the most recent time_type for each employee with the applicable START_DT:
EMP_ID START_DT END_DT TIME_TYPE
---------- --------- --------- ---------
12345 17-DEC-14 19-DEC-14 10
54321 19-DEC-14 19-DEC-14 10
The START_DT has been troublesome for me because the same TIME_TYPE can exist in multiple blocks in the input.
My background is more with other RDBMSs than Oracle. I've spent the last few days searching here and experimenting with Oracle's analytical, aggregate, and subquery functions to get these results. I feel like I'm close but I haven't had much luck.
I'm trying to keep this as an efficient set-based solution rather than resorting to a cursor or function.
Any help is very appreciated!
Aucun commentaire:
Enregistrer un commentaire