Posted by Erland Sommarskog on 07/11/06 21:10

metaperl (metaperl@gmail.com) writes:
> Erland Sommarskog wrote:
>> CREATE TABLE tradingdays (date datetime NOT NULL,
>> dayno int NOT NULL,
>> CONSTRAINT pk_tradingdays PRIMARY KEY (date),
>> CONSTRAINT u_tradingdays UNIQUE (dayno))
>>
>> That is a table that maps a day to a number. The rest is easy.
>
> I'm not following this.
>
> - how would this table be populated and what with?

Well, from somewhere you do get the information that a day is a trading
day or not. So you insert the trading day by some means, and for each
day you add, you increment the day numer.

> - how does the day number tell you that a day is not a weekend or
> holiday?

It doesn't. The point is that you only add the trading days to the
table (whence the name). So to get the trading day 50 days ago from
today, you say:

SELECT a.date
FROM tradingdays a
JOIN tradingdays b ON a.dayno = b.dayno - 50
WHERE b.date = '20060711'


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: Problem with INSERT Trigger
• Prev in forum: Re: Backup and Restore on different computer?
• Thread view: Re: a newbie question on derived/computed column

[Reply to this message]