For a Table T1
+----------+-----------+-----------------+
| PersonID | Date | Employment |
+----------+-----------+-----------------+
| 1 | 2/28/2017 | Stayed the same |
| 1 | 4/21/2017 | Stayed the same |
| 1 | 5/18/2017 | Stayed the same |
| 2 | 3/7/2017 | Improved |
| 2 | 4/1/2017 | Stayed the same |
| 2 | 6/1/2017 | Stayed the same |
| 3 | 3/28/2016 | Improved |
| 3 | 5/4/2016 | Improved |
| 3 | 4/19/2017 | Worsened |
| 4 | 5/19/2016 | Worsened |
| 4 | 2/16/2017 | Improved |
+----------+-----------+-----------------+
I'm trying to calculate a Final Result
field partitioning on Employment/PersonID fields, based on the latest result/person relative to prior results. What I mean by that is explained in the logic behind Final Result
:
For every Person,
If all results/person are Stayed the same, then only should final result for that person be "Stayed the same"
If Worsened/Improved are in the result set for a person, the final result should be the latest Worsened/Improved result for that person, irrespective of "Stayed the same" after a W/I result.
Eg:
- Person 1 Final result -> Stayed the same, as per (1)
- Person 2 Final result -> Improved, as per (2)
- Person 3 Final result -> Worsened, as per (2)
- Person 4 Final result -> Improved, as per (2)
Desired Result:
+----------+-----------------+
| PersonID | Final Result |
+----------+-----------------+
| 1 | Stayed the same |
| 2 | Improved |
| 3 | Worsened |
| 4 | Improved |
+----------+-----------------+
I know this might involve Window functions or Sub-queries but I'm struggling to code this.