Search This Blog

Thursday, June 25, 2015

Case statement in SQL



CASE is used to provide if-then-else type of logic to SQL.
This SQL case functionality provides the developer the ability to manipulate the presentation of the data without actually updating or changing the data as it exists inside the SQL table.

Solution:

SELECT a.supplierAmount,a.actionto
,case when action='DEBITTo' then a.toAmount else 00 end DEBIT
,case when action='CREDITTO' then a.toAmount else 00 end CREDIT,a.remark

 FROM [dbo].[tablexyz] a   

Input table Date :

supplier
supplierAmount
action
toamount
actionto
HB
150.0000
DebitTo
28.0000
Shimla
HB
200.0000
DebitTo
50.0000
Agra
MIKI
300.0000
CreditTo
150.0000
Agent
Hoojoozat
130.0000
CreditTo
57.0000
Agra
HotelRes
200.0000
CreditTo
100.0000
Agent
HB
40.0000
DebitTo
8.0000
Agent
Hoojoozat
80.0000
DebitTo
20.0000
Agra
GTA
308.0000
DebitTo
4.0000
Shimla
GTA
310.0000
DebitTo
2.0000
Agent

Output table Data:

supplierAmount
actionto
DEBIT
CREDIT
remark
150.0000
Shimla
28.0000
0.0000
booked by shimla
200.0000
Agra
50.0000
0.0000
booked by Agra
300.0000
Agent
0.0000
150.0000
back to agent
130.0000
Agra
0.0000
57.0000
DEBIT TO Agra
200.0000
Agent
0.0000
100.0000
TEST

No comments :