Agree the grain and acceptance rule

The key is SiteCode + TradingDate. A return counts only when its status is Accepted, its amount is not NULL and it was received by the chosen as-of time. Zero is allowed. An accepted state here is assumed to be final; systems with later revocations need status-history logic.

Store the business trading date separately from the UTC receipt time. In production, derive UTC deadlines from the approved local schedule, accounting for UK clock changes. Do not assume UK local time always equals UTC.

Run a reproducible comparison

Three fictional sites owe returns for two trading dates. Leicester has an accepted zero for the second day. Nottingham has a rejected submission for that day, which must not count as received.

-- Illustrative example. All timestamps below are UTC.
DECLARE @AsOfUtc datetime2(0) = '2026-09-23T08:30:00';

DECLARE @Expected TABLE (
    SiteCode varchar(3) NOT NULL,
    TradingDate date NOT NULL,
    DueAtUtc datetime2(0) NOT NULL,
    PRIMARY KEY (SiteCode, TradingDate)
);

DECLARE @Returns TABLE (
    SubmissionId int PRIMARY KEY,
    SiteCode varchar(3) NOT NULL,
    TradingDate date NOT NULL,
    ReceivedAtUtc datetime2(0) NOT NULL,
    ReturnStatus varchar(12) NOT NULL,
    SalesAmount decimal(12,2) NULL
);

INSERT INTO @Expected VALUES
('LEI','20260921','2026-09-22T08:00:00'),
('NOT','20260921','2026-09-22T08:00:00'),
('DER','20260921','2026-09-22T08:00:00'),
('LEI','20260922','2026-09-23T08:00:00'),
('NOT','20260922','2026-09-23T08:00:00'),
('DER','20260922','2026-09-23T08:00:00');

INSERT INTO @Returns VALUES
(1,'NOT','20260921','2026-09-22T07:00:00','Accepted',2100.00),
(2,'DER','20260921','2026-09-22T07:10:00','Accepted',1800.00),
(3,'LEI','20260922','2026-09-23T07:00:00','Accepted',0.00),
(4,'DER','20260922','2026-09-23T07:15:00','Accepted',1900.00),
(5,'NOT','20260922','2026-09-23T07:20:00','Rejected',NULL);

;WITH Due AS (
    SELECT SiteCode, TradingDate, DueAtUtc
    FROM @Expected
    WHERE DueAtUtc <= @AsOfUtc
), Checked AS (
    SELECT d.*,
           CASE WHEN EXISTS (
               SELECT 1
               FROM @Returns AS r
               WHERE r.SiteCode = d.SiteCode
                 AND r.TradingDate = d.TradingDate
                 AND r.ReturnStatus = 'Accepted'
                 AND r.SalesAmount IS NOT NULL
                 AND r.ReceivedAtUtc <= @AsOfUtc
           ) THEN 1 ELSE 0 END AS IsReceived
    FROM Due AS d
)
SELECT SiteCode, TradingDate, DueAtUtc,
       CASE WHEN IsReceived = 1
            THEN 'Received' ELSE 'Missing' END AS CheckStatus,
       COUNT(*) OVER () AS ExpectedCount,
       SUM(IsReceived) OVER () AS ReceivedCount,
       SUM(1 - IsReceived) OVER () AS MissingCount,
       CAST(100.0 * SUM(IsReceived) OVER ()
            / NULLIF(COUNT(*) OVER (), 0) AS decimal(5,1))
            AS CompletenessPct
FROM Checked
ORDER BY TradingDate, SiteCode;

EXISTS asks whether a qualifying row is present. Multiple qualifying submissions do not multiply the expected row. The window totals repeat the same summary alongside each detail row—do not sum those repeated totals in a downstream report. Microsoft: EXISTS; Microsoft: SUM and OVER.

Read the result without inventing sales

The query returns six detail rows. LEI on 21 September and NOT on 22 September are Missing; the other four are Received. Each row shows ExpectedCount 6, ReceivedCount 4, MissingCount 2 and CompletenessPct 66.7. These are illustrative results, not customer evidence.

If no obligations are due, this detail query returns no rows. The consuming report must explicitly show “No submissions due”, not 100% completeness or “all clear”. If a query fails, show “Check failed” separately from “nothing missing”.

To produce only the exception queue, replace the final SELECT with the following, retaining the declarations and CTEs above. It must immediately follow the same CTE block:

SELECT SiteCode, TradingDate, DueAtUtc
FROM Checked
WHERE IsReceived = 0
ORDER BY DueAtUtc, SiteCode;

Connect the rule to the real workflow

Replace the table variables with an approved schedule table and a validated submission log. Keep a unique expected key; do not generate obligations from the sales table, because absent sites would disappear from both sides of the comparison.

Use a bounded trading-date range. Ask your database owner to review the execution plan and indexes around site, trading date, receipt time and acceptance status. The small table variables are teaching fixtures, not a recommendation for large operational datasets.

Run the check after the agreed deadline and grace period. Record the run time, rule version, expected count, received count and exception keys. Route exceptions to a named branch or integration owner, and close them only after accepted evidence arrives or an authorised schedule correction is recorded.