October 29, 2014 at 10:39 am
Hello,
I have some code that works, but what I'd like to know is whether the update can be done directly in the CTE statement instead of in a separate update afterwards.
The data is for patients and their admissions and discharges from hospital. The calculation is to store the previous discharge date against the next admission.
if object_id('tempdb..#tmp1') is not null drop table #tmp1
-- create a temporary table
declare @PatientEpisodes table (
ID int,
PatientID int,
admissiondate date,
dischargedate date,
lastdischarge date)
-- populate it with some data
insert into @PatientEpisodes values
(1,1,'20140103','20140120',null),
(2,1,'20140125','20140127',null),
(3,1,'20140130','20140205',null),
(4,2,'20140406','20140407',null),
(5,3,'20140401','20140410',null),
(6,3,'20140411','20140411',null),
(7,2,'20140420','20140422',null),
(8,3,'20140501','20140510',null)
--select * from @PatientEpisodes
-- set the LastDischarge field which shows the previous discharge date for the same patient
;WITH cte AS (
SELECT ID, patientid,admissiondate, dischargedate,
row_number()over(partition BY patientid ORDER BY admissiondate) AS visitsequence
FROM @PatientEpisodes
)
select LastDischarge = visit1.dischargedate,
v2 = visit2.ID
into #tmp1
from cte visit1
INNER JOIN cte visit2 ON visit1.PatientID = visit2.PatientID
WHERE visit1.visitsequence + 1 = visit2.visitsequence -- next visit in sequence
AND visit2.admissiondate > visit1.dischargedate -- visit 2 admission must be after visit 1 discharge
update t1
set t1.LastDischarge =
t.LastDischarge
from @PatientEpisodes t1
inner join #tmp1 t on t1.ID = t.v2
-- let's have a look at the data now
select * from @PatientEpisodes order by PatientID, admissiondate
The CTE links back to itself and then stores the intermediate results in #tmp1, and the main table is updated from the data in #tmp1. This is what's confusing me when I'm trying to write it as an update query instead of a select query.
Thanks in advance,
Daniel
October 29, 2014 at 10:51 am
In this case, you can update the CTE without problems. You just need to add the column you're updating.
WITH cte AS (
SELECT ID, patientid,admissiondate, dischargedate, lastdischarge,
row_number()over(partition BY patientid ORDER BY admissiondate) AS visitsequence
FROM @PatientEpisodes
)
UPDATE visit2 SET
lastdischarge = visit1.dischargedate
from cte visit1
INNER JOIN cte visit2 ON visit1.PatientID = visit2.PatientID
WHERE visit1.visitsequence + 1 = visit2.visitsequence -- next visit in sequence
AND visit2.admissiondate > visit1.dischargedate; -- visit 2 admission must be after visit 1 discharge
Remember, a semicolon (;) is a statement terminator. It's not intended to go at the beginning of a statement.
October 29, 2014 at 11:00 am
Thanks, I was trying to write it with update @PatientEpisodes because that's the name of the table I wanted to write the data into. It all seems very obvious when you can see the answer in front of you!
Daniel
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply