Im banging my head against the wall. I hope you can help

I have the following columns in my invoice table:

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

CodePudding user response：

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

CodePudding user response：

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;

CodePudding user response：

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;