Question about using conditional SQL statement

[James Shao]

I have a time series data in which a numerical indicator fluctuates over time. If I want to find out for which given day, this indicator has crossed above 30 in the past 3 days. Is it possible to write a SQL query that retrieve this information? The logic to check for the crossing movement is: IF indicator(day x) > 30 AND indicator(3 days ago from day x) <30 THEN Return "YES WE HAVE AN UPWARD CROSSING MOVEMENT THROUGH 30 FOR PAST 3 DAYS!" END IF Can this be accomplished using a SQL CASE statement with a HAVING clause or something else? I'd greatly appreciate any help! If possible please provide a sample code ;P

[Ashfield]

An exists should do it - as far as I can tell from your description

select column_list....
from table1 t1
where indicator > 30
and exists (select 1 from table1 t2 where t2.pk = t1.pk and t2.indicator < 30 and t2.dateadded = dateadd(day,-3,t1.dateadded))

where PK is your primary key [Edit] I had missed the date bit from the exists originally [/Edit]

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP

modified on Tuesday, December 15, 2009 11:17 AM

[dan sh]

Please do not cross post.

50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!

[James Shao]

Thanks for your help in the other section, sorry about cross-posting. Will try to prevent that in the future. :((

[James Shao]

Hi Ashfield, Your code looks exactly like what I need! I'll give it a try after getting home. :) Many thanks again.

modified on Tuesday, December 15, 2009 10:19 PM

[Ashfield]

no problem

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP