UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> Find Missing Dates or Numbers within a Range    
Find Missing Dates or Numbers within a Range

Image:NotifCleanup.gif This page requires general cleanup in formatting or text to better fit the UA Wiki Guidelines

I have also attached a sample application with the code and it can be found here: Media:Find Missing Dates or Numbers within a Range.zip

It shows how to use SQL to identifying missing dates or numbers within a range.


Identifying Missing Numbers The table structure: Table Name : Tbl_Numbers Field name : InvoiceNo

So if your data is like this.

100
101
106
107
109
115

The SQL will return this Missing Data:

102-105
108
110-114

Number Example Code:

To use the code all you need to adjust is the inner query in the FROM Clause with the subquery.

Note: If you have extremely large amounts of records then it might be best to use the inner query and make a table then use that table as the source for the outer query.

SQL


SELECT T3.YourNumber
, T3.NextNum
, IIf([NextNum]-[YourNumber]=2,[YourNumber]+1,[YourNumber]+1 & "-" & [NextNum]-1) AS MissingNumbers
FROM

(SELECT Tbl_Numbers.InvoiceNo AS YourNumber
,(SELECT MIN(InvoiceNo) FROM Tbl_Numbers T1 WHERE T1.InvoiceNo >Tbl_Numbers.InvoiceNo) AS NextNum
FROM Tbl_Numbers) AS T3

WHERE ((([NextNum]-[YourNumber])>1));



Identifying Missing Dates

So if your data is like this.

8/1/09
8/2/09
8/5/09
8/10/09
8/14/09


The SQL will return this Missing Data:

8/3/09 - 8/4/09
8/6/09 - 8/9/09
8/11/09 - 8/13/09

Date Example 1:

To use the code all you need to adjust is the inner query in the FROM Clause with the subquery.

Note: If you have extremely large amounts of records then it might be best to use the inner query and make a table then use that table as the source for the outer query.

SQL

SELECT D3.YourDate
, D3.NextDate
, IIf(DateDiff("d",[YourDate],[NextDate])=2,DateAdd("d",1,[YourDate]),DateAdd("d",1,[YourDate]) & " - " & DateAdd("d",-1,[NextDate])) AS MissingDates
FROM
(SELECT tbl_Dates.TheDate As YourDate
, (SELECT Min(TheDate) FROM Tbl_Dates D1 WHERE D1.TheDate >Tbl_Dates.TheDate) As NextDate
FROM tbl_Dates) AS D3
WHERE (((DateDiff("d",[YourDate],[NextDate]))>1));


Date Example 2:

SQL

SELECT DATEADD("d",1,a.theDate) AS GapStart

, DATEADD("d",-1,b.theDate) AS GapEnd

FROM ( SELECT tbl_Dates.TheDate , (SELECT COUNT(*) FROM Tbl_Dates D1 WHERE D1.TheDate <= tbl_Dates.TheDate) AS ID

FROM tbl_Dates ) AS a

INNER JOIN

( SELECT tbl_Dates.TheDate, (SELECT COUNT(*) FROM Tbl_Dates D1 WHERE D1.TheDate <= tbl_Dates.TheDate) AS ID

FROM tbl_Dates ) AS b

ON a.ID=b.ID-1

WHERE DATEDIFF("d",a.theDate,DATEADD("d",-1,b.theDate))<>0;




--Dallr 08:47, 24 July 2010 (EDT)

Edit Discussion
Custom Search
Thank you for your support!
This page has been accessed 17,553 times.  This page was last modified 00:04, 3 February 2012 by Jack Leach. Contributions by pacala_ba and dallr  Disclaimers