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))
September 01, 2009
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment