Calculating Working Days

[NotSQL]

Hi Guys,

Im trying to calculating the number of working days in each week/month. I currently got a date dimension with the usual fields however not working days.

I was looking to use the following syntax to pull working days from the table:

Select *, case when Day_Desc in ('Mon','Tue','Wed','Thu','Fri') then 1 else 0 end As WorkingDays
From (Select * from Calendar)t1

Great However, i quickly relised that this gives me my bank holidays to. Can anyone help

 

[hneal98]

Do you have anything in the Calendar table that identifies the bank holidays?

http://www.totalsecurityforyou.com

for any personal security products.

 

[NotSQL]

Unfortunately not...

 

[hmckillop]

The only way you can identify bank holidays is to have a source table containing them.
Bank holidays are not mandatory and differ in countries, e.g. Scotland have hogmany, Northern Ireland have 12th July etc.

SQL doesnt contain every bank/public holiday and as such the only way round this is to have either a "working days" table which excludes weekends and bank holidays, or to have a "bank holidays" table, which you then check through code.

sorry.

"I'm living so far beyond my income that we may almost be said to be living apart

 

[IanWaterman]

YOu could create a table which holds all bank holiday dates.

Join this to calendar with a left outer. And only bring back dates which are null from this new table.

Ian

 

[NotSQL]

Thanks guys, back to the drawing board then. First things first is to create a table containing all bank holdays.

Cheers

 

[gmmastros]

Maybe this will help:

faq183-5075



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom