October 24, 2016 at 8:08 am
Hi,
I am currently working on a test dataset, and trying to figure out the best way to do it.
I have days of the week and times which have been converted into ticks (864,000,000,000 ticks in a day).
I am receiving data which specifies a tick, and then a value for that tick. This value will be valid until the next tick occurs.
I am trying to create a SQL statement that will allow me to calculate which tick value is "active" at any given point in time. Given that this is representing time, and any given week, the values wrap around, and the last value of day 7 will be active at the start of day 1 again.
My current solution requires me to union two more datasets, to represent the boundaries that would be outside the range of those initially specified but still within the week, and then to join the table onto itself, offsetting it by 1.
I think this works, but the SQL doesn't look very elegant to me.
I wanted to make something like this into an in-line table valued function, which this is not, as I am having to make intermediate data sets for my joins. Does anyone have any ideas on improvements to this, that would not lose any of the current functionality?
DECLARE @testnumber bigINT
SET @testnumber = 0.5 * CAST(864000000000 AS BIGINT)
-- create initial data
DECLARE @numbers TABLE
(id INT IDENTITY(1,1),day_of_week TINYINT, total_ticks bigint, [testvalue] int)
INSERT INTO @numbers
VALUES
(1,1080000000000,1)
,(1,1584000000000,2)
, (2,1944000000000,3)
, (2,2448000000000,4)
, (3,2808000000000,5)
, (3,3312000000000,6)
, (4,3672000000000,7)
, (4,4176000000000,8)
, (5,4536000000000,9)
, (5,5040000000000,10)
-- adding outer boundaries
SELECT * INTO #temp
FROM
(
SELECT TOP 1 0 AS id, 1 AS day_of_week, 0 AS total_ticks, testvalue
FROM @numbers n
ORDER BY n.id desc ) AS tmptbl1
UNION ALL
SELECT id, day_of_week, total_ticks, testvalue
FROM @numbers
UNION ALL
SELECT * FROM
(
SELECT TOP 1 11 AS id, 7 AS day_of_week, 8*864000000000
AS total_ticks, testvalue
FROM @numbers n
ORDER BY n.id DESC ) AS tmptbl2
-- this is the "active" row for the @testnumber variable
SELECT t1.* FROM #temp t1
INNER JOIN #temp t2 ON t2.id = t1.id+1
WHERE @testnumber >= t1.total_ticks AND @testnumber < t2.total_ticks
DROP TABLE #temp
October 24, 2016 at 9:54 am
I would just use integer division and remainders. x / ticks_per_day gives the number of full days that have passed and x % ticks_per_day gives the remaining ticks. By the way, it looks like a tick is 100 nanoseconds.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 25, 2016 at 6:38 am
I understand what you're saying, but I am not sure how to incoporate that, into finding in which interval that my data lies? The actual case is more to do with something like opening times for a store. So at certain time periods, a site will be opened or closed, and I want to figure out that given any time, then was the store open or closed, based on what the "active" row is, on the table.
I could get the day and the ticks of a time, but the status of a site can change multiple times in the day, so I need to figure out, for a given number of total ticks, what the state currently is.
October 25, 2016 at 7:21 am
If my understanding is correct, and you're always after the next tick value, have you considered using LEAD (T-SQL), rather than doing a self join?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 25, 2016 at 8:02 am
That does look like I could rewrite things better with that, I'll give that a try. is there any way I can wrap it around though, so that day 7 links back to day 1? That's the bit I dislike the most, with the union statements.
October 25, 2016 at 8:14 am
Does day 1 always have a default value? is so LEAD has the option of providing this, as the format is [expression], [offset], [default value].
So for example you could have:
--Assumes that day 1 has a tick_value of 0
LEAD(Tick_value, 1, 0) OVER (ORDER BY Tick_value ASC) AS Next_tick
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 25, 2016 at 9:18 am
the first value is the rolled over from what the highest tick value is, as it is representing a week, and is rolling over. There is no set default value. If the highest day of week is midnight on day 5, this value will roll over through day 6, 7 and back through to the first value in day 1.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply