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".

  • Setup:

    USE tempdb;
    GO

    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)
    );
    GO

    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');

    (Sorry, reading that was making me dizzy).

    Something like this?

    SELECT pv.PatID, pv.AcctID, pv.ProcDate, oa.Department, oa.DeptDate, oa.duration
    FROM #ProcValues pv
    OUTER APPLY (
    SELECT TOP 1 dv.Department, dv.DeptDate, duration = ABS(DATEDIFF(day, pv.ProcDate, dv.DeptDate))
    FROM #Deptvalues dv
    WHERE dv.PatID = pv.PatID
    AND ABS(DATEDIFF(day, pv.ProcDate, dv.DeptDate)) <= 1
    ORDER BY ABS(DATEDIFF(day, pv.ProcDate, dv.DeptDate)) ASC
    ) oa
  • Alternately

    with rn_cte as (
    select pv.PatID, pv.AcctID, pv.ProcDate,
    iif(v.abs_diff>=2, null, dv.Department) Department,
    iif(v.abs_diff>=2, null, dv.DeptDate) DeptDate,
    v.abs_diff,
    row_number() over (partition by pv.PatID, pv.AcctID order by v.abs_diff) rn
    from #ProcValues pv
    join #Deptvalues dv on pv.PatID=dv.PatID
    cross apply (values (abs(datediff(day, pv.procdate, dv.deptdate)))) v(abs_diff))
    select *
    from rn_cte
    where rn=1
    order by PatID, AcctID, ProcDate;

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

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