Self Join to calcuate one day before a given date

I have this table Employee_Hire which has Hire date of an employee (ID) in a Department. Now I want to write a Select query which adds one more column (Last_day) to show last day he worked in that department. For instance, for ID = 1 in department D01 his last day was 2014/12/31. Similarly in D02 his last day was 2017/12/31.

Now since he is still working in D04, we can have a string like ‘Till Present’ or a future date like 2099/12/31 or anything which serves the purpose.

I want to do this for each employee in the table.

+---+-------------+------------+
| Id| Dept_name   | Hiredate   |
+---+-------------+------------+
| 1 |      D01    | 2012-01-01 |
| 1 |      D02    | 2015-01-01 |
| 1 |      D03    | 2018-01-01 |
| 1 |      D04    | 2019-01-01 |
| 2 |      D01    | 2010-01-01 |
| 2 |      D02    | 2012-01-01 |
| 3 |      D01    | 2008-01-01 |
| 3 |      D02    | 2010-01-01 |
| 3 |      D03    | 2012-01-01 |
| 4 |      D01    | 2015-01-01 |
| 4 |      D02    | 2017-01-01 |
+---+-------------+------------+

I know this can be done using LEAD OR LAG function but can anyone please tell me how to do this using Self Join?

Thanks in advance!!

Answer

Assuming ROW_NUMBER() function isn’t also off limits, this query uses an actual self join:

SELECT
    curr.Id,
    curr.Dept_name,
    curr.Hiredate,
    DATEADD(day, -1, next.Hiredate) AS Last_day
FROM
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY HireDate) AS RowNum
    FROM EmployeeHire
) AS curr
LEFT OUTER JOIN
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY HireDate) AS RowNum
    FROM EmployeeHire
) AS next
    ON curr.ID = next.ID AND curr.RowNum = next.RowNum - 1

I’m not sure how you could do a self join when sorting by arbitrary criteria without the ROW_NUMBER() function.