Check if all invoices are paid

Question

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 statement:

Select Case
    When InvoicePaidDate IS NULL then 'Invoice ' + InvoiceNo + ' Is Not Paid yet'
    else  'Invoice ' + InvoiceNo + ' Is Paid ' END as PaymentStatus
From Invoices

Answer 1

you could try this

DROP TABLE invoices1;

CREATE TABLE invoices1 (
  InvoiceNumber varchar(10),
  JobNumber varchar(10),
  InvoiceDate date NULL,
  InvoicePaidDate date NULL
);


INSERT INTO invoices1
  VALUES ('1', '1', '2022-05-01', NULL),
  ('2', '1', '2022-05-02', NULL),
  ('3', '2', '2022-05-02', '2022-05-03'),
  ('4', '2', '2022-05-03', '2022-05-04');

DECLARE @jobNo int = 1;
SELECT DISTINCT
  CASE
    WHEN EXISTS (SELECT
        1
      FROM dbo.invoices1
      WHERE JobNumber = @jobNo
      AND InvoicePaidDate IS NOT NULL) THEN 'ALL INVOICES ARE NOW PAID'
    WHEN InvoicePaidDate IS NULL THEN 'Invoice ' + InvoiceNumber + ' Is Not Paid yet'
    ELSE 'Invoice ' + InvoiceNumber + ' Is Paid '
  END AS PaymentStatus
FROM Invoices1
WHERE JobNumber = @jobNo;