SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Calculating Business Days
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kerry
Starting Member

6 Posts

Posted - 05/21/2009 :  14:24:42  Show Profile  Reply with Quote
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

USA
366 Posts

Posted - 05/21/2009 :  19:38:50  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/22/2009 :  03:39:00  Show Profile  Reply with Quote
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.
Go to Top of Page

Kerry
Starting Member

6 Posts

Posted - 05/25/2009 :  10:32:02  Show Profile  Reply with Quote
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

Go to Top of Page

Kerry
Starting Member

6 Posts

Posted - 05/25/2009 :  10:53:18  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/25/2009 :  13:57:58  Show Profile  Reply with Quote
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.
Go to Top of Page

Kerry
Starting Member

6 Posts

Posted - 05/26/2009 :  10:27:40  Show Profile  Reply with Quote
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.


Go to Top of Page

Kerry
Starting Member

6 Posts

Posted - 05/26/2009 :  11:39:29  Show Profile  Reply with Quote
I've got it guys. Thank you.

Kerry
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.3 seconds. Powered By: Snitz Forums 2000