sql在第一个表中最早的日期之后加入2个表 [英] sql join 2 tables on earliest date after date in first table

问题描述

我的请求类似于此处描述的请求:

My request is similar to the one described here: sql server - join 2 tables based on earliest date in 2nd table

一个区别是，我需要在第一张表格中找到出院日期之后的最早的门诊就诊日期，该日期在出院日期的30天内.我想返回Null，即在该时间范围内没有后续访问的地方.

One difference is that I need to find the earliest available date of Outpatient Clinic visit after the Discharge date in first table and that falls within the 30 days from the Discharge Date. I would like to return Nulls where no Follow-up visit exists within the time frame.

表1'Discharges'-包含列:ClientId，DischargeFrom，Discharge Date

Table 1 'Discharges' - contains columns: ClientId, DischargeFrom, Discharge Date

ClientId    DischargeFrom   DischargeDate
1   Unit A  2009/11/08
1   Unit A  2010/01/05
2   Unit A  2010/01/08
3   Unit B  2010/10/01
4   Unit A  2010/02/04
4   Unit B  2010/04/05
5   Unit A  2010/01/04

表2'OutpatientVisits-包含以下列:ClientId，FollowUpClinicName，FollowUpVisitDt

Table 2 'OutpatientVisits - contains columns: ClientId, FollowUpClinicName, FollowUpVisitDt

ClientId    FollowUpClinicName  FollowUpVisitDt
1   Outpatient_Clinic_1 2009/05/04
1   Outpatient_Clinic_1 2009/07/07
1   Outpatient_Clinic_1 2010/01/14
1   Outpatient_Clinic_1 2010/01/18
2   Outpatient_Clinic_2 2007/11/05
2   Outpatient_Clinic_3 2009/12/22
2   Outpatient_Clinic_1 2010/01/04
5   Outpatient_Clinic_2 2010/01/01
5   Outpatient_Clinic_1 2010/01/11
7   Outpatient_Clinic_3 2010/01/25

一个ClientId可以具有多个出勤(此处为ClientId 1和4)，并且一个ClientId也可以与许多后续访问相关联(ClientId 1、2和5).我想在出院日期或之后但在出院日期的30天内返回第一个FollowUpVisitDt.我还想返回那些没有后续随访的出院记录.

One ClientId can have many Discharges (here it would be ClientIds 1 and 4) and one ClientId can also be associated with many FollowUp Visits (ClientIds 1, 2, and 5). I would like to return the first FollowUpVisitDt on or after the Discharge Date but within 30 days of Discharge Date. I would also like to return those discharge records where no follow-up visit exists.

我尝试了以下语法，但似乎无法返回最早的访问记录(请参阅ClientId 1在相同的出院日期有2次重复的结果).此外，ClientId 2也已释放，但未在结果中列出.

I have tried the following syntax but I cannot seem to return the earliest visit for record (see ClientId 1 has 2 repeating results for the same discharge date). Also ClientId 2 was discharged but not listed in the results.

SELECT DISTINCT Discharges.ClientId,
    Discharges.DischargeFrom, 
    Discharges.DischargeDate,
    FollowUpVisits.FollowUpVisitDate,
    DateDiff(DAY,Discharges.DischargeDate,FollowUpVisits.FollowUpVisitDate) As DaysBetween,
    FollowUpVisits.rn
 FROM Discharges LEFT JOIN
 (
 SELECT *, ROW_NUMBER() OVER (PARTITION By ClientId, FollowUpClinicName ORDER BY FollowUpVisitDate ASC) as rn
    FROM [MH].[dbo].OutpatientVisits
 ) As FollowUpVisits
 ON Discharges.ClientId=FollowUpVisits.ClientId
 WHERE (FollowUpVisits.FollowUpVisitDate>=Discharges.DischargeDate and FollowUpVisitDate<=DATEADD(day,30,DischargeDate)) OR FollowUpVisits.FollowUpVisitDate Is Null

结果:

ClientId    DischargeFrom   DischargeDate   FollowUpVisitDate   DaysBetween rn
1   Unit A  2009-11-08  2009-12-27  49  3
1   Unit A  2009-11-08  2010-01-18  71  4
1   Unit A  2010-01-05  2010-01-18  13  4
3   Unit B  2010-10-01  NULL    NULL    NULL
4   Unit A  2010-02-04  NULL    NULL    NULL
4   Unit B  2010-04-05  NULL    NULL    NULL
5   Unit A  2010-01-04  2010-01-11  7   1

非常感谢您的帮助！我正在使用MSSQL 2005.

Your help is greatly appreciated! I am using MSSQL 2005.

推荐答案

为此尝试使用outer apply:

select d.*, fv.*,
       datediff(day, d.DischargeDt, ov.FollowupVisitDt) as DaysBetween
from Discharges d outer apply
     (select top 1 ov.*
      from [MH].[dbo].OutpatientVisits ov
      where ov.ClientId = d.ClientId and
            ov.FollowupVisitDt > d.DischargeDt
      order by FollowupVisitDt
     ) fv;

这篇关于sql在第一个表中最早的日期之后加入2个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！