Date Logic

[poornima.chandramouli]

Hi,

I've requirement like the following,

I've a key field,if the value of key fileds are same for more than one record then we should check the date1 & date2 of both records,if the date1 value of the second record is one day greater than date2 of first record then both these records are merged to form a single record.Else if the date1 value of the second record more than one day greater than date2 of first record then both these records should be returned as two records,

The input & expected output are as follows,(Date values are given in dd/mm/yyyy format)



INPUT:

KEY Date1 Date2
A 1/1/2008 1/6/2008
A 1/7/2008 31/12/2008
B 1/1/2008 1/6/2008
B 1/7/2008 31/12/2008
C 1/1/2008 1/6/2008
C 1/7/2008 31/12/2008
C 1/2/2009 6/8/2009



OUTPUT:

KEY Date1 Date2
A 1/1/2008 31/12/2008
B 1/1/2008 1/6/2008
B 1/7/2008 31/12/2008
C 1/1/2008 31/12/2008
C 1/2/2009 6/8/2009


How to achieve it datastage.

Thanks in Advance.


[/u][/b]

[girija]

What is the difference between A and B date logic. These are same in input but your expected output contain one row from A and two row from B...

[DSguru2B]

Souce and lookup will be the same. The source remains unchanged but while building the lookup, subtract 1 from Date1. Now your source joining key will be KEY and Date2 and your lookup joining key will be KEY and Date1. If there is a hit grab KEY and Date1 from source and Date2 from the lookup.
This should get you your desired output.

EDIT:I just saw your third example for KEY 'C'. My logic will work if for eacy KEY you have max. 2 values. For values more than two, a different approach is required. Probably loading your values into a temp table, prefereably oracle, and using cycling joins, using connect_by clause. Search for it in google.

[priyadarshikunal]

Please be careful while posting else you might endup with wrong results.

As Date values are given in dd/mm/yyyy format
there is 1 month difference in two records for key value A. How you are merging them. If that is correct then why B has two records in output.

Same can also be done in a transformer using stage Variable (just a thought, you may need to refine it to make it working).

You need to hold two/three complete records in stage variables
DateFromDaysSince(1,PrevDate2) =Date1 and PrevKey=Key
then update the stored Date2 or say PrevDate2 else release the previous record and update the stored record.

Make sure your input is sorted on Key and date and Hash partitioned on key.