How do I add business days to a timestamp?

[lathalr]

Hello everyone,

I want to add 10 business days to one field which is timestamp datatype. I tried in SQL like below

Select add_business_days(timestampfieldname,5) as A from XYZ.

But it is saying invalid identifier. So I want to implement this logic in my job using some stages. I know we have some function in transformer stage. But I want to exclude weekends i.e., Saturdays and Sundays.

Please help me on this.

Thanks in advance.

[rkashyap]

"Business days" is specific to the organization. If you have a data dimension already built then it would probably be accounting for state, national, company specific holidays and other non business days, so use it.

If you only want to exclude weekend (Saturday and Sundays and), then 10 business days is same as 14 calendar days. If the starting day is on a weekend, then add days extra days to find the subsequent Monday.

[kduke]

14 days is not the same as 10 business days. A business day depends on if the business is open. So a holiday does not count as a business day. You have the check every day where it is open.

Let us say you have a date dimension with BIZ_DAY = 'Y' then the SQL is a little tricky. You need count records until you get to 10 where BIZ_DAY = 'Y'.

I would probably join the table to itself where = start day and where >= start day. Then sum(1) where BIZ_DAY = 'Y'.

Not simple.

[rkashyap]

Calculation of 14 business days is certainly not simple.

Leveraging Date Dim is the best way to go. However if you only want to exclude weekend (Saturday and Sundays and), then you can use 14 calendar days in your calculation.

[lathalr]

Thanks all!!!!!!!!

I want to exclude only weekends. Holidays is not mandatory. I tried with 14calendar days which gives 10business days(Monday to Friday) and its working fine. But my requirement, not only 10 business days...some places i have to use 14,17,21 business days. So I want to implement this concept in datastage job itself. Please help me on this.

Thanks in advance

[chulett]

As already noted, the most flexible way is a Date Dimension or something very much akin to one. If you don't have one as part of a Data Warehouse, you could alwaysbuild one. The lookup table, not the whole warehouse.

Failing that you could put together something, a custom routine or BuildOP I suppose, to compute it. There are date functions like WeekdayFromDate and others that could be leveraged in an iterative manner... check where you are before and/or after incrementing. Or I'm sure you can find some C++ code out there you can 'borrow' that does this as you're hardly the first person with a need like this.

[lathalr]

Thanks Chulett

[Teej]

Heh, this came up in a ticket to me a couple months ago, to find out how many business days there were since the start of the year.

I built a simple logic to handle this and gave it to L2 to give to the customer. But for the life of me, I can't seem to find this anymore. Oh well.

In a nutshell, you have to brew your own solution (if you don't care for holidays, then just do a div 5 and multiply by 7 any non-zero values, and mod 5, and determine the day of the week the origin date is, and determine if there's a Saturday/Sunday between the origin date and resulting (mod 5) value.

It is far better to just build a parallel routine instead of trying to wield this code in the normal Transformer logic.

-T.J.

[lathalr]

Thank you Teej

Can you pls explain me with some example...
for eg: my input date : 23-MAY-15.. i need to add 17business days (Monday to friday). Please help me on this and thanks in advance.