Issue
This Content is from Stack Overflow. Question asked by Seb Soum
Im banging my head against the wall. I hope you can help
I have the following columns in my invoice table:
InvoiceNo
InvoiceDate
InvoicePaidDate
JobNumber
each JobNumber value will have 1 or more InvoiceNo
I have a variable (‘JobNo’) that I will receive from an external system
I need to use this variable to check :
- Check each invoice attached to the JobNo variable is paid or not using the InvoicePaidDate and return a string “Invoice 1234 IS PAID” or “Invoice 1232 IS NOT PAID”, and
- Return 1 additional string if all invoices are paid “ALL INVOICES ARE NOW PAID”
Is it possible to do this with a single query?
Im stuck with a rumentary select stement:
Select Case When InvoicePaidDate IS NULL then 'Invoice ' + InvoiceNo + ' Is Not Paid yet' else 'Invoice ' + InvoiceNo + ' Is Paid ' END as PaymentStatus From Invoices
Solution
I think you can try this :
Select Case
When InvoicePaidDate IS NULL then 'Invoice ' + InvoiceNo + ' Is Not Paid yet'
else 'Invoice ' + InvoiceNo + ' Is Paid ' END as PaymentStatus
From Invoices
Where JobNumber = @JobNo
UNION
SELECT 'ALL INVOICES ARE NOW PAID' as PaymentStatus FROM Invoices
WHERE (SELECT COUNT(*) FROM Invoices WHERE JobNumber = @JobNo) =
(SELECT COUNT(*) FROM Invoices Where JobNumber = @JobNo AND InvoicePaidDate IS NOT NULL)
This Question was asked in StackOverflow by Seb Soum and Answered by Md. Suman Kabir It is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.