code help

kool_cons · Post by **kool_cons** » Thu Jun 19, 2008 2:20 pm

All,

I need your help on a design,here is the sample data of a file. I need to get the output as shown below. Not sure how to get this result. I could not find out a way to do it datastage. Can you please guide me?

Source
key,Field,Start_dt,End_Dt
1234,ABCD,1/1/2000,1/1/2001
1234,ABCD,1/1/2001,1/1/2002
1234,ABCD,1/1/2002,1/1/2003
1234,DEFT,1/1/2003,1/1/2005
1234,ABCD,1/1/2005,1/1/2007
1234,ABCD,1/1/2007,1/1/2009
1234,DEFT,1/1/2009,12/31/9999

Ouput
1234,ABCD,1/1/2000,1/1/2003
1234,DEFT,1/1/2003,1/1/2005
1234,ABCD,1/1/2005,1/1/2009
1234,DEFT,1/1/2009,12/31/9999

Thanks

mikegohl · Post by **mikegohl** » Thu Jun 19, 2008 2:39 pm

Why is this row in the output twice:
1234,DEFT,1/1/2003,1/1/2005
1234,DEFT,1/1/2009,12/31/9999

I would have expected only one row like this:
1234,DEFT,1/1/2003,12/31/9999

kool_cons · Post by **kool_cons** » Thu Jun 19, 2008 3:00 pm

ABCD is active from 2005 to 2009. user changed his mind and again changed to old value DEFT that will be active from 2009 to 9999. So it can be valid to have this way.

kool_cons · Post by **kool_cons** » Thu Jun 19, 2008 3:01 pm

ABCD is active from 2005 to 2009. user changed his mind and again changed to old value DEFT that will be active from 2009 to 9999. So it can be valid to have this way.

mikegohl · Post by **mikegohl** » Thu Jun 19, 2008 3:08 pm

What if you added a field for every key break:

1,1234,ABCD,1/1/2000,1/1/2001
1,1234,ABCD,1/1/2001,1/1/2002
1,1234,ABCD,1/1/2002,1/1/2003
2,1234,DEFT,1/1/2003,1/1/2005
3,1234,ABCD,1/1/2005,1/1/2007
3,1234,ABCD,1/1/2007,1/1/2009
4,1234,DEFT,1/1/2009,12/31/9999

Then Aggregate and use min and max functions

kool_cons · Post by **kool_cons** » Thu Jun 19, 2008 6:15 pm

thanks for good idea..that should work..i will try tomorrow and let you know..i really appreciate your help..i was doing complex query to get this way.more burden on database side...this new approach will perform better for sure..