SQL – Check if all invoices are paid

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 :

  1. 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
  2. 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

This question is not yet answered, be the first one who answer using the comment. Later the confirmed answer will be published as the solution.

This Question and Answer are collected from stackoverflow and tested by JTuto community, is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.

people found this article helpful. What about you?