SQL Server Select previous record for an ID based on dates

lx0bsm1f  于 2023-10-15  发布在  其他
关注(0)|答案(3)|浏览(91)

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:

IDRecDatePrevious RecPrevious Date
40A102202/03/2022NULLNULL
40A102902/03/2022NULLNULL
40A554204/11/2022102202/03/2022
40A554204/11/2022102902/03/2022
40A769304/11/2022102202/03/2022
40A769304/11/2022102902/03/2022
40A158006/10/2022554204/11/2022
40A158006/10/2022769304/11/2022
40A970008/15/2022158006/10/2022
88A991109/10/2022NULLNULL
88A103312/15/2022991109/10/2022
88A144303/15/2023103312/15/2022
ujv3wf0j

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:

select t.Id, t.Rec, t.Date, pt.Rec Previous_Rec, pt.Date PreviousDate
from t
outer apply(
  select max(Date)
  from t t2
  where t2.Id = t.Id and t2.date < t.Date
)pd(Date)
left join t pt on pt.Id = t.Id and pt.Date = pd.Date
order by t.Id, t.Date;
qni6mghb

qni6mghb2#

Do in 2 stages, first get the previous date then left join

with cte as (
    select
        ID, Rec, Date,
        (select top 1 date from t t1 where t1.id = t.id and t1.date < t.date order by t1.date desc) as Previous_Rec
    from t
)
select cte.*,t.rec,t.date 
from cte
left join t on t.date = cte.previous_rec and t.id = cte.id
order by cte.id, cte.date

https://dbfiddle.uk/IbwFcMn6

k75qkfdt

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:

select s1.*, s2.rec [Prev Rec], s2.[date] [Prev Date]
from   [Source Table] s1 left outer join
       [Source Table] s2 on s1.id = s2.id and 
       s2.[date] = (select max([date]) from [Source Table] 
                    where id = s1.id and [date] < s1.[date])

相关问题