Click here to Skip to main content
15,896,063 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a sales invoice table having field names payment mode, cash amount, card amount and I need to group all my invoices under two groups cash bill/card bill.
if the payment mode is cash then card amount field is 0. if the payment mod is card then cash amount field is 0. if payment mod is both cash and card(situation when a customer pay as cash and remaining balance as card) then both fields contain value.

Issue is when payment mode is both, my invoice should go in both groups.
Please help me

What I have tried:

SELECT case when cash_amount=0 and card_amount!=0 then 'Card Bill'
when card_amount=0 and cash_amount!=0 then 'cash bill'
when cash_amount!=0 and card_amount!=0 then ''
else '' end as BillTyps
Posted
Updated 8-May-18 3:17am
Comments
Maciej Los 8-May-18 8:46am    
What you mean by "when payment mode is both, my invoice should go in both groups"?
Wendelius 8-May-18 11:34am    
Please post some example data to work with

1 solution

I'm not sure i understand you well, but check this:
SQL
DECLARE @invoice TABLE(payment_mode VARCHAR(30), cash_amount DECIMAL(8,2), card_amount DECIMAL(8,2))

INSERT INTO @invoice(payment_mode, cash_amount, card_amount)
VALUES('cash', 500, 0), ('cash', 255, 0),
('card', 0, 1500), ('card', 0, 299),
('cash', 111, 0), ('cash', 222, 0),
('card', 0, 88), ('card', 0, 77),
('cash and card', 200, 300), ('cash and card', 300, 200)

;WITH CTE AS
(
	SELECT *
	FROM @invoice
	WHERE payment_mode <>'cash and card'
	UNION ALL
	SELECT 'cash', cash_amount, 0
	FROM @invoice
	WHERE payment_mode ='cash and card'
	UNION ALL
	SELECT 'card', 0, card_amount 
	FROM @invoice
	WHERE payment_mode ='cash and card'
)
SELECT C.*, CASE WHEN C.payment_mode = 'cash' THEN 'CashBill' ELSE 'CardBill' END AS BillType 
FROM CTE AS C

A result set contains 12 records, because 'cash and card' data have been "splited" into 4 rows (instead of 2).
 
Share this answer
 
Comments
Wendelius 8-May-18 11:34am    
Nice!
Maciej Los 8-May-18 15:36pm    
Thank you, Mika.
Member 13817076 9-May-18 0:10am    
It works. Thank you
Maciej Los 9-May-18 1:55am    
You're very welcome.
Please, accept my answer as a solution - fomrally - to remove your question from unanswered list.
Cheers,
Maciej

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900