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.

No comments:

Post a Comment

Comments posted on ASP.Net Ajax Tutorials Blog are moderated and will be approved only if they are on-topic and not abusive. Please email me or my team for tech-support or blogging related questions. Avoid including website URLs in your comments - Thanks Author