Post reply

Update based on CTE - can this be done in one step?

  • October 29, 2014 at 10:39 am

    #312095

    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

    #1755593

    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.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • October 29, 2014 at 11:00 am

    #1755596

    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