Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Microsoft SQL Server: Programming FAQ

T-SQL Hints and Tips

How Can I Find Gaps in Date and Number Sequences? by tlbroadbent
Posted: 19 Jun 01 (Edited 6 Nov 02)

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
    Insert #t (ColD) Values(@bd)
    Set @bd=dateadd(day,1,@bd)

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

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
  Select MinID=min(a.PartId+1)
   From PartTable a Left Join PartTable b
         On a.PartId+1=b.PartId
    Where b.PartNumber Is Null

Please feel free to make recommendations for improvements or additions.

Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close