How to Perform This sql Query - Bandi

Posted by Jayakumars under Sql Server on 9/17/2015 | Points: 10 | Views : 558 | Status : [Member] [MVP] | Replies : 2
Hi

Bandi

I have attached Excel sheet can you check and let me know . most urgent

Mark as Answer if its helpful to you


Responses

Posted by: Bandi on: 9/19/2015 [Member] [MVP] Platinum | Points: 25

Up
1
Down
Is this you are looking for ?
SELECT Number, CASE WHEN Payment>0 THEN Ledgertype+' Payment' ELSE LedgerType+ ' Receipt' END as Mode, 	Payment,	Receipt FROM TableName


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajnilari2015 on: 9/19/2015 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 25

Up
1
Down
Try the below options

--Create a table variable
DECLARE @T TABLE(Number INT,LedgerType VARCHAR(20),Payment INT,Receipt INT)

--Populate the table variable with some data
INSERT INTO @T VALUES
(1001,'Cash',1000,0),
(1002,'Adjust',0,1000),
(1003,'Cash',0,1500),
(1004,'Adjust',0,7500)

--Display the original record
SELECT *
FROM @t

Solution 1: (Using Case Statement)
SELECT 
t.Number,
CASE WHEN t.PAYMENT > 0 THEN t.LedgerType + ' Payment'
ELSE t.LedgerType + ' Receipt'
END Mode,
t.Payment,
t.Receipt
FROM @t t


Solution 2: (Using IIF Statement - Sql Server 2012 onwards)
SELECT 
t.Number,
IIF ( t.PAYMENT > 0 , t.LedgerType + ' Payment', t.LedgerType + ' Receipt' ) Mode,
t.Payment,
t.Receipt
FROM @t t


Solution 3: (Using CHOOSE Statement - Sql Server 2012 onwards)

SELECT 
t.Number,
CHOOSE( CASE WHEN t.PAYMENT > 0 THEN 1 ELSE 2 END, t.LedgerType + ' Payment', t.LedgerType + ' Receipt') Mode,
t.Payment,
t.Receipt
FROM @t t


In all the 3 ways, the output generated is as under

Number	Mode	Payment	Receipt
1001 Cash Payment 1000 0
1002 Adjust Receipt 0 1000
1003 Cash Receipt 0 1500
1004 Adjust Receipt 0 7500


Hope this helps

--
Thanks & Regards,
RNA Team

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response