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.
Author |
Topic |
Fratton
Starting Member
5 Posts |
Posted - 2013-02-11 : 07:21:47
|
Hi AllI 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 readingGraham |
|
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 mCROSS APPLY (SELECT TOP 1 creatinine, [sample date] from Main where surname = m.surnameAND forename = m.forename and DoB = m.DoBAND [sample date] < m.[sample date]order by [sample date] DESC) nCROSS APPLY (SELECT TOP 1 creatinine, [sample date] from Mainwhere surname = m.surnameAND forename = m.forename and DoB = m.DoBAND [sample date] > m.[sample date]order by sample date ASC)pwhere m.source = 'Casualty'[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 informationSurname Forename Source Creat Sample dateTEST ADRIAN GP 80 1-Jan 2013.... ...... .... .... ........TEST ADRIAN CASUALTY 120 30-Jan-2013.... ...... ........ ..... ...........TEST ADRIAN ITU 240 3-Feb-2013The ideal output would be something along the lines of TEST, ADRIAN, 120, 30-Jan-2013, 80, 1-Jan-2013, 240, 3-Feb-2013This 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 tweakThanks for readingGraham |
|
|
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 likeSELECT m.surnam, m.forename, m.dob, m.creatinine, m.[sample date],COALESCE((SELECT COUNT(*) from Main where surname = m.surnameAND forename = m.forename and DoB = m.DoBAND [sample date] < m.[sample date]),0) +1 AS cntINTO #Tempfrom Main mSELECT t1.*,t2.creatinine,t2.[sample date],t3.creatinine,t3.[sample date] FROM #temp t1JOIN #temp t2ON t1.surname = t2.surnameAND t1.forename = t2.forename and t1.DoB = t2.DoBAND t1.cnt = t2.cnt + 1JOIN #temp t3ON t1.surname = t3.surnameAND t1.forename = t3.forename and t1.DoB = t3.DoBAND t1.cnt = t3.cnt - 1where t1.source = 'Casualty' ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 thanksGraham |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|