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 :

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

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

Do you want a row per JobNumber? Is the all paid tagged onto that column, a different column or different row? — Allan Wind, Sep 19 '22 at 06:39

Md. Suman Kabir · Answer 1 · 2022-09-19T07:24:17.780

1

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

edited Sep 19 '22 at 07:24

answered Sep 19 '22 at 06:41

Md. Suman Kabir

5,243
5
25
43

You can union with an `if` statement? – Magnus Sep 19 '22 at 06:50
@Magnus Thanks for pointing this, I corrected the query. – Md. Suman Kabir Sep 19 '22 at 07:06

score 1 · Accepted Answer · answered Sep 19 '22 at 07:36

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;

score 0 · Answer 3 · answered Sep 19 '22 at 07:26

Select Distinct
    Case
        When count(InvoicePaidDate) over () = count (*) over () then 'All Invoices Paid'
        When InvoicePaidDate Is Null then 'Invoice ' + InvoiceNo + ' Is Not Paid'
        Else 'Invoice ' + InvoiceNo + ' Is Paid'
    End as PaymentStatus
From Invoices
Where JobNo = @JobNo;

Check if all invoices are paid

3 Answers3