title: SQL Join by rolling date period
body: <p>I am using an SQL Server 2005 database. In it I have an Audit table, and would like to know when a value was changed relative to a period. The period simply has a start date, and any Audit change after it should be displayed up until the next periods start date. If there is no next period start date, I would also like to display the result. </p>
  
  <p>Here is the code to create the table and input data:</p>
  
  <pre><code>CREATE TABLE [dbo].[Period](
  [Id] [int] NOT NULL,
  [Name] [varchar](50) NOT NULL,
  [StartDate] [datetime] NOT NULL
  )
  
  INSERT INTO [dbo].[Period] VALUES (1, 'Period 1', '2015-03-01')
  INSERT INTO [dbo].[Period] VALUES (2, 'Period 2', '2015-04-01')
  INSERT INTO [dbo].[Period] VALUES (3, 'Period 3', '2015-05-01')
  
  CREATE TABLE [dbo].[Audit](
    [Id] [int] NOT NULL,
    [OldValue] [VARCHAR](50),
    [NewValue] [VARCHAR](50),
    [DateModified] [DATETIME] NOT NULL,
  )
  
  INSERT INTO [dbo].[Audit] VALUES (1, 'Old Value 1', 'New Value 1', '2015-03-27')
  INSERT INTO [dbo].[Audit] VALUES (2, 'Old Value 2', 'New Value 2', '2015-04-03')
  INSERT INTO [dbo].[Audit] VALUES (3, 'Old Value 3', 'New Value 3', '2015-04-09')
  INSERT INTO [dbo].[Audit] VALUES (4, 'Old Value 4', 'New Value 4', '2015-05-12')
  </code></pre>
  
  <p><a href="http://sqlfiddle.com/#!6/b012c" rel="nofollow">http://sqlfiddle.com/#!6/b012c</a></p>
  
  <p>What I would like is to display the data as follows:</p>
  
  <pre><code>Period 1 | Old Value 1 | New Value 1
  Period 2 | Old Value 2 | New Value 2
  Period 2 | Old Value 3 | New Value 3
  Period 3 | Old Value 4 | New Value 4
  </code></pre>
  
  <p>Can anyone explain what technique to use?</p>