0

I've got a table where we have registries of employees and where they have worked. In each row, we have the employee's starting date on that place. It's something like this:

Employee ID Name Branch Start Date
1 John Doe 234 2018-01-20
1 John Doe 300 2019-03-20
1 John Doe 250 2022-01-19
2 Jane Doe 200 2019-02-15
2 Jane Doe 234 2020-05-20

I need a query where the data returned looks for the next value, making the starting date on the next branch as the end of the current. Eg:

Employee ID Name Branch Start Date End Date
1 John Doe 234 2018-01-20 2019-03-20
1 John Doe 300 2019-03-20 2022-01-19
1 John Doe 250 2022-01-19 ---
2 Jane Doe 200 2019-02-15 2020-05-20
2 Jane Doe 234 2020-05-20 ---

When there is not another register, we assume that the employee is still working on that branch, so we can leave it blank or put a default "9999-01-01" value. Is there any way we can achieve a result like this using only SQL?

Another approach to my problem would be a query that returns only the row that is in a range. For example, if I look for what branch John Doe worked in 2020-12-01, the query should return the row that shows the branch 300.

Moisés Almeida
  • 31
  • 3
  • You may use subselect for `end_date` in the form of `(select min (start_date) from mytab t where...)` or `lead` olap function for that. Try it on your own and show what you get. – Mark Barinstein Mar 18 '22 at 19:36

1 Answers1

1

You can use LEAD() to peek at the next row, according to a subgroup and ordering within it.

For example:

select
  t.*,
  lead(start_date) over(partition by employee_id order by start_date) as end_date
from t
The Impaler
  • 45,731
  • 9
  • 39
  • 76
  • Sorry about the delay on my response. So, I've tried your solution but I'm getting this error: `NO AUTHORIZED LEAD NAMED HAVING COMPATIBLE ARGUMENTS WAS FOUND. SQLCODE=-440, SQLSTATE=42884, DRIVER=4.19.26` – Moisés Almeida Mar 28 '22 at 17:53
  • 1
    I didn't realize this was for z/OS. This query works well in DB2 LUW, and unfortunately I don't have the chance of testing it in z/OS. – The Impaler Mar 28 '22 at 21:43