Closest to ProcDate

  • Not sure I have this 2nd left join correct. I need to grab the closest #Deptvalues.DeptDate and #Depvalues.Department within 1 day (before or after) of #ProcValues.ProcDate for each row. Each PatID and ProcDate combination in the #ProcValues table should look at the #Deptvalues table and create this connection. The desired outcome is enclosed. Appreciate any help.

     

    drop table if exists #procvalues

    drop table if exists #deptvalues

    create table #Procvalues

    (

    PatID varchar(10)

    ,AcctID varchar(10)

    ,ProcDate date

    )

    create table #Deptvalues

    (

    PatID varchar(10)

    ,DeptDate date

    ,Department varchar(10)

    )

    Insert into #Procvalues

    values

    ('1','28304','2021-01-21')

    ,('2','34893','2021-01-17')

    ,('2','33845','2021-01-19')

    ,('3','39483','2021-01-13')

    ,('4','39056','2021-01-25')

    ,('5','29112','2021-01-09')

     

    Insert into #Deptvalues

    values

    ('1','2021-01-21', 'Dept1')

    ,('2','2021-01-15', 'Dept3')

    ,('2','2021-01-18', 'Dept1')

    ,('3','2021-01-27', 'Dept2')

    ,('4','2021-01-25', 'Dept1')

    ,('4','2021-01-27', 'Dept2')

    ,('5','2021-01-09', 'Dept1')

    ,('5','2021-01-10', 'Dept2')

    ;with ProcCTE as (

    SELECT

    dv.PatID

    ,dv.DeptDate

    ,dv.Department

    ,pv.ProcDate

    ,ABS(DATEDIFF(d,dv.DeptDate,pv.ProcDate)) AS DateDiff

    FROM #Deptvalues dv

    join #Procvalues pv

    on pv.PatID=dv.PatID

    WHERE ABS(datediff(d, dv.deptdate, pv.procdate))<=1

    )

    Select

    pv.PatID

    ,pv.AcctID

    ,pv.ProcDate

    ,dv.DeptDate

    ,CASE

    WHEN dv.DeptDate = pv.ProcDate THEN dv.Department --Same Date

    ELSE ns.department -- Else return the nearest department

    END AS Department

    FROM #Procvalues pv

    left join #Deptvalues dv

    on dv.PatID=pv.PatID and dv.DeptDate=pv.ProcDate

    left join

    (SELECT top 1 pc.PatID, pc.Department, pc.DeptDate

    FROM ProcCTE pc

    WHERE DateDiff = 1

    ORDER BY pc.DeptDate

    ) ns ON pv.PatId = ns.PatID;

    Attachments:
    You must be logged in to view attached files.
  • I haven't reviewed it all fully, but I would think you'd want an OUTER APPLY rather than a LEFT JOIN:

    ...
    OUTER APPLY

    (SELECT top (1) pc.PatID, pc.Department, pc.DeptDate

    FROM ProcCTE pc

    WHERE pc.PatID = pv.PatId AND pc.DateDiff = 1

    ORDER BY pc.DeptDate

    ) AS ns

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply