Calculating Business Days - 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 2005 Forums

Transact-SQL (2005)

Calculating Business Days

Author

Topic

Kerry
Starting Member

6 Posts

Posted - 2009-05-21 : 14:24:42

Hi there,

I need to calculate business days between 2 existing dates in a table.
The table is called TABLE1. In that table, there are 2 date fields already exists. Variable: Date1 and Date2. Also, it has dates available already.

I need to calculate the business days excluding holidays, between Date2 and Date1.

I need to eventually create a trigger. But, for now i was wondering if i can create a view and then use that view to create a triggers as i cannot create another column in the table.

i've checked out coding from other websites and it doesn't seem to help me.

Any help is appreciated. Thank you.

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2009-05-21 : 19:38:50

If you're excluding holidays in the "total business days" then you'll first to have decide which days are holidays and put them in a table (probably, but depending on your final solution).
Did you look at this?
http://www.eggheadcafe.com/articles/20030626.asp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-22 : 03:39:00

quote:
Originally posted by Kerry

Hi there,

I need to calculate business days between 2 existing dates in a table.
The table is called TABLE1. In that table, there are 2 date fields already exists. Variable: Date1 and Date2. Also, it has dates available already.

I need to calculate the business days excluding holidays, between Date2 and Date1.

I need to eventually create a trigger. But, for now i was wondering if i can create a view and then use that view to create a triggers as i cannot create another column in the table.

i've checked out coding from other websites and it doesn't seem to help me.

Any help is appreciated. Thank you.

the best way to deal with this is to have a calendar table with all days including your actual holidays as well as other off days info with a bit field to indicate holiday status. then its just a matter of adding days from this table with bit value = 0.

Kerry
Starting Member

6 Posts

Posted - 2009-05-25 : 10:32:02

Thank you.

create or replace FUNCTION TEST1.BUSINESS_DAYS (START_DATE IN DATE, END_DATE IN DATE)
RETURN NUMBER IS DAY_COUNT NUMBER := 0;
CURR_DATE DATE;
BEGIN
CURR_DATE := START_DATE;
WHILE CURR_DATE <= END_DATE
LOOP
IF TO_CHAR(CURR_DATE,'DAY') NOT IN ('SAT','SUN')
THEN DAY_COUNT := DAY_COUNT + 1;
END IF;
CURR_DATE := CURR_DATE + 1;
END LOOP;
RETURN DAY_COUNT;
END BUSINESS_DAYS;

This coding works, only for business days. It's a function. Instead of entering the start date and end date, i wanted to take it from a column in a TEST1 instance, where i have this DAYS table where i have 2 days such as Day1 and Day2. I wanted to subtract it from day2-day1.

So, i put it as (DAYS.day1, DAYS.day2) instead of (START_DATE DATE, END_DATE DATE). it doesn't work. i've tried other methods where i have come to fail.
If anyone can help, please do.

Thank you.
Kerry

Kerry
Starting Member

6 Posts

Posted - 2009-05-25 : 10:53:18

Please ignore my above message. The way i did is correct, but not the calculation shown.

I have to do so that MM/DD/YYY is to be calculated.
Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-25 : 13:57:58

it seems like you're not using sql server. this is ms sql server forum and solution given here will be specific to sql server. so if you're using any other dbms suggest you to post in relevant forums.

Kerry
Starting Member

6 Posts

Posted - 2009-05-26 : 10:27:40

Thank you.
I put in the wrong code. It's a copy from elsewhere. sorry.

I have created a function that will calculate week days only.

create or replace FUNCTION DATA.BUSINESSDAYS (s_date IN TABLE1.DATE%TYPE, e_date IN TABLE2.DATE%TYPE)

which returns a number.

Now, i need to input holidays as well. I have a holiday table called HOLIDAY which has the HOLIDAY_DATES.
I'm thinking i would need to loop around through the table HOLIDAY and compare the value of s_date and also increment while it loops.

shed some light please.

Thank you.

Kerry
Starting Member

6 Posts

Posted - 2009-05-26 : 11:39:29

I've got it guys. Thank you.

Kerry

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources