Bank Holidays - 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 2000 Forums

SQL Server Development (2000)

Bank Holidays

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-11-28 : 09:13:08

writes "Hi,
I've recently bought some data covering the next 20 years giving me the date of every Bank holiday in England Scotland and Ireland. So I can basically tell which one of these is a working day. The data looks like this


Calendar_Date   - smallDateTime
Ireland_status  - char 3
England_status  - char 3
Scotland_status - char 3

The char fields have several values anything numeric is a Bank holiday anything with a w is a weekend and anything with a g is good. and wg is weekend good - i.e. a Saturday. What I need to do is work out with 2 parameters a start date and an offset the next working day. So assuming no bank holidays if I pass the values (fri 10th, 1) it should return Tuesday the 14th as Monday is the working day with 2 it would return Wednesday the 15th and assuming the Thursday was a bank holiday with 3 it would return Friday the 17th.
By the way for a business day to be good it needs to be good in all 3 places ie



without saturdays = 
Ireland_status  = 'g' AND England_status  = 'g' AND Scotland_status = 'g'

with Saturdays 

Ireland_status  IN ('g', 'wg') AND England_status  IN ('g, 'wg')  AND Scotland_status IN ('g, 'wg')

I'm banging my head a little on this one and hoping you can help,

Cheers

Andrew""

ToddV
Posting Yak Master

218 Posts

Posted - 2001-11-28 : 10:02:23

I think this query will get your results.

Create Table #TEMP(
Calendar_Date Smalldatetime,
Ireland_status char( 3),
England_status char( 3),
Scotland_status char( 3))

Insert #TEMP VALUES('20011110','g','g','g')--Firday From Your example
Insert #TEMP VALUES('20011111','wg','wg','wg')
Insert #TEMP VALUES('20011112','w','w','w')
Insert #TEMP VALUES('20011113','g','g','g')
Insert #TEMP VALUES('20011114','g','g','g')
Insert #TEMP VALUES('20011115','g','g','g')
Insert #TEMP VALUES('20011116','1','1','1')
Insert #TEMP VALUES('20011117','g','g','g')

Declare @StartDate Datetime,
@Offset INT

Set @StartDate = '20011101'
Set @Offset = 3

SELECT A.Calendar_Date, Count(*)
FROM #TEMP A
JOIN #Temp B ON A.Calendar_Date > B.Calendar_Date
WHERE A.Calendar_date > @StartDate AND
B.Ireland_status = 'g' AND
B.England_status = 'g' AND
B.Scotland_status = 'g' AND
A.Ireland_status = 'g' AND
A.England_status = 'g' AND
A.Scotland_status = 'g'
GROUP BY A.Calendar_Date
Having Count(*)-1 = @Offset

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2001-11-28 : 11:13:29

Try that on a 7000-odd row date table

I would start by numbering working days.

ToddV
Posting Yak Master

218 Posts

Posted - 2001-11-28 : 13:05:54

quote:

Try that on a 7000-odd row date table

No problem. I did make some small additions to the where clause to limit the rows in the Cross Join (of sorts). You might need to play with the upper end limiter, but sql server does not have any problem with this.

Create Table #TEMP(Calendar_Date Smalldatetime,
Ireland_status char( 3),
England_status char( 3),
Scotland_status char( 3))

SET NOCOUNT ON
DECLARE @Counter Int,
@BaseDate Datetime

SET @BaseDate = DATEADD(DD,-(DATEPART(DD,CONVERT(VARCHAR,GETDATE(),101))-1), CONVERT(VARCHAR,GETDATE(),101))
SELECT @BaseDate

SET @Counter = 0

WHILE @Counter <= 7000
BEGIN
Insert #Temp
Values (DateAdd(dd,@Counter,@BaseDate),
CASE WHEN Datepart (dw, (DateAdd(dd,@Counter,@BaseDate))) = 7 then 'wg'
WHEN Datepart (dw, (DateAdd(dd,@Counter,@BaseDate))) = 1 then 'w'
ELSE 'g'
END,
CASE WHEN Datepart (dw, (DateAdd(dd,@Counter,@BaseDate))) = 7 then 'wg'
WHEN Datepart (dw, (DateAdd(dd,@Counter,@BaseDate))) = 1 then 'w'
ELSE 'g'
END,
CASE WHEN Datepart (dw, (DateAdd(dd,@Counter,@BaseDate))) = 7 then 'wg'
WHEN Datepart (dw, (DateAdd(dd,@Counter,@BaseDate))) = 1 then 'w'
ELSE 'g'
END)

SET @Counter = @Counter+1
END

Declare @StartDate DATETIME,
@Offset INT

Set @StartDate = '20011101'
Set @Offset = 3

SELECT A.Calendar_Date, Count(*)
FROM #TEMP A
JOIN #Temp B ON A.Calendar_Date > B.Calendar_Date
WHERE A.Calendar_date > @StartDate AND
A.Calendar_Date <
DATEADD(DD,CASE WHEN @OFFSET < 10 THEN 20 ELSE @OFFSET * 2 END, @StartDate) AND
B.Calendar_Date >= @StartDate AND
B.Ireland_status = 'g' AND
B.England_status = 'g' AND
B.Scotland_status = 'g' AND
A.Ireland_status = 'g' AND
A.England_status = 'g' AND
A.Scotland_status = 'g'
GROUP BY A.Calendar_Date
Having Count(*)-1 = @Offset

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2001-11-28 : 15:03:11

What I had in mind was: clustered pk on Calendar_date, and an extra indexed int column Workday_number with the number of working days up to that point.

Then, to get the result, it's just a question of looking up the Workday_number (clustered index seek), adding the offset and looking up the smallest date with that Workday_number (index seek, loop join and stream aggregate no more than 5 values). O(log n) w.r.t. the size of the date table, constant for all offset values.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2001-11-29 : 06:15:06

Another angle of attack may be to turn the data structure around.

Region, Date, Status
ie Ireland, 20010101, 0
England, 20010101, 0
Scotland,20010101, 0
Ireland, 20010102, 0
England, 20010102, 1
Scotland,20010102, 1
Ireland, 20010102, 1
England, 20010102, 1
Scotland,20010102, 1

etc....where 0 is a non-working day....does it matter if a non-working day is because it's a bank-holiday or a weekend?

You may encounter problems with your existing solution if you try to do any of the following.

1. what is the 2nd/3rd/....xth next working day...where there are possibly multiple non-working dates between the start date and the offset?
2. same question as 1, except going backwards.....in time.
3. what dates are involved in the desired time-period...ie the start+finish of it...simple enough answer when today/tommorrow, etc, are a working day....but if today is a Sunday, and Friday just gone was a bank-holiday....and IF the object of the exercise is to find all dates since (and not-including) the last working date until (and including) the next working date then it get's more complicated...

And it get's harder if the solution is to cope with offsets forwards + backwards...and also starting dates which can be either a working day or not.

Have a think about what you want from your data....and what you're going to use it for....and if some of the questions above seem to be valid for your situation you may need to go down this route. I've a solution that may be of help to you if you want me to post it....approx. 40 line SP....I'll probably have to add a few comments to explain some sections...but it's fairly straightforward.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources