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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy