INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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.

Jobs

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
  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.
_________________________

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

My Archive

Resources

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