Number of Holidays Logic! - 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

General SQL Server Forums

New to SQL Server Programming

Number of Holidays Logic!

Author

Topic

amodi
Yak Posting Veteran

83 Posts

Posted - 2012-10-05 : 16:18:02

Dear All,

I have a table tAuxCalendarDates with the following columns:

Id int IDENTITY(1,1) NOT NULL,
Date datetime NOT NULL,
IsHoliday bit NOT NULL

tAuxCalendarDates has all the dates as per calendar and Fridays are marked as Holiday(IsHoliday is set to 1) as follows:

ID---------Date--------IsHoliday
279----- 2012-10-05----- 1
280----- 2012-10-06----- 0
281----- 2012-10-07----- 0
282----- 2012-10-08----- 0
283----- 2012-10-09----- 0
284----- 2012-10-10----- 0
285----- 2012-10-11----- 0
286----- 2012-10-12----- 1

Now my requirement is to calculate number of holidays
between @StartDate and @EndDate and add it to the @EndDate,but the new @EndDate should not be makred as Holiday i.e, IsHoliday=1.

For Example:
declare @StartDate datetime
declare @EndDate datetime

set @StartDate=GETDATE() --(2012-10-05)
set @EndDate=GETDATE()+6 --(2012-10-11)

select @ActualHolidays = count(1) from tAuxCalendarDates where Date between @StartDate and @EndDate and IsHoliday=1

So i will get new @EndDate as follows:
set @EndDate = @EndDate + @ActualHolidays -- which will be (2012-10-12 FRIDAY, IsHoliday=1)

Note 1 : Only Fridays are not holidays any date can be marked as Holiday(Festivals, Govermnent Holidays).

Note 2 : I can achieve this using a while loop, but performance is my concern.

Thanks!

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-10-05 : 17:48:22

It should be doable to calculate the simple end date and then find the first (minimum) non-holiday that comes on or after that date. Something along the lines of:[CODE];with cteSimpleEndDate
as select
<calculate simpleEndDate>
from
Calendar c1 (blah, blah, blah)
)
select min(c.MyDate)
from Calendar c
inner join cteSimpleEndDate sed
on c.MyDate >= sed.SimpleEndDate
where
c.IsHoliday = 0[/CODE]

=================================================
We are far more concerned about the desecration of the flag than we are about the desecration of our land. -Wendell Berry

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-10-05 : 17:48:49

In your example, what do you want for output 2012-10-13?

amodi
Yak Posting Veteran

83 Posts

Posted - 2012-10-05 : 17:52:51

Thanks Bustaz Kool, I will check and will get back to you.

Yes Lamprey, I want the output to be 2012-10-13

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources