Hi all,
I want to calculate the total number of business days between two given dates.
my i/p===>
emp_id|startdate|enddate
11| 6/10/2010|8/10/2010
12|8/10/2010|13/10 2010
Required o/p==>
emp_id| timetaken_days
11|2
12|3
(ie) if there is a saturday or sunday between startdate and enddate, those two days should be excluded.
Note:i/p data is a sequential file and also i have the entire list of holidays in a .txt file.
I am new to datastage 8. Your help/suggestions highly appreciated.
Thanks in advance,
Regards,
Raja R P
calculating total number of business days
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 57
- Joined: Wed Oct 21, 2009 4:46 am
- Location: India
1)Try doing a range lookup on the list of holidays like start date < holiday < End date ... aggregate this count based on the key combination of empid,Start Date and End Date by which one can get the number of holidays in that range. Once you come to know the number of records for a particular pair of start date and end date suppose if it is "X".
2)Join the above data with the main data based on keys empid,start date and end date.So now you will have columns empid,start date, end date and number of holidays
3) In transformer, apply the below logic.....
Total days = (EndDt - Start Dt)
Business days = Total days - No. of holidays
I dint try this out but I think it should work.
2)Join the above data with the main data based on keys empid,start date and end date.So now you will have columns empid,start date, end date and number of holidays
3) In transformer, apply the below logic.....
Total days = (EndDt - Start Dt)
Business days = Total days - No. of holidays
I dint try this out but I think it should work.
Rohith
-
- Premium Member
- Posts: 892
- Joined: Thu Oct 16, 2003 5:18 am
There is a function that will give you the day number or name for a date and you could use it to check for Mon-Fri v. Sat-Sun and call those 'business days'. Or you could use an honest-to-goodness date dimension.
I don't have documentation access at the moment, I think it is simply DateToString() with the appropriate mask to get the 'day of the week'.
I don't have documentation access at the moment, I think it is simply DateToString() with the appropriate mask to get the 'day of the week'.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 57
- Joined: Wed Oct 21, 2009 4:46 am
- Location: India