“I want you to identify all of the data missing data in my database table”

Serve Data that Isn’t There

fetch Missing data

In this scenario, the client had a database with all received transactions for a given time period. Transactions for a given till could  start and end at any number; these were variables per till.

Transactions were also only unique per till so there would be large overlaps of transactionNumbers — but each transactionNumber would be unique for a given store/till combination.

Furthermore, transactionNumbers were sequential. In other words, if you picked a given till and selected the smallest & largest transactionNumbers for that till, you should also expect to see a record for every integer value between those two numbers.

This is where things went awry. Not every expected transaction would be received and the client needed a quick and easy way to retrieve the missing transactions for every till in every store.

In other words, the client wanted us to identify the data that was NOT present in the database.   

This is not a Mule problem per se. It is a database problem. However, my colleague who was tasked with this solution is a true Mule Whisperer.

He faced the challenge head-on — he whispered — and found a SQL script that worked.

You can try the SQL yourself. Open sqlfiddle.com  and paste the code below into the main window. Then click Execute and see how a script serves all of the expected data that isn’t in the database.

Pretty neat!

-- INIT database
CREATE TABLE ReceivedTransactions (
Store char(4),
Till INT,
TransactionNumber INT,
CreatedDate Datetime
);

INSERT INTO ReceivedTransactions(Store,Till,TransactionNumber,CreatedDate) VALUES ('1230',1,1,GETDATE());
INSERT INTO ReceivedTransactions(Store,Till,TransactionNumber,CreatedDate) VALUES ('1230',1,9,GETDATE());
INSERT INTO ReceivedTransactions(Store,Till,TransactionNumber,CreatedDate) VALUES ('1230',1,6,GETDATE());
INSERT INTO ReceivedTransactions(Store,Till,TransactionNumber,CreatedDate) VALUES ('1231',1,7,GETDATE());
INSERT INTO ReceivedTransactions(Store,Till,TransactionNumber,CreatedDate) VALUES ('1231',1,10,GETDATE());
INSERT INTO ReceivedTransactions(Store,Till,TransactionNumber,CreatedDate) VALUES ('1231',1,15,GETDATE());
INSERT INTO ReceivedTransactions(Store,Till,TransactionNumber,CreatedDate) VALUES ('1231',2,1,GETDATE());
INSERT INTO ReceivedTransactions(Store,Till,TransactionNumber,CreatedDate) VALUES ('1231',2,8,GETDATE());
INSERT INTO ReceivedTransactions(Store,Till,TransactionNumber,CreatedDate) VALUES ('1231',2,9,GETDATE());
INSERT INTO ReceivedTransactions(Store,Till,TransactionNumber,CreatedDate) VALUES ('1231',2,10,GETDATE());
INSERT INTO ReceivedTransactions(Store,Till,TransactionNumber,CreatedDate) VALUES ('1231',2,16,GETDATE());

-- QUERY database
-- Step 1: Get min and max transaction numbers per Store and Till
WITH TransactionRanges AS (
SELECT Store, Till, MIN(TransactionNumber) AS MinTransaction, MAX(TransactionNumber) AS MaxTransaction
FROM ReceivedTransactions
GROUP BY Store, Till
),

-- Step 2: Generate all transaction numbers in the range using recursion
AllTransactions AS (
SELECT Store, Till, MinTransaction AS TransactionNumber, MaxTransaction
FROM TransactionRanges
UNION ALL
SELECT Store, Till, TransactionNumber + 1, MaxTransaction
FROM AllTransactions
WHERE TransactionNumber < MaxTransaction
),

-- Step 3: Filter out the ones that exist in the actual data
MissingTransactions AS (
SELECT AT.Store, AT.Till, AT.TransactionNumber
FROM AllTransactions AT
LEFT JOIN ReceivedTransactions FRT
ON AT.Store = FRT.Store AND AT.Till = FRT.Till AND AT.TransactionNumber = FRT.TransactionNumber
WHERE FRT.TransactionNumber IS NULL
)

-- Final result
SELECT Store, Till, TransactionNumber
FROM MissingTransactions
ORDER BY Store, Till, TransactionNumber
OPTION (MAXRECURSION 0); -- Allows recursion beyond 100 levels