Query to calculate column value based upon another using CASE statement in SQL Server

mahesh213
 
on Jul 11, 2022 07:08 AM
293 Views

HI,

I have one table like below

Id Name Salary

1    a       10

2    b        20

3    c         30

4    d        40

5    e         50

my requirement is that i need to deduct minus 3 from salary values if Name=''b" and Name=''d"

this was what my expected o.p

Id Name Salary

1    a       10

2    b        17

3    c         30

4    d        37

5    e         50

can you please with working script in sql

 select Id,Name,IsNull((Select salary From Employee),0) as Salary
Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jul 11, 2022 07:10 AM

Hi mahesh213,

You need to use CASE statement.

Refer below query.

SQL

DECLARE @Test AS TABLE(Id INT,Name VARCHAR(50),Salary INT)
INSERT INTO @Test VALUES(1,'a',10)
INSERT INTO @Test VALUES(2,'b',20)
INSERT INTO @Test VALUES(3,'c',30)
INSERT INTO @Test VALUES(4,'d',40)
INSERT INTO @Test VALUES(5,'e',50)

SELECT Id, [Name],
	CASE WHEN ([Name] = 'b' OR [Name] = 'd') AND ISNULL(Salary, 0) = 0  THEN 0
	WHEN [Name] = 'b' OR [Name] = 'd' THEN ISNULL(Salary, 0) - 3
	ELSE IsNull(Salary, 0) END AS Salary
FROM @Test

Output

Id Name Salary
1 a 10
2 b 17
3 c 30
4 d 37
5 e 50