September 3, 2008 at 4:06 pm
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
September 3, 2008 at 4:18 pm
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
September 4, 2008 at 3:07 am
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...
September 4, 2008 at 9:00 am
Can you please provide the following....
table structure
a_mon
a_ereig
Small Data Extract from both tables....
Thank you
September 4, 2008 at 9:58 am
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..
September 4, 2008 at 10:11 am
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
September 4, 2008 at 10:27 am
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.
September 4, 2008 at 10:30 am
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
September 4, 2008 at 10:41 am
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:
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/
September 4, 2008 at 12:30 pm
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 😉
September 4, 2008 at 7:24 pm
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