CODEWITHSUNDEEP
sqlsql-server
Yetiish
Yetiish

Reputation: 703

SQL Join by rolling date period

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.

Here is the code to create the table and input data:

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')

http://sqlfiddle.com/#!6/b012c

What I would like is to display the data as follows:

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

Can anyone explain what technique to use?

Upvotes: 3

Views: 2268

Answers (2)

Christian Barron
Christian Barron

Reputation: 2755

This should work for what you're after:

Select P.Name, A.OldValue, A.NewValue

From Period P
LEFT JOIN
Period P2 on P2.Id = P.Id + 1
INNER JOIN
Audit A on A.DateModified > P.StartDate and (A.DateModified < P2.StartDate or P2.StartDate is null)

Upvotes: 1

ASh
ASh

Reputation: 35733

select 
  (select top 1 Name from Period where StartDate < DateModified order by StartDate desc),
  a.OldValue,  
  a.NewValue  
from Audit a

Upvotes: 2

Related Questions