Calculating buisness hours between two timestamps

perfman · Post by **perfman** » Sun Sep 23, 2007 8:19 pm

Dear experts,

I need to calculate the buisness hours between two timestamps.

Buisness hours are between 7am and 9pm Mon - Sat (no pub holiday exeptions)

I'm guessing I will need to write a routine for this. What are your suggestions?

ArndW · Post by **ArndW** » Sun Sep 23, 2007 8:38 pm

You posted in the "General" but specified a PX job, so we would need to confirm if this is for a PX/EE job or for a server one.

Although this could be done with stage variables to compute the hour ranges, a routine would be much more efficient (as well as being more legible and maintainable).

perfman · Post by **perfman** » Sun Sep 23, 2007 9:15 pm

Thanks ArndW, First I'm not really sure of what is the difference between server and PX, so that is why I posted in General.

Can you show me what commands I would need to use in the routine?

chulett · Post by **chulett** » Sun Sep 23, 2007 9:20 pm

Not until you end any confusion over your job type.

PX means Parallel Extender and is the reason you buy the "Enterprise" Edition. You used the word "routine" which is typically associated with Server jobs and which would be written in BASIC, whereas custom PX goodies would be written in C or C++.

How about this, do your jobs use Hashed Files?

chulett · Post by **chulett** » Sun Sep 23, 2007 9:22 pm

All of his other posts are PX specific, so I'd go down that path Arnd.

perfman · Post by **perfman** » Sun Sep 23, 2007 10:06 pm

My jobs do not use Hash File, they use Sequential file. So Does that mean I am using a Parallel Extender job? I know that I am using a "Parallel" Job, but I have not seen or heard the phrase "Parallel Extender" before.

Thanks for your help.

ArndW · Post by **ArndW** » Sun Sep 23, 2007 11:18 pm

So you are using the Parallel Extender / Extended Edition (PX/EE) version of the product; this means you should not be using the DS/BASIC language subroutines to do these computations.

You would code your own routine in C++ and call it from your datastage job. The effort to code this is a bit more than can be quickly done for this forum, but perhaps someone already has a similar program that can be posted here and adapted by others.

Have you looked at what is involved in adding your own c++ routine?

perfman · Post by **perfman** » Mon Sep 24, 2007 8:09 pm

I have programming experience in other languages, so I guess I could work it out with a little research.

What would help me in the meantime then would be for someone to explain to me exactly how to implement a custom C++ stage into a parallel job. Maybe show me a simple one that will add 1 to a value or concatenate a string to a piece of text. If I can get that working I should be able to work the rest out.

ray.wurlod · Post by **ray.wurlod** » Mon Sep 24, 2007 9:39 pm

Open Designer.
Open a parallel job.
Select the Stage Types branch.
From the New tool choose new parallel stage then, from the menu presented, choose Build.
Note the names of the tabs. Emplace your code there.
You can use F1 to get help or read the pertinent chapter of the Parallel Job Advanced Developer's Guide or both.

perfman · Post by **perfman** » Mon Sep 24, 2007 10:14 pm

Thanks Ray,

I can see the dialogue.

Do you have a sample C++ code that will take one parameter and do the most simplest of tasks that I can get working in a parallel job?

I understand things much better when I see a working example even if it is very simple.

Thanks.

ray.wurlod · Post by **ray.wurlod** » Tue Sep 25, 2007 12:30 am

There are already some examples in the Parallel forum. Search is your friend. There's a good one by dsguru2b that mimics the functionality of an Ereplace function.

perfman · Post by **perfman** » Tue Sep 25, 2007 8:55 pm

My money is your friend. Haha. Did I say that! Lol

Thanks, I will go and have a look right now.

perfman · Post by **perfman** » Tue Sep 25, 2007 9:22 pm

Thanks Ray!
I got the Ereplace code which I wouldn't have found without your guidance.

So now I have the code, I just want to know, in the "build" stage type dialogue, where exactly do I put this code?

Now I would love to search for this but I am a bit vague on what key words to search on

I tried: "build px stage" and "build parallel stage"
Both returned "No topics or posts met your search criteria"

Kind regards,
perfman

chulett · Post by **chulett** » Tue Sep 25, 2007 9:30 pm

Hmm... I got 523 matches for "build px stage". Hint - don't use an 'Exact Match' (the default) for a phrase like that, try 'All words' instead.

perfman · Post by **perfman** » Tue Sep 25, 2007 9:44 pm

Can you just direct me how to do it? I would really appreciate the help.

Cheers!