Thursday, July 2, 2009

RC SQL Challange Solution.- Part 2

hi.
you can view challange on http://weblogs.asp.net/ruicarvalho/archive/2009/03/28/rc-sql-challenge-01.aspx This is the another simple solution of this challange. In this solution we have to perform two Cross join and after that solution is very easy.I think this is good one regarding performance.

Solution:

WITH Pricetable
AS ( SELECT FT.ID,
FT.Product,
C.[NAME] FirstCoupen,
FT.SecondName,
FT.PRICE,
Discount_Pirce = ( CASE c.IS_PERCENT
WHEN 0
THEN FT.Discount_Pirce
- c.[VALUE]
ELSE FT.Discount_Pirce
- ( FT.Discount_Pirce
* c.[VALUE] ) / 100
END )
FROM ( SELECT T.ID,
T.[NAME] AS Product,
C.[NAME] AS SecondName,
C.ID AS Coupen1_ID,
T.PRICE,
Discount_Pirce = ( CASE c.IS_PERCENT
WHEN 0
THEN T.Price - c.[VALUE]
ELSE T.PRICE - ( T.PRICE * c.[VALUE] ) / 100
END )
FROM @T t
CROSS Apply @C c
) AS FT
CROSS Apply @C c
WHERE C.ID <> FT.Coupen1_ID
)
SELECT *
FROM ( SELECT *,
ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY Discount_Pirce ) AS RANK
FROM Pricetable
) AS FinalTable
WHERE Rank = 1


Thanks.