Need help with DATEDIFF function

[James Shao]

I have a stock time series data in which a numerical indicator fluctuates over time. If I want to find out for each stock that has its indicator now over 30, how many days since the last time this indicator is less than 30? In other words, how many days since the last time the stock's indicator crosses above 30? Just for your reference, currently I have the following: SELECT ticker, DATEDIFF(d,(SELECT MAX(DATE) FROM....),'12/21/2009') WHERE Indicator > 30 AND Date = '12/21/2009' I'd greatly appreciate any help! :) If possible please provide a sample code. By the way, thank you Ashfield for your help on a similar question last time, later I was able to understand the code you gave me and utilized it in different ways. This is a bit more difficult for me though.

modified on Monday, December 21, 2009 12:06 AM

[Ashfield]

How about something like

select ticker,datediff(day,t.date,(select max(date) from table t1
where t1.ticker = t.ticker and t1.indicator < 30))
from table t

You may have to play about a bit, but it should be somewhere close

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

[James Shao]

Thank you Ashfield, I'll test it out later and come back if I still have an issue.