Here are a couple of techniques for finding gaps in date or number sequences.
Finding gaps in a date sequence
Suppose a procedure is supposed to run daily and record the run date and time in a log table. In order to confirm that it ran each day of the last week or month you could use the following T-SQL code.
CREATE Procedure usp_ListMissingDates @bd datetime, @ed datetime As
Set nocount on
Create Table #t (ColD datetime)
While @bd<=@ed Begin Insert #t (ColD) Values(@bd) Set @bd=dateadd(day,1,@bd) End
Select Distinct MissingDate=a.ColD From #t a Left Join EventLog.dbo.AppEventLog b On a.ColD=convert(char(11),b.RunDateTime) Where b.ProcessID Is Null Order By 1
Drop Table #t
Set nocount off GO
Finding gaps in an identity column sequence
Suppose a table has an identity column and gaps exist in the identity sequence. Suppose you want to identify the gaps and fill them in. (I don't know why one would want to do this but we are just supposing. ) The following T-SQL code will find the next gap in the sequence.
Create Procedure usp_FindNextPartID As
Declare @MinID int Select @MinID = Min(PartID) From PartTable
If @MinID > 1 Select MinID=1 Else Select MinID=min(a.PartId+1) From PartTable a Left Join PartTable b On a.PartId+1=b.PartId Where b.PartNumber Is Null Go
Please feel free to make recommendations for improvements or additions. _________________________