consolidate the date ranges
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 40
- Joined: Mon May 18, 2009 5:22 am
consolidate the date ranges
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........................
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........................
Last edited by n.parameswara.reddy@accen on Mon Aug 10, 2009 7:19 am, edited 6 times in total.
Could you restate your rule in words, I am not certain what you are trying to group by or retrieve from your example.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 40
- Joined: Mon May 18, 2009 5:22 am
sfsdafsd
fasfsdf
Last edited by n.parameswara.reddy@accen on Mon Aug 10, 2009 6:13 am, edited 2 times in total.
-
- Participant
- Posts: 40
- Joined: Mon May 18, 2009 5:22 am
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.
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Re: xzcvxczv
WTF?!n.parameswara.reddy@accen wrote:zxcvxxv
-
- Participant
- Posts: 40
- Joined: Mon May 18, 2009 5:22 am
-
- Participant
- Posts: 40
- Joined: Mon May 18, 2009 5:22 am
chulett wrote: 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
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.
No need for all that revisionist history as now existing replies can make little to no sense.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers