September 26, 2011 at 9:58 pm
Can you please expand further on how you arrived at your desired result ? i am kinda stuck in understanding the requirement. Should we find the highest profit for Deal A or only Deal B or how ?
September 26, 2011 at 10:09 pm
Basically the table #tmpDeal represents a cartesian product of IDs. The profit is determined using some formula. What i need to do is identify the ideal combination of DealA and DealB by Profit (desc). The challenge is a # can only appear in DealA and DealB once. The query i provided is similar to the cursor used to run the process today and yields the correct results.
Process:
1. Select Top 1 DealA, DealB, Profit from #tmpDeal ordered by Profit desc, DealA, DealB
2. Do something with this record.
3a. Delete all records in #tmpDeal where DealA = DealA from Step 1
3b. Delete all records in #tmpDeal where DealB = DealB from Step 1
4. Repeat the cycle.
Results:
If you noticed #tmpDeal starts with 12 records. After running the above process we wind up with 4 rows in #tmpResults. The values in DealA and DealB are unique.
September 27, 2011 at 4:39 am
Sorry, still we are not getting what is the requirement?
regards
Palash Gorai
September 27, 2011 at 7:23 am
Sorry guys. I thought included all the information needed in my original post. It has the DDL and poorly performing query included. Let me try to explain the problem a different way.
DealA and DealB represents different profit centers. The table #tmpDeal contains a cartesian product of all possible combinations of profit centers (DealA and DealB). The goal is to identify the most profitable combinations. The catch is a profit center can exists in both DealA and DealB but may only be listed once in both.
Desired Results below:
DealA DealB Profit
1 2 10
2 6 10
3 1 7
4 8 6
Results Provided by using Rank with partion on DealA. Note: The DealA column contains unique values, but DealB has repeated values which is incorrect.
DealA DealB Profit
1 2 10
2 3 10
3 3 8
4 1 7
September 27, 2011 at 8:32 am
HI I think i understand your so-called formula
select ID,DealA,DealB,Profit from
(
select *,(select COUNT(1) from (select row_number() over(order by Profit Desc, DealA, DealB)as rank,* from
#tmpDeal)c where a.DealA=c.DealA and c.rank<a.rank) as COUNTDealA,
(select COUNT(1) from (select row_number() over(order by Profit Desc, DealA, DealB)as rank,* from
#tmpDeal)c where a.DealB=c.DealB and c.rank<a.rank) as COUNTDealB
from
(
select row_number() over(order by Profit Desc, DealA, DealB)as rank,* from
#tmpDeal
) a
) u where (u.COUNTDealA=0 and u.COUNTDealB=0) or (u.COUNTDealA>=2 and u.COUNTDealB>=2)
Viewing 5 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply