SQL Server gives us facility to
fetch or modify records on condition basis. Suppose we have an “Order” table
and we need to fetch records with conditions if user order price is less than
200 Rs it means user is “silver”, if price is greater than 200Rs and less than
500Rs it means user is Gold same like this if price is greater than 500Rs it
means user is “Platinum” .
We can easily solve this problem using SQL Server.
Example:
CREATE TABLE #Order(
ID INT IDENTITY(1,1) PRIMARY KEY,
USR_NAME VARCHAR(100),
SKU VARCHAR(100),
QTY INT,
PRICE DECIMAL(18,2),
CREATED_DATE DATETIME
)
INSERT INTO #Order (USR_NAME,SKU,QTY,PRICE,CREATED_DATE)
SELECT 'User1','9876543210',2,200,GETDATE()
UNION ALL
SELECT 'User2','9876543210',2,300,GETDATE()
UNION ALL
SELECT 'User3','9876543210',2,400,GETDATE()
UNION ALL
SELECT 'User4','9876543210',2,550,GETDATE()
--Case statement
SELECT USR_NAME,PRICE,
(
CASE
WHEN PRICE<=200 THEN 'SILVER'
WHEN PRICE>200 AND PRICE<=500 THEN 'GOLDEN'
WHEN PRICE>500 THEN 'PLATINUM' ELSE 'NULL' END
) AS
USER_TYPE
FROM #Order
DROP TABLE #Order
|
The basic formulations for CASE Expression
Simple Case Expression: Simple case expressions check one
expression against multiple values. Simple case expressions allow only an
equality check.
Example:
DECLARE @value INT=2
SELECT CASE @value WHEN 1 THEN 'ONE'
WHEN 2 THEN 'TWO'
WHEN 3 THEN 'THREE'
ELSE 'NOT MATCH'
END
|
Searched Case
Expression: Searched
Case Expression allows comparison operators. The simple CASE expression checks
only for equivalent values and can’t contain Boolean expressions.
Example:
DECLARE @value
INT=20
SELECT CASE
WHEN @value<20 THEN 'SILVER'
WHEN @value>=20 AND @value<=50 THEN 'GOLDEN'
WHEN @value >50 THEN 'PLATINUM'
END
|
No comments:
Post a Comment
Please do not enter any spam link in the comment box.