UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Edit Discussion
> Find Missing Dates or Numbers within a Range    
(Difference between revisions)
Revision as of 07:52, 1 August 2010
Pacala_ba (Talk | contribs)

← Previous diff		 Current revision
Jleach (Talk | contribs)
(u[dated to Article Layout)
Line 1: Line 1:
 +{{Article}}
 +{{GeneralCleanup}}
 +
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]] 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]]
Line 12: Line 15:
''So if your data is like this.'' ''So if your data is like this.''
-100+100<br />
- +101<br />
-101+106<br />
- +107<br />
-106+109<br />
- +
-107+
- +
-109+
- +
115 115
- 
''The SQL will return this Missing Data:'' ''The SQL will return this Missing Data:''
-102-105+102-105<br />
- +108<br />
-108+
- +
110-114 110-114
- 
Number Example Code: Number Example Code:
Line 62: Line 56:
''So if your data is like this.'' ''So if your data is like this.''
-8/1/09+8/1/09<br />
- +8/2/09<br />
-8/2/09+8/5/09<br />
- +8/10/09 <br />
-8/5/09+
- +
-8/10/09 +
- +
8/14/09 8/14/09
Line 75: Line 65:
''The SQL will return this Missing Data:'' ''The SQL will return this Missing Data:''
-8/3/09 - 8/4/09+8/3/09 - 8/4/09<br />
- +8/6/09 - 8/9/09 <br />
-8/6/09 - 8/9/09 +
- +
8/11/09 - 8/13/09 8/11/09 - 8/13/09
Line 130: Line 118:
--[[User:Dallr|Dallr]] 08:47, 24 July 2010 (EDT) --[[User:Dallr|Dallr]] 08:47, 24 July 2010 (EDT)
-[[Category:Table of Contents]] [[Category:Access Wiki Index]] 

Current revision

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)

Retrieved from "http://www.utteraccess.com/wiki/index.php/Find_Missing_Dates_or_Numbers_within_a_Range"
Edit Discussion
Thank you for your support!
This page has been accessed 6,434 times.  This page was last modified 00:04, 3 February 2012 by Jack Leach. Contributions by pacala_ba and dallr  Disclaimers