I'm looking for the previous record (rec) for ID based on dates. Some ID have multiple rec for the same date.
I tried LAG but that did not work out because ID have multiple rec for a single date (Please see below what I tried).
SELECT
ID, Rec, Date,
LAG(Rec) OVER (PARTITION BY ID ORDER BY Date DESC) AS [Previous Rec]
FROM
[Source Table]
What else can be used to obtain the desired results (please see below).
Source Table:
| ID | Rec | Date |
| ------------ | ------------ | ------------ |
| 40A | 1022 | 02/03/2022 |
| 40A | 1029 | 02/03/2022 |
| 40A | 5542 | 04/11/2022 |
| 40A | 7693 | 04/11/2022 |
| 40A | 1580 | 06/10/2022 |
| 40A | 9700 | 08/15/2022 |
| 88A | 9911 | 09/10/2022 |
| 88A | 1033 | 12/15/2022 |
| 88A | 1443 | 03/15/2023 |
Desired results:
ID | Rec | Date | Previous Rec | Previous Date |
---|---|---|---|---|
40A | 1022 | 02/03/2022 | NULL | NULL |
40A | 1029 | 02/03/2022 | NULL | NULL |
40A | 5542 | 04/11/2022 | 1022 | 02/03/2022 |
40A | 5542 | 04/11/2022 | 1029 | 02/03/2022 |
40A | 7693 | 04/11/2022 | 1022 | 02/03/2022 |
40A | 7693 | 04/11/2022 | 1029 | 02/03/2022 |
40A | 1580 | 06/10/2022 | 5542 | 04/11/2022 |
40A | 1580 | 06/10/2022 | 7693 | 04/11/2022 |
40A | 9700 | 08/15/2022 | 1580 | 06/10/2022 |
88A | 9911 | 09/10/2022 | NULL | NULL |
88A | 1033 | 12/15/2022 | 9911 | 09/10/2022 |
88A | 1443 | 03/15/2023 | 1033 | 12/15/2022 |
3条答案
按热度按时间ujv3wf0j1#
My personal preferred solution for this type of exercise is to apply the required previous date to the current row and then use it to join on, I find this makes the intent more obvious:
qni6mghb2#
Do in 2 stages, first get the previous date then left join
https://dbfiddle.uk/IbwFcMn6
k75qkfdt3#
I see that you have multiple records for a date and you want to get all previous records for previous date. You can use self join like below to get the desired results: