1

Let me know how to find turning price points in stock data in SQL. for example we have following data columns and N rows:

Date     |Price|
20150101 | 100  |
20150102 | 50   |
20150103 | 80   |
     .
     .
     .
201708027 | 200  |

and I want to find the turning points (date and price) , to understand clearly check following image.please help me how to find red points. Sample

MahdiIBM
  • 55
  • 7
  • Do you want to forecast? Then you are looking for Holt-Winters-Brown smoothing and "changepoint analysis" (as you need the inflection points on the smoothed curve to filter out the noise). – Koshinae Aug 27 '17 at 10:49
  • @Koshinae Actually I am looking for the past buy and sell price and then implement those for future stock price – MahdiIBM Aug 27 '17 at 15:04
  • You still have to smooth the curve to find the inflection points. – Koshinae Aug 27 '17 at 15:18

1 Answers1

5

Using SELF JOIN:

SELECT cur.*
FROM tab cur
JOIN tab prev
  ON cur.date = prev.date + 1
JOIN tab next
  ON cur.date = next.date - 1
WHERE cur.price > prev.price AND cur.price > next.price;   -- peak
-- WHERE cur.price < prev.price AND cur.price < next.price; -- bottom

Using LEAD and LAG will be much more readable but they are available from SQL Server 2012+.

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • Based on the question, this is the right answer. I would be surprised if there are quotes every day. If so, the OP should accept this answer and ask *another* question, with more suitable sample data and a better explanation of what to do in the even of holidays and weekends. – Gordon Linoff Aug 27 '17 at 09:48
  • @lad2025 thanks for your answer but it doesn't accurate enough to me. – MahdiIBM Aug 27 '17 at 15:05