Identifying consecutive values with constraints

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

SQL Server 2008 Forums

Other SQL Server 2008 Topics

Identifying consecutive values with constraints

Author

Topic

thusi
Starting Member

25 Posts

Posted - 2008-11-19 : 04:46:51

Hi All
I have a set of blood pressure measurements like:

MMID MESDATE SYS DIA
A006283 2005-11-14 148 80
A006283 2006-01-16 130 88
A006283 2006-10-18 130 80
A006283 2006-12-28 144 96
A006283 2007-01-03 120 80
A006283 2006-05-17 130 80
A006283 2007-02-28 140 80
A006283 2007-05-03 130 80
A006283 2008-01-18 150 80
A006283 2006-06-29 130 70
...
M009781 2006-10-24 110 70
M015182 2008-07-22 130 90
M020100 2006-04-20 130 70

I want to identify sequences where SYS>=130 and DIA>=80 for all the different patients. So for patient A006283 for example, I should get:

1) A006283 2005-11-14 148 80 2006-01-16 130 88 2006-05-17 130 80
2) A006283 2007-02-28 140 80 2007-05-03 130 80 2008-01-18 120 80

cos these are the only consecutive measurements that meet the criteria (note that the raw data was not sorted!).

I'm thinking using a CROSS APPLY will get part of the required results, but I think you need to PIVOT it as well to display the multiple rows (ie. the 3 consecutives) in a single row like above.

Any help will be greatly appreciated. I've spent nearly 5hrs trying to figure this out and reading all sorts of forums..but no luck so far :(

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-19 : 04:50:36

Read http://www.sqlteam.com/article/detecting-runs-or-streaks-in-your-data

E 12°55'05.63"
N 56°04'39.26"

thusi
Starting Member

25 Posts

Posted - 2008-11-19 : 04:59:51

hmm..thanks for the quick response. Almost all the forums I've seen (similar to this one) uses some sort of an aggregate function which is not really what I need. Even if you can use a similar technique, I just don't understand how to apply it to get the result I want..so a bit of sql is very welcome :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-19 : 11:03:06

[code]SELECT t.MMID,
prev.MESDATE, prev.SYS, prev.DIA,
t.MESDATE, t.SYS, t.DIA,
nxt.MESDATE, nxt.SYS, nxt.DIA,
FROM Table t
CROSS APPLY (SELECT TOP 1 MESDATE, SYS, DIA
FROM Table
WHERE MMID=t.MMID
AND MESDATE>t.MESDATE
ORDER BY MESDATE)nxt
CROSS APPLY (SELECT TOP 1 MESDATE, SYS, DIA
FROM Table
WHERE MMID=t.MMID
AND MESDATE<t.MESDATE
ORDER BY MESDATE DESC)prev
WHERE t.SYS >=130
AND t.DIA >=80
AND prev.SYS >=130
AND prev.DIA >=80
AND nxt.SYS >=130
AND nxt.DIA >=80[/code]

thusi
Starting Member

25 Posts

Posted - 2008-11-19 : 17:02:57

Thanks Visakh..I guess this is a fine solution (which I still need to try out though), but is there any way I could make it work for 'n' consecutives as well so that it becomes a generic solution? May be I'm asking for too much..but I wonder if there is a way..

thusi
Starting Member

25 Posts

Posted - 2008-11-19 : 19:19:12

Just tried your solution out Visakh..and it works great! Thanks a lot :-)

Pls let me know if there's a way you can implement it using a more generic way for 'n' consecutives, if it's possible at all. Thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources