"No one is harder on a talented person than the person themselves" - Linda Wilkinson ; "Trust your guts and don't follow the herd" ; "Validate direction not destination" ;

September 01, 2009

Simple TSQL Exercise

Question #1
Given a Table, Lookup based on Priority and return the value of action based on it

--Priotitylist
1. Match for PriorityA, PriorityB
2. Match for PriorityA, *
3. Match for *, PriorityB
4. Match for *, *

--STEP 1
CREATE TABLE TestPriority
(
PriorityA CHAR(5) NOT NULL,
PriorityB CHAR(5) NOT NULL,
Action BIT NOT NULL
)

--STEP2
INSERT INTO TestPriority (PriorityA, PriorityB, Action)
VALUES ('AA','BB',1)
INSERT INTO TestPriority (PriorityA, PriorityB, Action)
VALUES ('AA','*',0)
INSERT INTO TestPriority (PriorityA, PriorityB, Action)
VALUES ('*','BB',1)
INSERT INTO TestPriority (PriorityA, PriorityB, Action)
VALUES ('*','*',0)

--STEP 3
DECLARE @PriorityA CHAR(5)
DECLARE @PriorityB CHAR(5)
SET @PriorityA = '*'
SET @PriorityB = 'BB'

SELECT Top 1 Action
FROM
(
SELECT ACTION, Priority =
CASE WHEN PriorityA = @PriorityA AND PriorityB = @PriorityB THEN 1
WHEN PriorityA = @PriorityA AND PriorityB = '*' THEN 2
WHEN PriorityA = '*' AND PriorityB = @PriorityB THEN 3
WHEN PriorityA = '*' AND PriorityB = '*' THEN 4
END
FROM TestPriority
) AS TESTResult
WHERE Priority IS NOT NULL
Order by Priority ASC

Question #2
You Have a Customer And Interest Table. Write a Query to Calculate Interest based on below conditions

Condition
1. When a match is found use the interest value
2. When no match found use default value '*'

--STEP 1
CREATE TABLE INTEREST
(
Region VARCHAR(5),
Rate INT
)

--STEP 2
INSERT INTO INTEREST(Region,Rate)
VALUES('CA',5),('US',10),('FR',20)

INSERT INTO INTEREST(Region,Rate)
VALUES('*',25)

--STEP 3
CREATE TABLE Customer
(
Name VARCHAR(20),
Amount INT,
Region VARCHAR(5)
)

--STEP 4
INSERT INTO Customer(Name,Amount,Region)
VALUES('Raj',10000,'CA')

INSERT INTO Customer(Name,Amount,Region)
VALUES('Raja',10200,'SA')

INSERT INTO Customer(Name,Amount,Region)
VALUES('Raa',10200,'FR')

INSERT INTO Customer(Name,Amount,Region)
VALUES('Ram',10200,'IN')

SELECT * FROM Customer

--STEP 5
SELECT C.Name, C.Amount*I.Rate/100, C.Amount, C.Region FROM
INTEREST I JOIN Customer C
ON ((I.Region = C.Region) OR (I.Region = CASE WHEN C.Region NOT IN (SELECT Region FROM INTEREST) THEN '*' END))

No comments: