Calculate 2 business days - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Calculate 2 business days

Author

Topic

ph1long
Starting Member

16 Posts

Posted - 2012-11-02 : 12:30:11

Hi,

I have a Calendar table. I would like get 2 business days before Today's Date while skipping Holidays and weekeends.

For example:

Example 1: New years 2013 falls on a Tuesday. If it was Thursday (1/3), then my starting date would be Monday (12/31).

Example 2: If it was Monday (12/31), then my starting date would be Thursday (12/27).

My Calendar table as been prepopulated with 30 years worth of dates and have flags to determine which ones are weekend and hollidays. The table has these fields:

dt: Date and time for 30 years
Y: year
D: Day
M: Month
Isholiday: (Is a holiday are not, flagged with 0 for "no" and 1 for "Yes")
IsWeekday: (Is a weekday? Flagged with 0 for "no" and 1 for "Yes" HolidayDescritption: Holiday Names

Please help.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2012-11-02 : 13:25:43

[code]select top 2
*
from
MyCalendarTable
where
-- After 10 days before today
MyDate > dateadd(dd,-10,getdate) and
-- Before today
MyDate < dateadd(dd,datediff(dd,0,getdate()),0) and
Isholiday = 0 and
IsWeekday = 1
order by
MyDate desc[/code]

CODO ERGO SUM

ph1long
Starting Member

16 Posts

Posted - 2012-11-02 : 13:49:39

If it Is Monday (12/31), the start date should be Thursday 12/27, but the above solution gives me Friday 12/28 and 12/27Friday is only one business day before Monday, not 2. Is there anyway to come back with only 1 correct result?

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-02 : 15:18:33

[code]SELECT TOP 1 * FROM (
select top 2
*
from
MyCalendarTable
where
-- After 10 days before today
MyDate > dateadd(dd,-10,getdate) and
-- Before today
MyDate < dateadd(dd,datediff(dd,0,getdate()),0) and
Isholiday = 0 and
IsWeekday = 1
order by
MyDate DESC
) s ORDER BY MyDate ASC[/code]

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2012-11-02 : 16:06:28

quote:
Originally posted by ph1long

If it Is Monday (12/31), the start date should be Thursday 12/27, but the above solution gives me Friday 12/28 and 12/27Friday is only one business day before Monday, not 2. Is there anyway to come back with only 1 correct result?

Thanks

In your post, you said "I would like get 2 business days before Today's Date while skipping Holidays and weekends." so I gave you the two business days you asked for.

Plus the title of this thread is "Calculate 2 business days".

It's hard to read that as wanting anything other that 2 business days.

CODO ERGO SUM

ph1long
Starting Member

16 Posts

Posted - 2012-11-02 : 17:15:16

Thank Michael and sunitabeck.

Sunitabeck's variation of Michael's query worked.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources