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)

SQL fiddle

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.