CODEWITHSUNDEEP
sql-servert-sqldate
Margaret Martin
Margaret Martin

Reputation: 23

T-SQL: list of dates with most recent previous date

These are dates a patient visited the hospital:

NAME    DISCHARGEDATE
---------------------
Eva         1/1/17
Eva         1/10/17
Eva         2/8/17

I need a list of dates patients visited the hospital along with the most recent previous date they visited the hospital, like this:

NAME   DISCHARGEDATE     PREVIOUSDISCHARGEDATE
-----------------------------------------------
Eva         1/1/17       NULL
Eva         1/10/17      1/1/17
Eva         2/8/17       1/10/17

I've been trying RowNum() and Top 1 and correlated subqueries and I just can't get it. Thank you.

Here's the script I wrote for reality it returns the patient's visits but "NULL" for all the fields from the second table:

SELECT 
    ad.Name,
    ad.DischargeDate,
    PrevDisch.DischargeDate as PrevDischDate
FROM 
    AbstractData AS ad
LEFT JOIN 
    (SELECT TOP 1 
         DischargeDate, UnitNumber, VisitID 
     FROM 
         AbstractData AS ad2
     WHERE 
         SourceID = 'BLD'
         AND PtStatus in ('IN', 'INO')
         AND DischargeDateTime > @ReadmitStartDate
         --AND ad2.DischargeDate < ad.DischargeDate
     ORDER BY 
         UnitNumber, DischargeDate) AS PrevDisch ON ad.UnitNumber = PrevDisch.UnitNumber
                                                 AND PrevDisch.DischargeDate < ad.DischargeDate                                              
                                                 AND PrevDisch.DischargeDate IS NOT NULL
WHERE 
    ad.Name = 'Eva'

Upvotes: 2

Views: 114

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81950

You can use LAG() 

Declare @YourTable table (NAME varchar(25),DISCHARGEDATE date)
Insert Into @YourTable values
('Eva','2017-01-01'),
('Eva','2017-10-01'),
('Eva','2017-02-08')

Select A.*
      ,PREVIOUSDISCHARGEDATE = Lag(DISCHARGEDATE,1) over (Partition By Name Order by DISCHARGEDATE)
 From  @YourTable A

Returns

NAME    DISCHARGEDATE   PREVIOUSDISCHARGEDATE
Eva     2017-01-01      NULL
Eva     2017-02-08      2017-01-01
Eva     2017-10-01      2017-02-08

EDIT- Alternative to LAG()

;with cte as (
    Select *
          ,RN = Row_Number() over (Partition By Name Order by DISCHARGEDATE)
    From  @YourTable
)
Select A.*
      ,PREVIOUSDISCHARGEDATE = B.DISCHARGEDATE
 From  cte A
 Left  Join cte B a.name=B.name and on A.RN=B.RN+1

Upvotes: 4

PowerStar
PowerStar

Reputation: 895

I know that John Cappelletti has already provided the answer by using LAG(). But wanted to try a solution with join hence the solution. May be in future if someone needs its without LAG(). This solution seems to be bit cumbersome but it servers the purpose.

Declare @YourTable table (NAME varchar(25),DISCHARGEDATE date)
Insert Into @YourTable values
('Eva','2017-01-01'),
('Eva','2017-10-01'),
('Eva','2017-02-08')

SELECT NAME,DISCHARGEDATE,MAX(PREVIOUS) AS PREVIOUS_DISCHARGE
 FROM
(SELECT NAME,DISCHARGEDATE,'NULL' AS PREVIOUS
FROM 
(SELECT  NAME
        ,DISCHARGEDATE
        ,ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY DISCHARGEDATE) RN FROM @YourTable) M
WHERE M.RN=1        
UNION ALL
SELECT X.NAME,X.DISCHARGEDATE,CONVERT(VARCHAR(10),Y.DISCHARGEDATE)
FROM 
        (SELECT  NAME
               ,DISCHARGEDATE
               ,ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY DISCHARGEDATE) RN FROM @YourTable) X
INNER JOIN       
        (SELECT  NAME
               ,DISCHARGEDATE
               ,(ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY DISCHARGEDATE)-1) RN FROM @YourTable) Y       
ON X.RN > Y.RN) OUTERR
WHERE CONVERT(VARCHAR(10),DISCHARGEDATE) <> PREVIOUS
GROUP BY NAME,DISCHARGEDATE
ORDER BY NAME

Upvotes: 0

Related Questions