Interesting question about a MIN function and usage

[James Shao]

I have a series of time series data. How do I construct a SQL statement to find whether the minimum (or trough) between 2 different dates is smaller than both the values on the starting date and the ending date? My table has StockName, Date, and Price column. The purpose of this query is to find all the stocks when their price slopes changes from negative to positive. :) So if a minimum point does exists between the 2 dates but is smaller than either starting and ending values, it means there's a slope change like the following: *................*.. ..*............*.... ....*........*...... .......*....*....... .........*.......... Could you please provide a sample query statement? Much thanks in advance!

[Luc Pattyn]

Hi, for begin point B and end point E, you could - select all points X between B and E; - order them by the distance between val(X) and the smallest of begin and end points = MIN(val(B),val(E)); If any record is selected, you have a V pattern, and specifying "TOP 1" would yield it. :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

---

Merry Christmas and a Happy New Year to all.

---

[James Shao]

Thank yuo Luc, that is pure elegancy, much better than my original idea which involves 2 separate comparisons. Could you please let me know how to write that query statement? I currently have the following which doesn't work.... :(( The time series data is stock price and I want to retrieve all the stocks with that V shape price pattern: [Edit] Sorry for some reason when I tried to post my code, I got an error internet message and not able post the message. Could you please show me a sample of the query? I'd greatly appreciate it!

modified on Tuesday, December 29, 2009 4:04 AM

[Luc Pattyn]

James Shao wrote:

which doesn't work

James Shao wrote:

got an error internet message

that is too vague, nobody can help you without a more accurate description. :|

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

---

Merry Christmas and a Happy New Year to all.

---

[James Shao]

Thanks for the reply Luc. Here's a more accurate discription of what my DataTable looks like: Ticker Date Price MSFT 1/2/2009 20 MSFT 1/3/2009 19 MSFT 1/4/2009 18 MSFT 1/5/2009 17 MSFT 1/6/2009 16 MSFT 1/7/2009 18 MSFT 1/8/2009 20 MFST 1/9/2009 21 DELL 1/2/2009 11 DELL 1/3/2009 12 DELL 1/4/2009 17 DELL 1/5/2009 17 DELL 1/6/2009 16 DELL 1/7/2009 15 DELL 1/8/2009 15 DELL 1/9/2009 14 AAPL ... ... ... I need a query to return all the stocks with its price first going down then going up, in other words a V pattern; and the query would need to check whether this happens for a particular date. In the example above, this happened to MSFT on 1/7/2009 when the first V patter is formed. Not sure if this can be done using SQL, but if possible, could you please help me with it? I'd be very very thankful! :)

[Corporal Agarn]

select @minval = min(stockval) from table where stockdate between d1 and d2 select @mindate = stockdate from table where stockdate between d1 and d2 and stockval = @minval This is just a starting point. Good luck

modified on Wednesday, December 30, 2009 8:13 AM

[James Shao]

Thank you djj55, a question about the query statement you wrote. Is that 2 different SQL statement, as I don't see anything between the first SELECT and the second SELECT statement. Or could you actually write a statement like that? Because I thought the @minval in the second statement is not defined....of course it's defined in the first statement, but the two statements are not connected, right?

[Corporal Agarn]

Sorry for the confusion. I did notice that I did a cut & paste instead of a copy & paste so I edited my query. There should be two queries

select @minval = min(stockval) from table where stockdate between d1 and d2;
select @mindate = stockdate from table where stockdate between d1 and d2 and stockval = @minval;

Not being a C programmer I do not normally use the semicolon which in T-SQL is not needed. Hope this helps.