SQL：如何获得与患者值相关的最小日期

Trying to get earliest date associated with each PatientID for this period of time.

尝试在这段时间内获得与每个PatientID相关的最早日期。

Current SQL returns multiple visits/documents within the time period for a patient I need to show only earliest date for patient tied to particular provider in date range.

当前SQL在患者的时间段内返回多次访问/文档，我需要在日期范围内仅显示与特定提供者绑定的患者的最早日期。

Multiple Dates for PatientID

PatientID的多个日期

    USE EHR 
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    DECLARE @PROV NVARCHAR (255) ='KCOOPER0'
    DECLARE @START_DATE DATETIME = '2017-09-18 00:00:00.000'    
    DECLARE @END_DATE DATETIME = '2017-12-17 23:59:59.999'    
    --DECLARE @START_DATE DATETIME = '2017-10-02 00:00:00.000'    
    --DECLARE @END_DATE DATETIME = '2017-12-31 23:59:59.999'    

    SELECT DISTINCT 
                PS.ID AS AppointmentID
                , CL.Code AS PatientID
--              , SU.NameFirst AS PROVFNAME
--              , SU.NameLast AS PROVLNAME
--              , SU.NameSuffix AS PROVSUFFIX
                , PS.ProviderId
                , PS.ScheduledDateTime AS AppointmentDT
                , PS.Duration
                , PS.[TYPE] AS TypeDescription
                , PS.IsActive as [Status]
                , PS.ExternalId  AS VisitID
  --            , REPLACE(REPLACE(LOC.[Description],'[',''),']','') AS LOCATIONPLACE
              , CDA.CreatedOn AS CDA
               FROM PatientSchedule PS
    INNER JOIN ContactsList CL WITH(NOLOCK) ON PS.PatientID=CL.ReferenceID
    AND CL.Relation = 0
       AND PS.ScheduledDateTime BETWEEN @START_DATE AND @END_DATE
    INNER JOIN SystemUsers SU WITH(NOLOCK) ON PS.InterfaceCode=SU.InterfaceCode AND SU.Status='1' 
       INNER JOIN EMRDocuments ED ON PS.ID=ED.PatientScheduleId
       AND ED.IsActive=1
       LEFT JOIN 
              (SELECT DISTINCT ED.ID
              ,SU.NPI
              ,ED.PATIENTSCHEDULEID
              ,EDE.CreatedOn
              FROM
              EMRDOCUMENTS ED
              INNER JOIN SystemUsers SU ON ED.ModifiedByID=SU.ID
              AND ED.IsActive = 1
              AND ED.IsSignedOff ='TRUE'
              INNER JOIN EMRDocumentExport EDE ON ED.ID=EDE.DocumentId
              AND EDE.LabCompanyName = 'FollowMyHealth_CCDA'
              ) CDA ON PS.ID=CDA.PatientScheduleId
    WHERE --CL.Code = @PatientID
              su.RegisteredProvider =1
              AND SU.UserID =@PROV 
       ORDER BY CL.Code, CDA.CreatedOn

2 个解决方案

#1

This is the general idea. You can fill in the details.

这是一般的想法。您可以填写详细信息。

select your fields
from your tables

join (select patientId, min(the date field you want) minDate
from your tables
where whatever
group by patientId) minDates 

on minDates.patientId = sometable.patientId
and the date field you want = minDate

etc

#2

A join-less alternative, using window function and either a Common Table Expression or sticking with a subselect.

无连接替代方案，使用窗口函数和公用表表达式或坚持使用子选择。

using CTE:

使用CTE：

with mindates as (
    select field1, field2, ..., 
        AppointmentDT,
        min(AppointmentDT) OVER (PARTITION BY PatientID) minptAppointmentDT
    from table
)

select field1, field2, ... , AppointmentDT from mindate_table
where AppointmentDT = minptAppointmentDT

using subselect:

使用subselect：

select field1, field2, ... , AppointmentDT from 
    (select field1, field2, ..., 
    AppointmentDT, 
    min(AppointmentDT) OVER (PARTITION BY PatientID) minptAppointmentDT
    from table) mindates
where AppointmentDT = minptAppointmentDT

#1