calculating total number of business days

[RAJARP]

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

[rohithmuthyala]

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.

[Sreenivasulu]

I think there is no built in function in datastage to find whether a day is a business day hence you need to derive this logic. I found this built-in function in sqlserver. You can call the approprate SQLServer SP (if you have access)

Regards
Sreeni

[chulett]

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'.

[datisaq]

The function is WeekDayFromDate() which will give the day of the week, sunday is the default date -- the value is 0.

[RAJARP]

Thanks for all,
I am having all the saturdays, sundays and public holidays in a .txt file and am planning to do a range lookup(b/w start_date and end_date from i/p file and holidays from lookup file). That would be fine, correct?


Regards,
Raja R P

[rohithmuthyala]

Hi Raja,

As I already suggested the same, it did work out for me.....when I tried this experiment out.

[RAJARP]

Hi Rohit,
I am yet to implement this logic since it is in design phase.Will start codind from today, hopefully. will come back to you, if i face any hurdle on this . Anyways, thanks a ton.

Regards,
Raja R p