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
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 |