title: T-SQL query to flag repeat records
body: <p>I have a table that will have 500,000+ records.
  Each record has a LineNumber field which is not unique and not part of the primary key.
  Each record has a CreatedOn field.</p>
  
  <p>I need to update all 500,000+ records to identify repeat records.</p>
  
  <p>A repeat records is defined by a record that has the same LineNumber within the last seven days of its CreatedOn field.</p>
  
  <p><a href="https://i.stack.imgur.com/zS9Uy.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/zS9Uy.png" alt="alt text"></a>
   </p>
  
  <p>In the diagram above row 4 is a repeat because it occurred only five days since row 1.
  Row 6 is not a repeat even though it occurs only four days since row 4, but row 4 itself is already a repeat, so Row 6 can only be compared to Row 1 which is nine days prior to Row 6, therefore Row 6 is not a repeat.</p>
  
  <p>I do not know how to update the IsRepeat field with stepping through each record one-by-one via a cursor or something.</p>
  
  <p>I do not believe cursors is the way to go, but I'm stuck with any other possible solution.</p>
  
  <p>I have considered maybe Common Table Expressions may be of help but I have no experience with them and have no idea where to start.</p>
  
  <p>Basically this same process needs to be done on the table every day as the table is truncated and re-populated every single day.  Once the table is re-populated, I have to go through and re-mark each record if it is a repeat or not.</p>
  
  <p>Some assistance would be most appreciated.</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>Here is a script to create a table and insert test data</p>
  
  <pre><code>USE [Test]
  GO
  
  /****** Object:  Table [dbo].[Job]    Script Date: 08/18/2009 07:55:25 ******/
  IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Job]') AND type in (N'U'))
  DROP TABLE [dbo].[Job]
  GO
  
  USE [Test]
  GO
  
  /****** Object:  Table [dbo].[Job]    Script Date: 08/18/2009 07:55:25 ******/
  SET ANSI_NULLS ON
  GO
  
  SET QUOTED_IDENTIFIER ON
  GO
  
  IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Job]') AND type in (N'U'))
  BEGIN
  CREATE TABLE [dbo].[Job](
      [JobID] [int] IDENTITY(1,1) NOT NULL,
      [LineNumber] [nvarchar](20) NULL,
      [IsRepeat] [bit] NULL,
      [CreatedOn] [smalldatetime] NOT NULL,
   CONSTRAINT [PK_Job] PRIMARY KEY CLUSTERED 
  (
      [JobID] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  END
  GO
  
  
  SET NOCOUNT ON
  
  INSERT INTO dbo.Job VALUES ('1006',NULL,'2009-07-01 07:52:08')
  INSERT INTO dbo.Job VALUES ('1019',NULL,'2009-07-01 08:30:01')
  INSERT INTO dbo.Job VALUES ('1028',NULL,'2009-07-01 09:30:35')
  INSERT INTO dbo.Job VALUES ('1005',NULL,'2009-07-01 10:51:10')
  INSERT INTO dbo.Job VALUES ('1005',NULL,'2009-07-02 09:22:30')
  INSERT INTO dbo.Job VALUES ('1027',NULL,'2009-07-02 10:27:28')
  INSERT INTO dbo.Job VALUES (NULL,NULL,'2009-07-02 11:15:33')
  INSERT INTO dbo.Job VALUES ('1029',NULL,'2009-07-02 13:01:13')
  INSERT INTO dbo.Job VALUES ('1014',NULL,'2009-07-03 12:05:56')
  INSERT INTO dbo.Job VALUES ('1029',NULL,'2009-07-03 13:57:34')
  INSERT INTO dbo.Job VALUES ('1025',NULL,'2009-07-03 15:38:54')
  INSERT INTO dbo.Job VALUES ('1006',NULL,'2009-07-04 16:32:20')
  INSERT INTO dbo.Job VALUES ('1025',NULL,'2009-07-05 13:46:46')
  INSERT INTO dbo.Job VALUES ('1029',NULL,'2009-07-05 15:08:35')
  INSERT INTO dbo.Job VALUES ('1000',NULL,'2009-07-05 15:19:50')
  INSERT INTO dbo.Job VALUES ('1011',NULL,'2009-07-05 16:37:19')
  INSERT INTO dbo.Job VALUES ('1019',NULL,'2009-07-05 17:14:09')
  INSERT INTO dbo.Job VALUES ('1009',NULL,'2009-07-05 20:55:08')
  INSERT INTO dbo.Job VALUES (NULL,NULL,'2009-07-06 08:29:29')
  INSERT INTO dbo.Job VALUES ('1002',NULL,'2009-07-07 11:22:38')
  INSERT INTO dbo.Job VALUES ('1029',NULL,'2009-07-07 12:25:23')
  INSERT INTO dbo.Job VALUES ('1023',NULL,'2009-07-08 09:32:07')
  INSERT INTO dbo.Job VALUES ('1005',NULL,'2009-07-08 09:46:33')
  INSERT INTO dbo.Job VALUES ('1016',NULL,'2009-07-08 10:09:08')
  INSERT INTO dbo.Job VALUES ('1023',NULL,'2009-07-09 10:45:04')
  INSERT INTO dbo.Job VALUES ('1027',NULL,'2009-07-09 11:31:23')
  INSERT INTO dbo.Job VALUES ('1005',NULL,'2009-07-09 13:10:06')
  INSERT INTO dbo.Job VALUES ('1006',NULL,'2009-07-09 15:04:06')
  INSERT INTO dbo.Job VALUES ('1010',NULL,'2009-07-09 17:32:16')
  INSERT INTO dbo.Job VALUES ('1012',NULL,'2009-07-09 19:51:28')
  INSERT INTO dbo.Job VALUES ('1000',NULL,'2009-07-10 15:09:42')
  INSERT INTO dbo.Job VALUES ('1025',NULL,'2009-07-10 16:15:31')
  INSERT INTO dbo.Job VALUES ('1006',NULL,'2009-07-10 21:55:43')
  INSERT INTO dbo.Job VALUES ('1005',NULL,'2009-07-11 08:49:03')
  INSERT INTO dbo.Job VALUES ('1022',NULL,'2009-07-11 16:47:21')
  INSERT INTO dbo.Job VALUES ('1026',NULL,'2009-07-11 18:23:16')
  INSERT INTO dbo.Job VALUES ('1010',NULL,'2009-07-11 19:49:31')
  INSERT INTO dbo.Job VALUES ('1029',NULL,'2009-07-12 11:57:26')
  INSERT INTO dbo.Job VALUES ('1003',NULL,'2009-07-13 08:32:20')
  INSERT INTO dbo.Job VALUES ('1005',NULL,'2009-07-13 09:31:32')
  INSERT INTO dbo.Job VALUES ('1021',NULL,'2009-07-14 09:52:54')
  INSERT INTO dbo.Job VALUES ('1021',NULL,'2009-07-14 11:22:31')
  INSERT INTO dbo.Job VALUES ('1023',NULL,'2009-07-14 11:54:14')
  INSERT INTO dbo.Job VALUES (NULL,NULL,'2009-07-14 15:17:08')
  INSERT INTO dbo.Job VALUES ('1005',NULL,'2009-07-15 13:27:08')
  INSERT INTO dbo.Job VALUES ('1010',NULL,'2009-07-15 14:10:56')
  INSERT INTO dbo.Job VALUES ('1011',NULL,'2009-07-15 15:20:50')
  INSERT INTO dbo.Job VALUES ('1028',NULL,'2009-07-15 15:39:18')
  INSERT INTO dbo.Job VALUES ('1012',NULL,'2009-07-15 16:06:17')
  INSERT INTO dbo.Job VALUES ('1017',NULL,'2009-07-16 11:52:08')
  
  SET NOCOUNT OFF
  GO
  </code></pre>