Return first date and previous dates

There is a custid with 3 dates .

I want to return first date and then its previous date .

data should look like:

custid first       previous
11     2019-06-10  2019-06-15
11     2019-06-10  2019-07-10

I have done it but I’m not able to put it in 2nd row.

SELECT A.PersonID ,min(a.date) as first,b.date as previous,c.date as previous from Persons as a
INNER JOIN (select PersonID ,date from Persons) b
on a.PersonID
=b.PersonID  AND 
a.date<
b.date
INNER JOIN (select PersonID ,date from Persons) c
            on b.PersonID=
c.PersonID
           AND b.date<
c.date

Output which I’m getting:

personid first         previous          previous
11       2019-06-10    2019-06-15            2019-07-10

I want it to look like:

custid first       previous
11     2019-06-10  2019-06-15
11     2019-06-10  2019-07-10

Answer

You don’t need to add the 3rd inner join table. Since your using min() with other columns, this should be accompanied by Group By.

select
  a.PersonID as custid,
  min(a.date) as first,
  b.date as previous     
from
  Persons as a
inner join Persons b on a.PersonID = b.PersonID and b.date > a.date
group by a.PersonID, b.date

Advertisement

Answer