DSXchange

Posted: **Tue Nov 13, 2012 10:57 am**

Hi All,

In my Source table has start date and end date and data's are like I mentioned below

Start Date End Date

08-11-2012 13-11-2012

Expected result would be

08-11-2012
09-11-2012
10-11-2012
11-11-2012
12-11-2012
13-11-2012

I have to pivot the start date till the end date. I know this logic would be done through loop concept but I have very limited knowledge on this.

Any help would be appreciated.

Posted: **Tue Nov 13, 2012 11:28 am**

If my source datbase was Oracle 9i or above, I would most certainly use this SQL than do this in datastage transformer loop var

Example here that works on any Oracle DB after 10g -
substitute your tables columns query instead of inner query .

Code: Select all

SELECT ROWNUM, START_DATE+ROWNUM-1, END_DATE FROM
(select  sysdate AS START_DATE,sysdate+7 AS END_DATE from dual )
CONNECT BY ROWNUM <= ABS(END_DATE-START_DATE)+1

Posted: **Tue Nov 13, 2012 11:51 am**

Hi praburaj,

Although I do not have datastage in front of me as of now, you can try the below-

In Stage Variable-

Code: Select all

juliandayfromdate(EndDate)-JulianDayfromdate(StartDate)  SV1

In Loop condition-

Code: Select all

@iteration<=SV1+1

In Loopvariable

Code: Select all

if @iteration=1 then StartDate else 
DateFromJulianDay(JulianDayfromdate(StartDate)+(@iteration-1))       LV1

Output Derivation-

LV1 OutputCol

Posted: **Thu Nov 15, 2012 3:28 am**

Really Thanks! Your solution works as per the expected output.

Once again thanks for your help at the right time. :D

Posted: **Thu Nov 15, 2012 4:22 am**

enjoy... :D

Please mark the topic as resolved.

DSXchange

Pivot Date...

Pivot Date...