Thursday, June 11, 2009

Calculating Business Day without Calendar table

If you don’t have a calendar table, not a problem. You can still able to calculate Business Day easily by making use of a function table F_TABLE_DATE written by MVJ.

Let me show you the code

select    count(*) - 1
from F_TABLE_DATE('2009-06-04', '2009-06-09')
where WEEKDAY_NAME not in ('Sat', 'Sun')
And you will get the same result 3 business days as before.
But that does not exclude any holiday. if you have a table that stores the dates of the holiday, something like




create table holiday
(
holiday_date datetime
)
then you can make used of that and LEFT JOIN to it.




select    count(*) - 1
from F_TABLE_DATE('2009-06-04', '2009-06-09') d
left join holiday h on d.[DATE] = c.holiday_date
where WEEKDAY_NAME not in ('Sat', 'Sun')
and h.holiday_date is null
That’s not too bad isn’t it ?
Next, we will see how to calculate if you don’t have a calendar table and does not want to make use of a Function table like F_TABLE_DATE.

No comments:

Post a Comment