T-SQL:最近的日期列表

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.

我一直在尝试RowNum()和Top 1以及相关的子查询，但我就是无法得到它。谢谢你！

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'

2 个解决方案

#1

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

#2

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.

我知道John Cappelletti已经使用LAG()提供了答案。但是想尝试用join来解决这个问题。如果有人需要它的话，也许是在将来。这个解决方案似乎有点麻烦，但它服务于目的。

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

#1