Identify Missing Dates (Gaps) in Transaction Data

We have a utility which run every month on 4^th business day. It basically extracts the data from the source and getting the information of transaction. Every month end getting the transaction. Few transactions we are not getting. We need to find out that for which month we are not getting the transaction.

To identify missing dates, we generate a complete date range between minimum and maximum transaction dates, left join it with transaction data, and select dates where no matching transaction exists.

For this memo we are creating a table and inserting few records.

CREATE TABLE Transactions (     AssetID int,               TranDate DATE,     EOMVal   decimal );  INSERT INTO Transactions VALUES (1,'2025-01-31', 100), (1,'2025-03-31', 200), (1,'2025-04-30', 150), (1,'2025-07-31', 300), (1,'2025-08-31', 100), (1,'2025-11-30', 200)

See the records in the table.

Now we will create a date series.

--Create Temp tale to store the date  CREATE TABLE #dates   (      dt DATE   ) DECLARE @mindt DATE,         @maxdt DATE; SET @mindt=(SELECT Min(trandate) AS Dt             FROM   transactions); SET @maxdt=(SELECT Max(trandate)             FROM   transactions); WITH dates      AS (SELECT Eomonth(@mindt) AS Dt          UNION ALL          SELECT Eomonth(Dateadd(month, 1, dt))          FROM   dates          WHERE  dt < @maxdt) INSERT INTO #dates             (dt) SELECT dt FROM   dates

See the data into the Temp table.

Now we are using left join to finding the missing month.

See the below query.

select dt.dt from #dates dt left join Transactions tr on tr.TranDate=dt.dt where tr.TranDate is null

This query will return the missing date. See below

Here we are getting the list of date where we did not get the transaction.