SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Sub query or specific coding ???
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Fratton
Starting Member

5 Posts

Posted - 02/11/2013 :  07:21:47  Show Profile  Reply with Quote
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
2208 Posts

Posted - 02/11/2013 :  07:42:22  Show Profile  Reply with Quote
Can you post some sample data and expected output?

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/11/2013 :  10:14:00  Show Profile  Reply with Quote

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/

Go to Top of Page

Fratton
Starting Member

5 Posts

Posted - 02/12/2013 :  07:42:52  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/13/2013 :  05:34:43  Show Profile  Reply with Quote
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/

Go to Top of Page

Fratton
Starting Member

5 Posts

Posted - 02/14/2013 :  04:06:03  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/14/2013 :  09:40:22  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000