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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy