Monday, June 8, 2009

Calculate Business Day with Calendar table


Yesterday I posted about Business Day. Now let's look into more details on how to do that.
For the first method, which is the most ideal case is you have a calendar table to store all the dates. The calendar table will looks something like

create table calendar
(
dates datetime,
week_day varchar(3)
)

Dates is a datatime field containing the date only. Week_day is the name of the weekday like 'Mon', 'Tue', 'Wed' etc. The week_day is there to simplify and to give better performance to your query. If you don't have the week_day column in there you still can get the week_day using datename(weekday, dates) but this will result in bad performance of the query.

Now let's put some sample data into the calendar table

insert into calendar (dates, week_day)
select '2009-06-04', 'Thu' union all
select '2009-06-05', 'Fri' union all
select '2009-06-06', 'Sat' union all
select '2009-06-07', 'Sun' union all
select '2009-06-08', 'Mon' union all
select '2009-06-09', 'Tue'

Next, let's see how to calculate business day with a calendar table

select count(*) - 1
from calendar
where dates >= '2009-06-04'
and dates <= '2009-06-09'
and week_day not in ('Sat', 'Sun')

And the query will give you 3 as a result.

Now what if, you also want to exclude holiday. Surely holiday does not counted as a business day right ?.

That's easy. Just add a column to indicate the holiday.

create table calendar
(
dates datetime,
week_day varchar(3),
holiday bit
)

And let's include one of the days as holiday

insert into calendar (dates, week_day)
select '2009-06-04', 'Thu', 0 union all
select '2009-06-05', 'Fri', 0 union all
select '2009-06-06', 'Sat', 0 union all
select '2009-06-07', 'Sun', 0 union all
select '2009-06-08', 'Mon', 1 union all
select '2009-06-09', 'Tue', 0

And the new query will be

select count(*) - 1
from calendar
where dates >= '2009-06-04'
and dates <= '2009-06-09'
and week_day not in ('Sat', 'Sun')
and holiday = 0

And the result will be simply 2.

Next I will look at how to business day if you don't have a calendar table

No comments:

Post a Comment