Splitting of date range

mukejee · Post by **mukejee** » Wed Oct 21, 2009 12:58 am

Hi,
I want to split the date range based on the given number of days.

For Example.
Input Date Range - Oct 1 and Oct 9
Input Number of days - 3

My Output should be the following 3 date ranges with 3 days in each chunk
Oct 1 and Oct 3
Oct 3 and Oct 6
Oct 6 and Oct 9

Please suggest how can i achieve this with DataStage
Also let me know if i am not clear with the above description

Thanks in Advance
MUS

dr.murthy · Post by **dr.murthy** » Wed Oct 21, 2009 1:09 am

better to use constraints with in the transformer stage.
some thing like date range between oct 3 to oct 6.

mukejee · Post by **mukejee** » Wed Oct 21, 2009 1:13 am

Thanks Murthy for ur reply.
Can you please explain what exactly you meant?
I am not clear.

Thanks!
MUS

dr.murthy · Post by **dr.murthy** » Wed Oct 21, 2009 1:33 am

with in the trasformer just write a constraint with the datefield ,let us suppose your input date range is 3 day s and your inputdate like jan 1 to jan 9then you have to write constraint like

substr(inputdatefield,1,3) = 'jan' and (substr(inputdatefield,3,1) = 1 or substr(inputdatefield,3,1) = 2 or substr(inputdatefield,3,1) = 3)

manully . i hope it should have another mobile to resolve simply.

mukejee · Post by **mukejee** » Wed Oct 21, 2009 1:40 am

Hi Murthy,
I hope you were not clear about my requirement.
I exactly want the date range.
It is like if i am giving an input dates and number of days as

input date1 - 20091001
input date2 - 20091010
No of days - 3

Then it should give 3 date ranges like

20091001 and 20091003
20091003 and 20091006
20091006 and 20091009

Let me know if i am not clear.
I know how to to this using pl/sql.But i want to do it in datastage.

Thanks!
MUS

gssr · Post by **gssr** » Wed Oct 21, 2009 1:56 am

I think you can write a stage variable that add the value of 3 to your date 1 with constraints not exceeding the date2

ray.wurlod · Post by **ray.wurlod** » Wed Oct 21, 2009 3:05 am

Do you really want overlapping ranges, most of which are N+1 days, or not? Did you really mean
20091001 and 20091003
20091004 and 20091006
20091007 and 20091009

mukejee · Post by **mukejee** » Wed Oct 21, 2009 3:11 am

Hi Ray,
I dont want like that.I want like first iteration's enddate should be the start date for the next iteration.

20091001 and 20091003
20091003 and 20091006
20091006 and 20091009

Thanks!
MUS

chulett · Post by **chulett** » Wed Oct 21, 2009 7:38 am

So... the first date range is three days but the others are actually four days?

mukejee · Post by **mukejee** » Wed Oct 21, 2009 7:43 am

Hi,
I am sorry.It should be 3 only.Mistakenly i typed like that.It should be

20091001 and 20091003
20091003 and 20091005
20091005 and 20091007
20091007 and 20091009

Thanks!
MUS

ray.wurlod · Post by **ray.wurlod** » Wed Oct 21, 2009 4:01 pm

See how vitally important accurate specifications are!

Only now could someone work on this implementation without wasting time.

Now, to complete the specification, do you need these in separate rows or in separate columns or in some other format (such as a delimited string)?

mukejee · Post by **mukejee** » Thu Oct 22, 2009 4:52 am

Hi Ray,
Yes i need in seperate rows.

Thanks
MUS

chulett · Post by **chulett** » Thu Oct 22, 2009 6:54 am

'Separate rows' isn't very precise. So from your example it would be 8 rows of a single column, or 4 rows with two columns?

mukejee · Post by **mukejee** » Thu Oct 22, 2009 7:01 am

Hi,
I want it in the below format.

20091001,20091003
20091003,20091005
20091005,20091007
20091007,20091009

ie 4 rows with two columns.
Please excuse me if i am not clear now also

Thanks
MUS

ray.wurlod · Post by **ray.wurlod** » Thu Oct 22, 2009 4:01 pm

The specification is clear. We're now in think time, because nothing immediately comes to mind. It's easy in a server routine, but your question asks how to do it in a DataStage parallel job (which would probably need to execute in sequential mode).