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.

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

Sunday, June 7, 2009

Business Day

What is Business Day ? Wikipedia define it as "A business day is any day including Monday to Friday and does not include holidays".

How many business day is between 2 dates ? like between June 4th 2009 and June 9th 2009.

In SQL Server, the number of days between 2 days can be calculated using datediff() function



SELECT days = DATEDIFF(DAY, '2009-06-04', '2009-06-09')


But this will give you 5 days.


days

-----------

5


The business days between June 4th 2009 and June 9th 2009 should be 3 excluding Saturday and Sunday.


So how to calculate the number of business days between 2 dates ?


I will go though various methods in my coming posts


Area to cover will be