consolidate the date ranges

[n.parameswara.reddy@accen]

S.No WSLR_PARTY_ID WSLR_CUST_PARTY_ID BRND_CD PRC_GRP_CD PDCN_ITEM_ID PTR_AMT EFF_START_DT EFF_END_DT
1 Abc Abc123 BUD 411 333 10 20/05/2009 8/19/2009
2 Abc Abc123 BUD 411 333 10 20/08/2009 9/19/2009
3 Abc Abc123 BUD 411 333 11 20/09/2009 10/19/2009
4 Abc Abc123 BUD 411 333 10 20/10/2009 11/19/2009
5 Abc Abc123 BUD 411 333 10 20/11/2009 12/31/3999



On this input dataset we perform operations so as to consolidate the date ranges for the same PTR_AMT while maintaining all the pricing changes in a chronological order.
The result . Note that changes in start and end dates and how the data has been consolidated.
S.No WSLR_PARTY_ID WSLR_CUST_PARTY_ID BRND_CD PRC_GRP_CD PDCN_ITEM_ID PTR_AMT EFF_START_DT EFF_END_DT
1 Abc Abc123 BUD 411 333 10 20/05/2009 9/19/2009
2 Abc Abc123 BUD 411 333 11 20/09/2009 10/19/2009
3 Abc Abc123 BUD 411 333 10 20/10/2009 12/31/3999
This is my requirement please help me on this........................

[ArndW]

Could you restate your rule in words, I am not certain what you are trying to group by or retrieve from your example.

[miwinter]

I can't decipher that 'requirement' at all - it is very unclear.

[n.parameswara.reddy@accen]

fasfsdf

[miwinter]

Sort records in the correct order (amount) - you may also need to aggregate these - and employ stage variables to manage your 'date minus one' requirement.

[n.parameswara.reddy@accen]

Sort records in the correct order (amount) - you may also need to aggregate these - and employ stage variables to manage your 'date minus one' requirement.

no, this amount should be in the same order

[miwinter]

What should be in the same order - the output? If that's the case, sort that again for your requirement.

[ShaneMuir]

If I understand your requirement, then you will have to do this sequentially. If you have extra key fields that you are not sharing then you would partition by them.
You will have to sort by the start date ascending. You will need several variables which hold the start and end dates of the current incoming record and the previous row. The first new row for a given key you would hold your start date in a variable and the end date in a variable. Then for the next row you would compare the current start date to the previous end date.
If there is an overlap you would set the end date variable to be the current end date, and leave the start date as per the value in the variable.
If there is no overlap then you would reset the start and end dates in the variable with those in the current record.

You will probably have to dedup the records after the fact based on the key and start date and select the last record.

[chulett]

Give me a f'ing break. That kind of behaviour - going back and replacing your posts with random crap - falls squarely into the 'childish' camp and pretty much guarantees you a spot on the 'People We Tend To Ignore' list as who knows when you'll pull that crap again.

[ShaneMuir]

zxcvxxv

WTF?!

[miwinter]

A-men... x2...

Next time I'll pass on replying I think

[n.parameswara.reddy@accen]

A-men... x2...

Next time I'll pass on replying I think

hay just i am replacing the requirement

[n.parameswara.reddy@accen]

Give me a f'ing break. That kind of behaviour - going back and replacing your posts with random crap - falls squarely into the 'childish' camp and pretty much guarantees you a spot on the 'People We Tend To Ignore' list as who knows when you'll pull that crap again.

hay! just i am replacing the requirement

[chulett]

Alright, fine. Next time do that without the intermediate shenanigans, please and rather than replace everything posted so far simply add a new post that clarifies your requirement.

No need for all that revisionist history as now existing replies can make little to no sense.

[ShaneMuir]

In that case - refer back to my previous post as it should give you enough direction to get you on your way.