Tuesday, June 30, 2009

RC SQL Challange Solution.

hi.
i Found one more challange on a http://weblogs.asp.net/ruicarvalho/archive/2009/03/28/rc-sql-challenge-01.aspx.this challange was about Callulationg Minimum price after applying any Two of four coupens.we can solve this using CTE.

Problem:

(1) Product table(Given).
(2) Coupen Table(Give).
(3)Find Minimum Discount table by applying any two coupen.

Given Data:

DECLARE @T TABLE
(
ID INT IDENTITY,
NAME NVARCHAR(20),
PRICE MONEY
)
INSERT INTO @T ( NAME, PRICE )
VALUES ( 'PRODUCT 1', 100 )
INSERT INTO @T ( NAME, PRICE )
VALUES ( 'PRODUCT 2', 220 )
INSERT INTO @T ( NAME, PRICE )
VALUES ( 'PRODUCT 3', 70 )
DECLARE @C TABLE
(
ID INT IDENTITY,
NAME NVARCHAR(20),
VALUE INT,
IS_PERCENT BIT
)
INSERT INTO @C ( NAME, VALUE, IS_PERCENT )
VALUES (
'COUPON 1 : -15$',
15,
0
)
INSERT INTO @C ( NAME, VALUE, IS_PERCENT )
VALUES ( 'COUPON 2 : -5$', 5, 0 )
INSERT INTO @C ( NAME, VALUE, IS_PERCENT )
VALUES (
'COUPON 3 : -10%',
10,
1
)
INSERT INTO @C ( NAME, VALUE, IS_PERCENT )
VALUES (
'COUPON 4 : -12$',
12,
0
) ;


Solution:

DECLARE @T TABLE
(
ID INT IDENTITY,
NAME NVARCHAR(20),
PRICE MONEY
)
INSERT INTO @T ( NAME, PRICE )
VALUES ( 'PRODUCT 1', 100 )
INSERT INTO @T ( NAME, PRICE )
VALUES ( 'PRODUCT 2', 220 )
INSERT INTO @T ( NAME, PRICE )
VALUES ( 'PRODUCT 3', 70 )
DECLARE @C TABLE
(
ID INT IDENTITY,
NAME NVARCHAR(20),
VALUE INT,
IS_PERCENT BIT
)
INSERT INTO @C ( NAME, VALUE, IS_PERCENT )
VALUES (
'COUPON 1 : -15$',
15,
0
)
INSERT INTO @C ( NAME, VALUE, IS_PERCENT )
VALUES ( 'COUPON 2 : -5$', 5, 0 )
INSERT INTO @C ( NAME, VALUE, IS_PERCENT )
VALUES (
'COUPON 3 : -10%',
10,
1
)
INSERT INTO @C ( NAME, VALUE, IS_PERCENT )
VALUES (
'COUPON 4 : -12$',
12,
0
) ;
WITH FirstTable
AS ( SELECT ID,
[Name],
Price,
Discount_price = PRICE,
Cupen_count = 0,
0 Cupen_Id,
CAST('' AS VARCHAR(100)) Cupen_Name
FROM @T
UNION ALL
SELECT FT.ID,
FT.[Name],
FT.Price,
Discount_price = ( CASE c.IS_PERCENT
WHEN 1
THEN ( 100 - C.value )
* ( Discount_price / 100 )
ELSE Discount_price - C.[VALUE]
END ),
Cupen_count = Cupen_count + 1,
Cupen_Id = C.id,
Cupen_Name = CAST(Cupen_Name
+ CAST(c.ID AS VARCHAR(2)) + ' & ' AS VARCHAR(100))
FROM FirstTable FT,
@C c
WHERE Cupen_Id <> c.id AND Cupen_count<2)SELECT ID,[Name],Price,Discount_price,Cupen_NameFROM ( SELECT ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY Discount_price asc ) AS RN,*FROM FirstTable) AS T WHERE T.RN=1

Thanks.