September 26, 2011 at 8:46 pm
USE tempdb
/********************************************************************************************
* The problem is pretty basic.
* Find the DealA and DealB combination with the highest profit.
* The catch is an ID may only appear once in the DealA and DealB columns.
* I included a query which returns the correct results but does not scale.
*
*
* Failed attempts
* tried using rank, but could only partition on either DealA or DealB. Which only satisfies
* half the requirement because ID's can only exists once in each deal column.
*
* DealADealB Profit
* 3 4 7
* 4 3 7
* 1 2 3
* 2 1 3
*********************************************************************************************/
--=== Build the sample table
if object_id ('TempDb..#tmpDeal') is not null
drop table #tmpDeal
CREATE TABLE #tmpDeal
(ID Int Identity (1,1)
, DealA INT
, DealB INT
, Profit INT
)
INSERT INTO #tmpDeal
( DealA, DealB, Profit )
VALUES
( 1, 2, 3 )
, ( 1, 3, 4 )
, ( 1, 4, 5 )
, ( 2, 1, 3 )
, ( 2, 3, 5 )
, ( 2, 4, 6 )
, ( 3, 1, 4 )
, ( 3, 2, 5 )
, ( 3, 4, 7 )
, ( 4, 1, 5 )
, ( 4, 2, 6 )
, ( 4, 3, 7 )
--=== Build the results table. This table will store the expected results
If Object_ID ('TempDB..#tmpResults') is not null
Drop Table #tmpResults
CREATE TABLE #tmpResults
( DealA INT
, DealB INT
, Profit INT
)
--=== Let's build the correct results
Declare @count int
Set @count = 1
While @count > 0
Begin
Insert Into #tmpResults (DealA, DealB, Profit)
SElECT Top 1 DealA, DealB, Profit
From #tmpDeal
Order by Profit Desc, DealA, DealB
Delete from #tmpDeal
From #tmpDeal a
Inner join #tmpResults b
on a.DealA = b.DealA
or a.DealB = b.DealB
Set @count = @@ROWCOUNT
End
Select * from #tmpResults
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply