Search code examples
sqlsql-servermsbi

How to get previous record date for a group in a select query

I need to calculate the Prevdate as shown below without using CTE or temp tables. I tried using lead it applied for only one row above. Also the number of record for each group might vary. Can anyone suggest a solution here?

Below is the sample data:

create table #Sampledata 
(
id int
,groupno int
,Date date
)
insert into #Sampledata values (
1,1,'1/2/2020'),
(2,2,'1/13/2020'),
(3,2,'1/13/2020'),
(4,2,'1/13/2020'),
(5,3,'1/24/2020')

Below is the expected output:

enter image description here

Solution

  • What you are calling the previous date is really the next date. That just helps understanding the question.

    Unfortunately, SQL Server does not fully support range window frames, so window functions (at least in a simple manner) are not appropriate.

    One method is using lead() in an aggregation subquery and joining the results in:

    select sd.*, g.next_date
from sampledata sd join
     (select groupno, lead(min(date)) over (order by groupno) as next_date
      from Sampledata
      group by groupno
     ) g
     on sd.groupno = g.groupno;

    Another option is cross apply:

    select sd.*, x.next_date
from sampledata sd outer apply
     (select top (1) min(sd2.date) as next_date
      from sampledata sd2
      where sd2.date > sd.date
     ) x;

    Of course, if the groups really are sequentially enumerated, you can replace sd2.date > sd.date with sd2.groupno = sd.groupno + 1.

    Here is a db<>fiddle.