How Can I Find Gaps in Date and Number Sequences?

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.
[tt][color maroon]
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[/color][/tt]

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.
[tt][color maroon]
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[/color][/tt]

Please feel free to make recommendations for improvements or additions.
_________________________