October 5, 2011 at 3:05 am
Hi all,
I have a following table
CREATE TABLE #Records
(
[ID] INT,
[Date] DATETIME,
[Attrib] VARCHAR(max)
)
INSERT INTO #Records ([ID], [Date], [Attrib])
VALUES(1, '2011-10-01', 'red')
INSERT INTO #Records ([ID], [Date], [Attrib])
VALUES(2, '2011-10-02', 'blue')
INSERT INTO #Records ([ID], [Date], [Attrib])
VALUES(3, '2011-10-03', 'yellow')
INSERT INTO #Records ([ID], [Date], [Attrib])
VALUES(4, '2011-10-04', 'green')
INSERT INTO #Records ([ID], [Date], [Attrib])
VALUES(5, '2011-10-05', 'white')
and need to make a query that would return kind of "change log" per row.
I.e. the query should return the [Attrib] value per row as well as its immediate precedent in time as old value e.g.:
CREATE TABLE #Records2
( [ID] INT,
[Date] DATETIME,
[Attrib] VARCHAR(max),
[Old_Value] VARCHAR(max)
)
INSERT INTO #Records2([ID], [Date], [Attrib], [Old_Value])
VALUES(1, '2011-10-01', 'red', NULL)
INSERT INTO #Records2 ([ID], [Date], [Attrib], [Old_Value])
VALUES(2, '2011-10-02', 'blue', 'red')
INSERT INTO #Records2 ([ID], [Date], [Attrib], [Old_Value])
VALUES(3, '2011-10-03', 'yellow', 'blue')
INSERT INTO #Records2 ([ID], [Date], [Attrib], [Old_Value])
VALUES(4, '2011-10-04', 'green', 'yellow')
INSERT INTO #Records2 ([ID], [Date], [Attrib], [Old_Value])
VALUES(5, '2011-10-05', 'white', 'green')
select * from #Records2
Tried several approaches (UNION, self join, CTEs) - but could not find the right solution.
Can anybody point me in the right direction?
Many thanks for any hints.
Marin
October 5, 2011 at 3:18 am
Marin
A self join is the way to go - on r1.ID = r2.ID-1. Show us what you've tried, and we'll show you where you're going wrong.
John
October 5, 2011 at 6:22 am
Hello John,
many thanks for the quick reply.
In the meantime I found the answer in the very form I need it:
http://www.sqlservercentral.com/Forums/Topic515146-338-1.aspx
Regards,
Marin
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply