1

I have daily stock data stored in a table, and I want to be able to query something like "What is the data from the last 5 trading days". The problem is that I have no data for weekends or holidays, which throws off the day count.

From the other similar questions I see that some people have a holiday table where they store the holidays, but I'm hoping to avoid that, since I know that if there's no data, then that day was a weekend or holiday.

The only way I was thinking I could do this is to first run an initial query that enumerated each trading day sequentially, and then I can run a query that way, but I was hoping maybe there was a better way of doing it.

steve8918
  • 1,820
  • 6
  • 27
  • 38
  • Relevant: http://stackoverflow.com/questions/457176/how-do-i-determine-a-public-holiday-in-sql-server – Matt Ball Mar 27 '12 at 00:45
  • 3
    [Why should I consider using an auxiliary calendar table?](http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html) – onedaywhen Mar 27 '12 at 07:44
  • Thanks for the link, I guess the best practice is to have a separate table so I'll go with that. – steve8918 Mar 27 '12 at 15:05

2 Answers2

3

Have a 'table of days', table with one row for each day, and a column specifying that whether is a trade day or not. There is really no reason to oppose this solution, it has been proven efficient and correct many many many many times before. Don't try to outsmart this simple solution, you will only cause yourself pain.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • Thanks Remus, after reading the link above I guess the best practice is to have a separate table, so I'll go that route. – steve8918 Mar 27 '12 at 15:05
1

Your best option is to use the DENSE_RANK option, ranking on trading date. The example below assumes you're using SQL 2008.

CREATE TABLE #test (id INT IDENTITY(1, 1), trading_date DATETIME, stock_key INT, stock_count INT)

;WITH dates AS(SELECT DISTINCT DENSE_RANK() OVER (ORDER BY CAST(trading_date AS DATE) DESC) AS days_ago, 
        CAST(trading_date AS DATE) AS trading_date FROM #test)
SELECT t.* FROM #test t, dates d
WHERE d.days_ago = 5 AND t.trading_date >= d.trading_date

The common table expression is strictly necessary, but I've formatted it like this to make it more readable. if you're using SQL 2005, you'll need to change the cast to date as to

CAST(FLOOR(CAST(trading_date AS FLOAT)) AS DATETIME)

To retrieve only the date portion of the datetime field. Of course, if you're using 2008 and you have a DATE field type to begin with, you can do away with the cast altogether.

Peter
  • 1,055
  • 6
  • 8