What you seek is a
statement, which allows you to evaluate values and return one value. This is the SQL analog of a
This is similar to using a
block of code.
I prefer to use the CASE block when I can as I feel it is cleaner looking, but I do have some really ugly queries that actually have an IF block within the ELSE clause
Here is a proof of concept; I kept it as all INT types for ease of typing/reading. Please not the ELSE clause.... if someone did a split-payment it would show up here as neither value would be equal to the Total
DECLARE @Invoices TABLE (BillNo INT, Cash INT, CreditCard INT, GooglePay INT, PhonePe INT, Total INT )
INSERT @Invoices VALUES
(000001, 1000.0, 0.0, 0.0, 0.0, 1000.0)
, (000002, 0.0, 0.0, 0.0, 956.0, 950.0)
, (000003, 0.0, 575.0, 0.0, 0.0, 575.0)
, (000004, 824.0, 0.0, 0.0, 0.0, 824.0)
, (000005, 0.0, 0.0, 455.0, 0.0, 455.0)
, CASE Total
WHEN Cash THEN 'Cash'
WHEN CreditCard THEN 'CreditCard'
WHEN GooglePay THEN 'GooglePay'
WHEN PhonePe THEN 'PhonePe'
END as [PaymentMethod]
CASE (Transact-SQL) - SQL Server | Microsoft Docs