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;
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".
November 6, 2024 at 3:10 am
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
November 7, 2024 at 2:50 am
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
November 15, 2024 at 9:29 am
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