Could you have possible meant to use the
TotalDue instead of Balance? This would change your statement to look like this:
SELECT [InvoiceNo], [Date], [CustmID], [TotalDue]
, [Status] = CASE
WHEN [TotalDue] >0 THEN 'Pending'
WHEN [TotalDue] =0 THEN 'Paid'
END
FROM [dbo].[Sale_Bill_details]
However; I have a problem with this, as it does not compensate for someone who overpaid and has a negative balance. You should be using an ELSE statement OR alternatively change the second line:
SELECT [InvoiceNo], [Date], [CustmID], [TotalDue]
, [Status] = CASE
WHEN [TotalDue] > 0 THEN 'Pending'
WHEN [TotalDue] < 0 THEN 'Paid'
ELSE 'Credit Due'
END
FROM [dbo].[Sale_Bill_details]
SELECT [InvoiceNo], [Date], [CustmID], [TotalDue]
, [Status] = CASE
WHEN [TotalDue] > 0 THEN 'Pending'
WHEN [TotalDue] <=0 THEN 'Paid'
END
FROM [dbo].[Sale_Bill_details]
But I still have problems with this- as [Status] ("Pending", "Paid", "Credit Due") is a presentation item; and generally should neither be saved nor calculated within the database.
Separation of Concerns to me says that the DB is used to display the data. The application and it's friend end return the data, and choose what to display from it.
Addendum After reviewing the code due to an error pointed out to me, I found two errors in your CASE statement1. If you define the
condition in the opening statement, it cannot be used in the WHEN statement
2. TSQL uses a single equals sign for equality and assignment, your second WHEN uses ==
3. There is no END to the CASE statement.
, case Balance when Balance >0 then 'Pending' when Balance == 0 then 'Paid'
, case when Balance >0 then 'Pending' when Balance = 0 then 'Paid' END
Proof of Concept along, incorporating my recommended CASE statement along with the original one (will need to be commented out) and the simple fix to the bad statement
DECLARE @Sample TABLE (InvoiceNo INT NULL, [Date] DATE NULL, Custom ID INT NULL, TotalDue MONEY NULL, Balance MONEY NULL)
INSERT @Sample
VALUES (1, GetDate(), 1, 123, 123)
, (2, GetDate(), 2, 123, 0)
, (3, GetDate(), 3, 123, -123)
, (4, GetDate(), 4, 0, null )
SELECT [InvoiceNo]
,[Date]
,[CustomID]
,[TotalDue]
, [Status] = case
when Balance > 0 then 'Pending'
when Balance = 0 then 'Paid'
when Balance < 0 then 'Credit Due'
else 'na'
END
, case Balance when Balance >0 then 'Pending' when Balance == 0 then 'Paid'
, case when Balance >0 then 'Pending' when Balance = 0 then 'Paid' END
FROM @Sample
Running this (with the bad line commented out) will return something like this:
InvoiceNo Date CustomID TotalDue Status (No column name)
1 2018-11-02 1 123.00 Pending Pending
2 2018-11-02 2 123.00 Paid Paid
3 2018-11-02 3 123.00 Credit Due NULL
4 2018-11-02 4 0.00 na NULL