calculating business days from aux calendar table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

calculating business days from aux calendar table

Author

Topic

Zim327
Yak Posting Veteran

62 Posts

Posted - 2010-02-19 : 10:22:44

Hi,
I've been using an auxiliary calendar table and I need to calculate a
date that is 5 business days prior to a given date (not including
this date).
for example, if the received date is 2010-03-16 then the result should be 5 business days before that which is March 9th.

I created this query (it's not great but it works)


select top(5) dt from dbo.calendar where isHoliday = 0 and isWeekday=
 1 and dt < '2010-03-16' order by dt desc;

the Result is the last record:
2010-03-15 00:00:00
2010-03-12 00:00:00
2010-03-11 00:00:00
2010-03-10 00:00:00
2010-03-09 00:00:00

is there a better way to do this?

Thanks,

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-19 : 10:43:56

[code]
select min(dt)
from
(
< your query here >
) q
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Zim327
Yak Posting Veteran

62 Posts

Posted - 2010-02-19 : 12:18:18

Hey thanks!

I changed it slightly and it works!


select min(dt) from dbo.calendar where dt in (select top(5) dt from dbo.calendar where isHoliday = 0 and isWeekday = 1 and dt < '2010-03-16' order by dt desc)

Now why didn't I think of that?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources