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.

Solution for T-SQL challange #10

Hi.
this was the very interesting challange.i enjoy most to solve it.i can't solve it easily and have to get help from friend. This is the solution for T-SQL challange #10.To view challange click here.
on this site jacob post good challnge regarding SQL server problems.you can find more challanges on link provided.
Problem:
(1) perform horizontal sorting on sql table.
(2)Using simple query.
Given Data.

DECLARE @t TABLE
(
c1 CHAR(1),
c2 CHAR(1),
c3 CHAR(1)
)
insert into @t ( c1, c2, c3 )
values ( '2', '1', '3' )
insert into @t ( c1, c2, c3 )
values ( '3', '2', '1' )
insert into @t ( c1, c2, c3 )
values ( 'Z', 'X', 'Y' )
insert into @t ( c1, c2, c3 )
values ( 'B', 'C', 'D' )
insert into @t ( c1, c2, c3 )
values ( 'Y', 'Z', 'X' )
insert into @t ( c1, c2, c3 )
values ( 'B', 'C', 'A' )
SELECT *
FROM @t ;


Solution :


WITH FirstTable
AS ( SELECT *
FROM ( SELECT c1,
c2,
c3,
ROW_NUMBER() OVER ( ORDER BY ( SELECT 0
) ) as 'RN'
FROM @t
) AS t
CROSS JOIN ( SELECT 1 AS part
UNION ALL
SELECT 2
UNION ALL
SELECT 3
) AS n
--Cross join to convert each row value in Diagonal column for further Use
) ,
SecondTable
AS ( SELECT RN,
c1,
c2,
c3,
CASE part
WHEN 1 THEN c1
WHEN 2 THEN c2
WHEN 3 THEN c3
END AS [Values]
FROM FirstTable
--Converts Each Row value as Single Column
) ,
ThirdTable
AS ( SELECT RN AS PK,
c1,
c2,
c3,
[VALUES],
ROW_NUMBER() OVER ( PARTITION BY RN ORDER BY [Values] ) as 'RN'
FROM SecondTable
--perform actual order by operation and set approprite Column number by Row_num function
)
SELECT DISTINCT
[1] AS c1,
[2] AS c2,
[3] AS c3
FROM ThirdTable PIVOT ( MIN([Values]) FOR RN IN ( [1], [2], [3] ) ) AS pv
ORDER BY c1,
c2,
c3 ;


Thanks.

Friday, June 26, 2009

Solution for "TSQL Challenge 8" -Recursive CTE.

HI.
this is my first successful affort to solve T-SQL challange.This challange was published on
http://beyondrelational.com/ .this website is authored by wel known MVP Jacob..
you can vision mpre challanges on this site. this is the solution of Challenge-8 .
Problem:
Retrive data as Hierarchy
Given Data:

DECLARE @Employees TABLE
(
EmpID INT,
EmpName VARCHAR(20),
ReportsTo INT
)
INSERT INTO @Employees
(
EmpID,
EmpName,
ReportsTo
)
SELECT 1,
'Jacob',
NULL
UNION ALL
SELECT 2,
'Rui',
NULL
UNION ALL
SELECT 3,
'Jacobson',
NULL
UNION ALL
SELECT 4,
'Jess',
1
UNION ALL
SELECT 5,
'Steve',
1
UNION ALL
SELECT 6,
'Bob',
1
UNION ALL
SELECT 7,
'Smith',
2
UNION ALL
SELECT 8,
'Bobbey',
2
UNION ALL
SELECT 9,
'Steffi',
3
UNION ALL
SELECT 10,
'Bracha',
3
UNION ALL
SELECT 11,
'John',
5
UNION ALL
SELECT 12,
'Michael',
6
UNION ALL
SELECT 13,
'Paul',
6
UNION ALL
SELECT 14,
'Lana',
7
UNION ALL
SELECT 15,
'Johnson',
7
UNION ALL
SELECT 16,
'Mic',
8
UNION ALL
SELECT 17,
'Stev',
8
UNION ALL
SELECT 18,
'Paulson',
9
UNION ALL
SELECT 19,
'Jessica',
10
DECLARE @manager VARCHAR(20) ;
SELECT @manager = 'smith' ;


Solution:


WITH Managers
as (
--initialization
SELECT EmpID,
EmpName,
CAST(EmpName + '' AS VARCHAR(200)) Hierarchy,
ReportsTo,
1 AS Position,
EmpName AS MainManager,
CAST(EmpID AS VARBINARY(500)) AS sort
FROM @Employees
UNION ALL
SELECT emp.EmpID,
emp.EmpName,
CAST(SPACE(4 * Position) + emp.EmpName AS VARCHAR(200)) Hierarchy,
emp.ReportsTo,
Position + 1,
MainManager,
CAST(sort + CAST(emp.EmpID AS BINARY(4)) AS VARBINARY(500)) AS sort
from @Employees emp
INNER JOIN Managers ON emp.ReportsTo = Managers.EmpID
)
SELECT Hierarchy
FROM Managers
WHERE MainManager = @manager
ORDER BY sort



Thanks.
Haresh Dhameliya