| Author |
 Topic  |
|
|
Fratton
Starting Member
3 Posts |
 Posted - 02/11/2013 : 07:21:47
|
Hi All
I am not really familiar with SQL and what can be achieved but I need a fairly quick answer otherwise I'll have to go with what I know will work even though it may not be efficient or quicker.
I have a table (Main) that contains all results for a test called creatinine together with the patient details and the sample date. There are around 1M records so far.
What I need to extract is all records where the requesting source is the Casualty department (I'm happy with that part). However, I also need to pull, for each patient at the same time, the previous record and the next record. The am of this is that I can then use the data to assess the risk of acute kidney injury at the time of Casualty admission rather than 2-3 days down the line.
Coding wise I can do this quite happily by having a single SQL statement getting me all casualty records and then having 2 more statements to get the previous and the next record. However, I'd rather be able to do this on the fly using SQL as I may have to change the search parameters at a later date if a new research question arises.
So the real question is how/cam I format an SQL statement that essentially is:
1. SELECT surnam, forename, dob, creatinine, sample date from Main where source = 'Casualty'
2. SELECT TOP 1 creatinine, sample date from Main where surname, forename and DoB are the same as in statement 1 and the sample date < sample date in 1 order by sample date DESC.
3. SELECT TOP 1 creatinine, sample date from Main where surname, forename and DoB are the same as in statement 1 and the sample date > sample date in 1 order by sample date ASC.
Many thanks for reading
Graham |
|
|
bandi
Flowing Fount of Yak Knowledge
India
1419 Posts |
Posted - 02/11/2013 : 07:42:22
|
Can you post some sample data and expected output?
--
Chandu |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 02/11/2013 : 10:14:00
|
SELECT m.surnam, m.forename, m.dob, m.creatinine, m.[sample date],n.*,p.*
from Main m
CROSS APPLY (SELECT TOP 1 creatinine, [sample date]
from Main
where surname = m.surname
AND forename = m.forename
and DoB = m.DoB
AND [sample date] < m.[sample date]
order by [sample date] DESC) n
CROSS APPLY (SELECT TOP 1 creatinine, [sample date]
from Main
where surname = m.surname
AND forename = m.forename
and DoB = m.DoB
AND [sample date] > m.[sample date]
order by sample date ASC)p
where m.source = 'Casualty'
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Fratton
Starting Member
3 Posts |
Posted - 02/12/2013 : 07:42:52
|
Sorry but should have said that we are on SQL Server 2000 so can't use the CROSS APPLY.
Basically the sample table holds all information
Surname Forename Source Creat Sample date
TEST ADRIAN GP 80 1-Jan 2013
.... ...... .... .... ........
TEST ADRIAN CASUALTY 120 30-Jan-2013
.... ...... ........ ..... ...........
TEST ADRIAN ITU 240 3-Feb-2013
The ideal output would be something along the lines of
TEST, ADRIAN, 120, 30-Jan-2013, 80, 1-Jan-2013, 240, 3-Feb-2013
This way I can use stats programs to isolate the pre and post admission changes and see if we can use the AKIN criteria for acute kidney injury as they stand for acute admissions or whether they need a tweak
Thanks for reading
Graham |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 02/13/2013 : 05:34:43
|
then you've to use a temporary table and use logic like
SELECT m.surnam, m.forename, m.dob, m.creatinine, m.[sample date],
COALESCE((SELECT COUNT(*)
from Main
where surname = m.surname
AND forename = m.forename
and DoB = m.DoB
AND [sample date] < m.[sample date]),0) +1 AS cnt
INTO #Temp
from Main m
SELECT t1.*,
t2.creatinine,t2.[sample date],
t3.creatinine,t3.[sample date]
FROM #temp t1
JOIN #temp t2
ON t1.surname = t2.surname
AND t1.forename = t2.forename
and t1.DoB = t2.DoB
AND t1.cnt = t2.cnt + 1
JOIN #temp t3
ON t1.surname = t3.surname
AND t1.forename = t3.forename
and t1.DoB = t3.DoB
AND t1.cnt = t3.cnt - 1
where t1.source = 'Casualty'
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Fratton
Starting Member
3 Posts |
Posted - 02/14/2013 : 04:06:03
|
Thanks, that cracked it with a some minor modifications and has given me quite a bit of statistical data so much appreciated that you took the time.
Another question though, is it possible to get the lowest (MIN) value within a set timescale for the previous creatinine using this method. The criteria state that acute kidney injury is defined as the lowest value in the previous 48 ours. Whilst my application does this quite happily and issues alerts to clinicians, I'd like to get the stats on the previous values used (should have thought of this at the outset but the concern was the patients and not cracking statistics).
Many thanks
Graham |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 02/14/2013 : 09:40:22
|
yep... you just need to take MIN() over creatinine field instead of count in that case inside subquery
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Sub query or specific coding ???
