I have designed an sql database to keep historical data of changes. I am tracking either full copy of my object or the change since the last version (very similar to incremental changes). I need to query the data based on date filters, I need to return all the versions in the specified date range to be able to recover all the versions in the specific range. Here is the structure example.
CREATE TABLE _Chenges( dates datetime, IsFull BIT, ObjectID INT, changes XML ) GO INSERT INTO _Chenges(dates, IsFull, objectID, changes) VALUES ('2010-01-01', 1, 1, '<FullChange></FullChange>'), ('2010-01-02', 1, 1, '<FullChange></FullChange>'), ('2010-01-03', 0, 1, '<IncrementalChange></IncrementalChange>'), ('2010-01-04', 0, 1, '<IncrementalChange></IncrementalChange>'), ('2010-01-05', 0, 1, '<IncrementalChange></IncrementalChange>'), ('2010-01-01', 1, 2, '<FullChange></FullChange>'), ('2010-01-02', 0, 2, '<IncrementalChange></IncrementalChange>'), ('2010-01-03', 1, 2, '<FullChange></FullChange>'), ('2010-01-04', 0, 2, '<IncrementalChange></IncrementalChange>'), ('2010-01-05', 0, 2, '<IncrementalChange></IncrementalChange>')
Now if I want to retrieve the data in between 2010-01-03 and 2010-01-04 I need to get following results.
dates IsFull ObjectID changes ----------------------- ------ ----------- --------- 2010-01-02 00:00:00.000 1 1 <FullChange /> 2010-01-03 00:00:00.000 0 1 <IncrementalChange /> 2010-01-04 00:00:00.000 0 1 <IncrementalChange /> 2010-01-03 00:00:00.000 1 2 <FullChange /> 2010-01-04 00:00:00.000 0 2 <IncrementalChange />
For objectID = 1 I need 2, 3, 4 rows to be able to recover all the version in the specified range (the last full version and all the changes till the end of range), and for ObjectID = 2 I need only 3, 4 rows (the last Full is already included in the 3rd version).
Is it possible to write a query that will access the table only once (without self-joins and self-referencing sub-queries)?