December 21, 2016 at 7:11 am
Thanks in advance for any tips on this:
I'm looking for some documentation on how to form queries that track changes over time in a temporal (system-versioned) table. For example, I'd like to query a temporal table and return a result-set for each day with the data's state, so I can visualize the changes over time.
So if I wanted to query the data as of 3 days ago, I could do this:
SELECT *
FROM schemaname.tablename
FOR SYSTEM_TIME AS OF '2016-12-18 23:59:59'
Which returns a result set of valid data for Dec 18th near midnight. I'd like to basically multiply this result set by 30 and get sets for each day at midnight.
Any tips? I'm thinking it'll have to be some sort of join on a temporary table containing the 30 datetime values, but I'm unsure on how I would join the SysStartTime and SysEndTime columns on a date that falls within their range. It's like a fuzzy join of some sort.
I'm essentially looking for a query syntax that would effectively do this dynamically:
SELECT *, '2016-12-18 23:59:59' as 'Date'
FROM schemaname.tablename
FOR SYSTEM_TIME AS OF '2016-12-18 23:59:59'
UNION
SELECT *, '2016-12-19 23:59:59' as 'Date'
FROM schemaname.tablename
FOR SYSTEM_TIME AS OF '2016-12-19 23:59:59'
UNION
SELECT *, '2016-12-20 23:59:59' as 'Date'
FROM schemaname.tablename
FOR SYSTEM_TIME AS OF '2016-12-20 23:59:59'
UNION
-- ...
...which would give me per-day result sets so I could track changes in my BI tools.
I could always write a stored procedure to dynamically generate this, but that doesn't feel quite right.
December 21, 2016 at 7:41 am
Use a number/tally/calendar table to iterate through the days.
http://www.sqlservercentral.com/articles/T-SQL/62867/
On a separate note, UNION eliminates duplicate rows, but your result sets are guaranteed unique (different dates). You are adding unnecessary overhead to your query by telling sql server to check for duplicates.
Wes
(A solid design is always preferable to a creative workaround)
December 21, 2016 at 12:30 pm
This certainly looks like it would facilitate what I'm trying to do. Thanks!
Also, great point on the UNION.
December 21, 2016 at 1:25 pm
This feels a bit dirty, but here's what I came up with:
DECLARE @Sql VARCHAR(max)
DECLARE @DateStart DATETIME
DECLARE @DateEnd DATETIME
SELECT @DateStart = MIN(SysStartTime)
,@DateEnd = GETDATE()
FROM SchemaName.TableName
SELECT @Sql = COALESCE(@Sql + ' ', '') + UnionQuery
FROM (
SELECT 'SELECT *, CAST(''' + CAST(TallyDate AS VARCHAR(20)) + ''' AS DATE) as ''DateKey'' FROM SchemaName.TableName FOR SYSTEM_TIME AS OF ''' + CAST(TallyDate AS VARCHAR(20)) + ''' UNION ALL ' AS 'UnionQuery'
FROM (
SELECT CAST(t.N - 1 + @DateStart AS DATE) AS 'TallyDate'
FROM dbo.Tally t
WHERE t.N - 1 + @DateStart <= @DateEnd
) dates
) s
SELECT @Sql = REVERSE(STUFF(REVERSE(@Sql), 1, 10, ''))
EXEC (@Sql)
It actually works like a charm. Would love to get some feedback on this, because it feels like I've just used a rocket launcher to smite a mosquito.
December 21, 2016 at 3:15 pm
Sonny Childs (12/21/2016)
This feels a bit dirty, but here's what I came up with:
DECLARE @Sql VARCHAR(max)DECLARE @DateStart DATETIME
DECLARE @DateEnd DATETIME
SELECT @DateStart = MIN(SysStartTime)
,@DateEnd = GETDATE()
FROM SchemaName.TableName
SELECT @Sql = COALESCE(@Sql + ' ', '') + UnionQuery
FROM (
SELECT 'SELECT *, CAST(''' + CAST(TallyDate AS VARCHAR(20)) + ''' AS DATE) as ''DateKey'' FROM SchemaName.TableName FOR SYSTEM_TIME AS OF ''' + CAST(TallyDate AS VARCHAR(20)) + ''' UNION ALL ' AS 'UnionQuery'
FROM (
SELECT CAST(t.N - 1 + @DateStart AS DATE) AS 'TallyDate'
FROM dbo.Tally t
WHERE t.N - 1 + @DateStart <= @DateEnd
) dates
) s
SELECT @Sql = REVERSE(STUFF(REVERSE(@Sql), 1, 10, ''))
EXEC (@Sql)
It actually works like a charm. Would love to get some feedback on this, because it feels like I've just used a rocket launcher to smite a mosquito.
With regard to the part of your post I bolded, I just have to know when that became a bad thing... 😉
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 21, 2016 at 3:52 pm
Sonny Childs (12/21/2016)
Would love to get some feedback on this, because it feels like I've just used a rocket launcher to smite a mosquito.
Tally table provides the power of a rocket launcher with the labor of a pop cap.
I've put some comments inline. However, I don't have any temporal tables in my current environment (yet) so I can't validate the syntax of the FOR clause.
DECLARE @Sql VARCHAR(max)=''; --Set the blank string here so you don't need the null test later;
DECLARE @DateStart DATETIME
DECLARE @DateEnd DATETIME = GETDATE();
SELECT @DateStart = DATEADD(DAY,-1,MIN(SysStartTime)) --subtract a single day here rather than for every row within the worker query.
FROM SchemaName.TableName
SELECT @Sql = @SQL + UnionQuery --Can remove coalesce because variable was set to empty string in declaration
FROM (
SELECT 'SELECT *, ''' + TallyDate + ''' as ''DateKey'' FROM SchemaName.TableName FOR SYSTEM_TIME AS OF ''' + TallyDate + ''' UNION ALL ' AS UnionQuery
FROM (
SELECT CONVERT(CHAR(8),DATEADD(DAY,t.N,@DateStart),112) AS TallyDate
--built in date functions are very efficient and easier to read,
--converting to a string here saves multiple string conversions in the outer query
--style 112 is universal date format for sql server
FROM dbo.Tally AS T
WHERE N < DATEDIFF(DAY,@DateStart,@DateEnd)
--expressions on variables are much quicker,
--fields and expressions are on opposite sides of comparison which helps performance
) dates
) s
SELECT @Sql = LEFT(@Sql,LEN(@sql) -10) --Personal preference, I think this is easier to read than Reverse/Stuff/Reverse
EXEC (@Sql)
Hopefully it still executes for you. 🙂
On a separate note, I highly recommend using midnight rather than 23:59:59. It is so much easier to work, think and converse when you don't have the time component included.
Wes
(A solid design is always preferable to a creative workaround)
December 22, 2016 at 8:54 am
whenriksen (12/21/2016)
Tally table provides the power of a rocket launcher with the labor of a pop cap.
I've put some comments inline. However, I don't have any temporal tables in my current environment (yet) so I can't validate the syntax of the FOR clause.
...
Hopefully it still executes for you. 🙂
On a separate note, I highly recommend using midnight rather than 23:59:59. It is so much easier to work, think and converse when you don't have the time component included.
Thanks, whenriksen.
That's some fantastic feedback, I'll certainly include these.
Oddly enough, the performance was identical either way, after running some benchmarks, but I certainly need to keep those optimizations in mind in general.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply