history table point in time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

history table point in time

Author

Topic

hoggy
Starting Member

11 Posts

Posted - 2012-05-30 : 07:21:05

Hi I've built a history / audit table combining ideas from these:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=174995
http://thefirstsql.com/2010/05/21/create-an-audit-table-on-the-fly/#comment-39115

I am looking to script a point in time query for each table. I know I need to join the table to itself. I did not use a "data version" column so I cannot join on dataversion =dataversion-1, so I guess I need to find the max last changed date. Not sure how to go about this.

Here is a script to create and fill a simplified demo table:
CREATE TABLE [dbo].[aTable](
[comment] [varchar](max) NULL,
[dt] [datetime2](7) NULL,
[operation] [char](1) NULL,
[ID] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[aTable] ([comment], [dt], [operation], [ID]) VALUES (N'test', CAST(0x07000000000026350B AS DateTime2), N'O', 1)
INSERT [dbo].[aTable] ([comment], [dt], [operation], [ID]) VALUES (N'testa', CAST(0x07000000000045350B AS DateTime2), N'U', 1)
INSERT [dbo].[aTable] ([comment], [dt], [operation], [ID]) VALUES (N'testb', CAST(0x07000000000062350B AS DateTime2), N'U', 1)
INSERT [dbo].[aTable] ([comment], [dt], [operation], [ID]) VALUES (N'testb', CAST(0x07000000000081350B AS DateTime2), N'D', 1)
INSERT [dbo].[aTable] ([comment], [dt], [operation], [ID]) VALUES (N'testc', CAST(0x070000000000BE350B AS DateTime2), N'I', 1)
/****** Object: Default [DF_aTable_dt] Script Date: 05/30/2012 11:58:19 ******/
ALTER TABLE [dbo].[aTable] ADD CONSTRAINT [DF_aTable_dt] DEFAULT (getdate()) FOR [dt]

/*
Operation: O = Original data when history table created, I=Insert, D=Delete, U=Update.
*/

I want to create a function with an input date to show table how it looked at a point in time.

This is what I have so far based on Henning's advice
(I've created a new thread since my table is fairly different to his):

--Create Function fn_aTable ( @PointInTime datetime)
--AS
DECLARE @PointInTime datetime = '2012-05-30 12:00:00.000'
;WITH cte AS (
SELECT *, ValidFrom = dt, ValidTo = '9999-12-31'
FROM [aTable]
UNION ALL
SELECT a.*, ValidFrom = a.dt, ValidTo = b.dt
FROM [aTable] a
INNER JOIN (
SELECT * from [aTable]

UNION ALL
SELECT * FROM [aTable]

) AS b
ON a.ID = b.ID

AND a.dt > b.dt
and a.operation <>'D'
)
SELECT *
FROM cte
WHERE @PointInTime BETWEEN ValidFrom AND ValidTo

hoggy
Starting Member

11 Posts

Posted - 2012-05-30 : 09:52:06

I got a query working, I am interested in any comments on improvement regarding performance if any. Thank you in advance.

DECLARE @PointInTime datetime = '2012-02-01 12:00:00.000'

SELECT t1.*
FROM [aTable] t1
INNER JOIN
(
SELECT ID, MAX(dt) AS MaxDateTime
FROM [aTable]
where dt <=@PointInTime
GROUP BY ID
) t2 ON t1.ID = t2.ID AND t1.dt = t2.MaxDateTime
where operation<>'D'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources