November 5, 2024 at 7:39 pm
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;
November 5, 2024 at 7:47 pm
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