getting data for the last three months

uzl · Post by **uzl** » Tue Jun 18, 2013 3:30 am

I have the following requirement. I need to extract data for the last three months. So today (18-06-2013) I have to extract data from 01-03-2013 to 31-05-2013.
I managed to get the enddate : OConv((MONTH.FIRST(MONTH.TAG(DATE()))) ,'D-YMD[4,2,2]') :' ':Oconv(Time(),'MTS.') gives me "2013-06-01 11:13:17.000". My first question is how to transform this to "2013-06-01 00:00:00.000".
My second question is : how do I go three months back ?

ArndW · Post by **ArndW** » Tue Jun 18, 2013 4:54 am

OConv((MONTH.FIRST(MONTH.TAG(DATE()))) ,'D-YMD[4,2,2]') :' 00:00:00'

uzl · Post by **uzl** » Tue Jun 18, 2013 5:18 am

This solves my first question. I'm a bit embarressed it was so simple

. Now my second question?

ArndW · Post by **ArndW** » Tue Jun 18, 2013 6:14 am

How do you define '3 months back'?

does it mean subtract 90 days? or the first day of the month when you subtract 3 from the current month? Or some other rule?

uzl · Post by **uzl** » Tue Jun 18, 2013 6:36 am

substract 3 from the current month.

ArndW · Post by **ArndW** » Tue Jun 18, 2013 6:51 am

One last question - you are using OCONV() so I would assume a server job, but the question is posted in the parallel forum. The answer depends upon which you are really using.

uzl · Post by **uzl** » Tue Jun 18, 2013 7:26 am

It's a server job. We were told only to use serverjobs. We started working with datastage with version 8. I have to admit that I don't understand the distinction between the two fora.

chulett · Post by **chulett** » Tue Jun 18, 2013 7:42 am

You posted in the EE forum which is dedicated to PX / Parallel jobs and you marked your Job Type as Parallel as well, hence Arnd's question. I'm going to fix both issues. Hang on...

Here we are, correct forum and job type.

ray.wurlod · Post by **ray.wurlod** » Tue Jun 18, 2013 4:07 pm

I suspect the question relates to a sequence governing parallel jobs.

You might like also to investigate using DataStage routines such as the AddMonths routine available here.

bhasds · Post by **bhasds** » Wed Jun 19, 2013 12:14 am

Hi uzl,

You may use the following to get the 3 month back date-

Code: Select all

'01':"-":(if  DSJobStartTimestamp[6,2] - 3 <10 Then 0:DSJobStartTimestamp[6,2] - 3 Else DSJobStartTimestamp[6,2] - 3):"-":DSJobStartTimestamp[1,4]:'00:00:00'

uzl · Post by **uzl** » Wed Jun 19, 2013 7:23 am

Thanks bhasds. We use different date-formats but your example has set me in the right direction.

Code: Select all

prepBeginMaand_uv.UV_beginMaand : if DSJobStartTimestamp[6,2]-3 <= 0 then (DSJobStartTimestamp[6,2] + 9) else ('0' : DSJobStartTimestamp[6,2]-3)

prepBeginMaand_uv.UV_beginJaar : if DSJobStartTimestamp[6,2]-3 <= 0 then (DSJobStartTimestamp[1,4] - 1) else DSJobStartTimestamp[1,4]

dateBegin_UV : prepBeginMaand_uv.UV_beginJaar : '-' : prepBeginMaand_uv.UV_beginMaand : '-01 00:00:00.000'

=> the derivation of the year works only for years > 2000

ray.wurlod · Post by **ray.wurlod** » Wed Jun 19, 2013 5:07 pm

The AddMonths routine works for any date.

uzl · Post by **uzl** » Fri Jun 21, 2013 7:20 am

We don't have experience with added routines. How does this work with upgrades ?

ray.wurlod · Post by **ray.wurlod** » Fri Jun 21, 2013 3:37 pm

Perfectly. These routines have been in use since version 3.

DSXchange