Using Recursion

  • I am truly a newbie to SS2005 but keen to learn. I have some experience with MS Access. There is some preamble I need to describe to set the scene. I work in a hospital operating theatre and we have a db that links to the patient monitoring system that records all patient data. One particular piece of data being the blood pressure, "abpm". The data is recorded for the complete duration of the time they are in theatre. During this time span the patient undergoes a particular procedure that has a start and end time. As a marker of quality we try to avoid the "abpm" parameter going below a value of 40. Data is transmitted into the db every 10 secs, so over the course of the patient being in the OR for say 3hrs there are some 3 * 60 * 6 records for that one patient. Then of course there are all the other patients records as well. My first challenge is to pull out those records where they fall between the specified start and end times AND the abpm value is < 40. Well I managed to do this with the code below (note sure how elegant this is but it works). However I have to include the Patient ID (pat_nr) in this code or I get an error message in the "set" lines. What I now need to do is repeat this code (or something similar) presumably recursively to produce that same output but for ALL the patients in the file. I don't know how to replace the pat_nr field with a variable and set up the loop to go through all the patient records.

    declare @start as datetime

    declare @end as datetime

    set @start = (select date_time from a_ereig where er_m_nr = 100 and pat_nr = 2043)

    set @end = (select date_time from a_ereig where er_m_nr = 102 and pat_nr = 2043)

    select a_mon.pat_nr, abpm, date_time from a_mon

    where date_time >= @start and date_time <= @end and abpm <40

    group by a_mon.pat_nr, abpm, date_time

    order by date_time

    OUTPUT

    Patient ID abpm Date_Time

    2043 30.02008-05-19 10:15:21.000

    2043 19.02008-05-19 11:23:49.000

    2043 21.02008-05-19 11:23:59.000

    2043 34.02008-05-19 11:24:09.000

    2043 22.02008-05-19 11:34:49.000

    2043 34.02008-05-19 11:34:59.000

    2043 32.02008-05-19 11:37:10.000

    2043 31.02008-05-19 11:40:20.000

    2043 0.02008-05-19 11:44:41.000

    I hope this makes some sense and I really appreciate any guidance or pointers

    Ian

  • From the looks of it you should be getting multiple pat_nr with your query... Clean up your code a bit....

    You are assigning values to @start and @end from another table... I am presuming that table is when the pat_nr first came to the hospital and when they left..... However your query as it stands will give you all patients that are for a certain date range...

    I did add one line of code so that you can run this by patient or for all patients in a date range

    SELECT

    pat_nr,

    abpm,

    date_time

    FROM

    a_mon WITH (NOLOCK)

    WHERE

    pat_nr = COALESCE(@pat_nr, pat_nr)

    AND date_time BETWEEN @start AND @end

    AND abpm <40

    GROUP BY

    pat_nr

    ,abpm

    ,date_time

    ORDER BY

    date_time

  • Thanks for your prompt reply to my problem. I think I have not quite explained myself clearly enough.

    The patient arrives in the OR at say 8am and leaves at 11am and during this time all their vital parameters are recorded and sent to my db. During that 3hr time span in the OR there is a critical period say between 9am and 10am where I am very concerned that a particular parameter, abpm, does not go <40. Firstly I need to pull out all the records just between 9am and 10am which is why I declared the variables @start and @end and set them from another table where the exact start / end times are recorded. Trouble is that in using the statement:

    SET @start = (SELECT date_time FROM a_ereig WHERE er_m_nr = 100)

    I also have to include in the WHERE clause the patient identifier pat_nr beacuse the table a_ereig holds the start / end times of ALL the patients we have recorded in the database. If I omit it I get an error message "Subquery returned more than 1 value......" So by including the pat_nr in the SET statement fro @start and @end:

    set @start = (select date_time from a_ereig where er_m_nr = 100 and pat_nr = 123)

    set @end = (select date_time from a_ereig where er_m_nr = 102 and pat_nr = 123)

    the rest of the code:

    SELECT a_mon.pat_nr, abpm, date_time FROM a_mon

    WHERE date_time >= @start AND date_time <= @end AND abpm <40

    does exactly what I want, BUT only for that one patient, in this case whose pat_nr is 123. (Output above).

    I want to be able to exclude using the pat_nr constant and loop through this code using all the different pat_nr values found in the table a_ereig (to get their @start and @end values) and have a result that has all the records for each pat_nr in the table where the parameter abpm is < 40.

    I hope this is a bit clearer and that my logic makes sense, as a newbie maybe my apporach is all wrong??

    Thanks...

  • Can you please provide the following....

    table structure

    a_mon

    a_ereig

    Small Data Extract from both tables....

    Thank you

  • I hope the attachments get through, I scripted the table structures into two .sql files but as these are not valid extensions I have combined the two scripts into one text file and the sample data is a cut and paste into a .txt file, fingers crossed.

    The field names won't make much sense as this db has been transposed from one originally written in another language. Although the data is pretty meaningless please destroy once used.

    Many thanks for your help..

  • Try this, see if it gives you what you need:

    ;with

    Starts as

    (select pat_nr, date_time

    from a_ereig

    where er_m_nr = 100),

    Ends as

    (select pat_nr, date_time

    from a_ereig

    where er_m_nr = 102)

    SELECT a_mon.pat_nr, abpm, a_mon.date_time

    FROM a_mon

    inner join Starts

    on a_mon.pat_nr = Starts.pat_nr

    inner join Ends

    on a_mon.pat_nr = Ends.pat_nr

    where

    a_mon.date_time

    between

    Start.date_time

    and

    Ends.date_time

    and abpm <= 40

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Thanks G-Squared,

    Brilliant, that appears to do just what I need, now to examine the code and try to understand what went on!!

    If it's not too cheeky, if I want to put this resulting data set into a new table do I just replace the SELECT with a SELECT INTO and then add the table name at the end?

    Again many thanks,

    Ian.

  • select into will create a new table.. (or bomb if you already have that table name used)

    INSERT INTO however is what you are looking for

  • I think something like this will work for you:

    select

    pat_nr,

    abpm,

    date_time

    from

    a_mon a

    where

    date_time >= (select date_time from a_ereig e1 where er_m_nr = 100 and a.pat_nr = e1.pat_nr)

    AND date_time <= (select date_time from a_ereig e2 where er_m_nr = 102 and a.pat_nr = e2.pat_nr)

    AND abpm <40

    group by

    a_mon.pat_nr,

    abpm,

    date_time

    order by

    date_time

    That turns your time queries into subqueries instead of trying to get just one value and this should return the value for all patients in the database.

    I don't think you want to do this with recursion. If you want to avoid the subqueries for some reason, or this doesn't work for you, then I would either try a loop over all patient_ids or a cursor, but be warned that both of those invoke RBAR (Row by agonizing row to borrow an acronym from Jeff Moden) and the cursors can be particularly slow.

    If you want to explore recursion (which I do not particularly recommend for this problem), you can set it up through a recursive CTE. Those are discussed from different angles from numerous articles, like this one:

    http://www.sqlservercentral.com/articles/CTE/62404/

    or this one:

    http://www.sqlservercentral.com/articles/SQL+Server+2005+-+TSQL/recursivequeriesinsql1999andsqlserver2005/1846/

    Edit: I forgot to make the subqueries correlated the first time. This should be better now.

    ---
    Timothy A Wiseman
    SQL Blog: http://timothyawiseman.wordpress.com/

  • Sadly, even with your code edited I still get the error message that more than 1 value is returned by the query. I'm not smart enough to figure that one out, but I'll still look at using loops which I guess is what I really meant by recursion.

    Thanks to all who have helped me unravel this problem, I'm sure I'll be back again 😉

  • eyejay (9/4/2008)


    Sadly, even with your code edited I still get the error message that more than 1 value is returned by the query. I'm not smart enough to figure that one out, but I'll still look at using loops which I guess is what I really meant by recursion.

    Thanks to all who have helped me unravel this problem, I'm sure I'll be back again 😉

    Just to make sure I was clear, with the edited query that uses the subqueries, you don't need any variables. I cannot gauruntee that the query I provided will work since I have tested it, but you should not get that type of error with it.

    If you can't get the subqueries to work, then a loop should definitely do it.

    ---
    Timothy A Wiseman
    SQL Blog: http://timothyawiseman.wordpress.com/

Viewing 11 posts - 1 through 10 (of 10 total)

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