Sub query or specific coding ??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sub query or specific coding ???

Author

Topic

Fratton
Starting Member

5 Posts

Posted - 2013-02-11 : 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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-11 : 07:42:22

Can you post some sample data and expected output?

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-11 : 10:14:00

[code]
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'
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Fratton
Starting Member

5 Posts

Posted - 2013-02-12 : 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

52326 Posts

Posted - 2013-02-13 : 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

5 Posts

Posted - 2013-02-14 : 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

52326 Posts

Posted - 2013-02-14 : 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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources