UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Edit Discussion
> Find Missing Dates or Numbers within a Range    
(Difference between revisions)
Revision as of 15:44, 24 July 2010
Dallr (Talk | contribs)

← Previous diff		 Current revision
Jleach (Talk | contribs)
(u[dated to Article Layout)
Line 1: Line 1:
-This article was orginally created by Dane Miller, aka dallr and the sample application with the code can be found here:+{{Article}}
 +{{GeneralCleanup}}
-[[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]]
-----+
It shows how to use SQL to identifying missing dates or numbers within a range. It shows how to use SQL to identifying missing dates or numbers within a range.
-'''Identifying Missing Dates''' +----
- +
-''So if your data is like this.''+
- +
-8/1/09+
-8/2/09+'''Identifying Missing Numbers'''
 +The table structure:
 +Table Name : Tbl_Numbers
 +Field name : InvoiceNo
-8/5/09+''So if your data is like this.''
- +
-8/10/09 +
- +
-8/14/09 +
 +100<br />
 +101<br />
 +106<br />
 +107<br />
 +109<br />
 +115
''The SQL will return this Missing Data:'' ''The SQL will return this Missing Data:''
-8/3/09 - 8/4/09+102-105<br />
 +108<br />
 +110-114
-8/6/09 - 8/9/09 +Number Example Code:
-8/11/09 - 8/13/09 +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.
- 
-Date Example Code: 
[sql] [sql]
-SELECT DATEADD("d",1,a.theDate) AS GapStart 
- , DATEADD("d",-1,b.theDate) AS GapEnd+SELECT T3.YourNumber
 + , T3.NextNum
 + , IIf([NextNum]-[YourNumber]=2,[YourNumber]+1,[YourNumber]+1 & "-" & [NextNum]-1) AS MissingNumbers
 +FROM
-FROM ( SELECT tbl_Dates.TheDate, (SELECT COUNT(*) FROM Tbl_Dates D1 WHERE D1.TheDate <= tbl_Dates.TheDate) AS ID+ (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
- FROM tbl_Dates ) AS a+WHERE ((([NextNum]-[YourNumber])>1));
- INNER JOIN+[/sql]
- ( 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+'''Identifying Missing Dates'''
 +
 +''So if your data is like this.''
 +
 +8/1/09<br />
 +8/2/09<br />
 +8/5/09<br />
 +8/10/09 <br />
 +8/14/09
-WHERE DATEDIFF("d",a.theDate,DATEADD("d",-1,b.theDate))<>0; 
-[/sql] 
-----+''The SQL will return this Missing Data:''
-----+8/3/09 - 8/4/09<br />
 +8/6/09 - 8/9/09 <br />
 +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.
-'''Identifying Missing Numbers'''+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.
-''So if your data is like this.''+[sql]
-100+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));
-101+[/sql]
-106 
-107+Date Example 2:
-109+[sql]
 +SELECT DATEADD("d",1,a.theDate) AS GapStart
-115+ , 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
-''The SQL will return this Missing Data:''+ FROM tbl_Dates ) AS a
-102-105+ INNER JOIN
-108+ ( SELECT tbl_Dates.TheDate, (SELECT COUNT(*) FROM Tbl_Dates D1 WHERE D1.TheDate <= tbl_Dates.TheDate) AS ID
-110-114+ FROM tbl_Dates ) AS b
-----+ ON a.ID=b.ID-1
 +WHERE DATEDIFF("d",a.theDate,DATEADD("d",-1,b.theDate))<>0;
 +[/sql]
-Number Example Code:+----
-[sql]+
-SELECT T3.InvoiceNo , T3.NextNum, IIf([NextNum]-[InvoiceNo]=2,[Invoiceno]+1,[InvoiceNo]+1 & "-" & [NextNum]-1) AS MissingNumbers+
-FROM +
- (SELECT Tbl_Numbers.InvoiceNo,(SELECT MIN(InvoiceNo) FROM Tbl_Numbers T1 WHERE T1.InvoiceNo >Tbl_Numbers.InvoiceNo) AS NextNum FROM Tbl_Numbers) AS T3+
-WHERE ((([NextNum]-[InvoiceNo])>1));[/sql]+
---- ----
 +
--[[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,418 times.  This page was last modified 00:04, 3 February 2012 by Jack Leach. Contributions by pacala_ba and dallr  Disclaimers