I have a table where it is segmented along EPOCH, which is a 5 minute
time value that TTAV is recorded off of and grouped by TMCID. I am
being tasked with finding sections of the database where there is a gap
between EPOCHs, like 70, 71,73,74, and filling that gap with a
calculated TTAV value. I could do this easily by doing it manually, but
there is around 4000 TMCID, so I need automation. To which I have no
idea how to do.
It isn't, TTAV is a measured value from on site detectors. The detectors don't ping when there is nothing to measure, however, so there are EPOCHs that are missing in the database. I was told to fill these slots with calculated TTAV values by looking at distance and speed, but I don't have speed yet. Which is a problem that you cannot me with. I just need to figure out how to get the calculated values in there then figure out the calculations.
declare @ahtdben table(TMC varchar(50), DATE varchar(50), EPOCH int not null, TTAV int null )
;WITH Nums(Number) AS
(SELECT 1 AS Number
UNION ALL
SELECT Number+1 FROM Nums where Number < 10000 --you can make this smarter by grabbing the min max
)
insert into @NumbersTest(Number)
select Number from Nums option(maxrecursion 10000)
insert into @ahtdben
select '113N04516', '1212015', 70, 261
union
select '113N04516' ,'1212015', 71, 268
union
select '113N04516', '1212015', 73, 263
union
select '113N04516', '1212015', 74, 239
declare @min int, @max int
select @max = max(EPOCH) from @ahtdben
select @min = min(EPOCH) from @ahtdben
insert into @ahtdben
select distinct TMC, DATE, src.Number, null TTAV
from @ahtdben tgt
cross apply @NumbersTest src
where src.Number not in (select EPOCH from @ahtdben)
and src.Number between @min and @max
also keep in mind that if there is an error in your ping process, or lag in your ping process and you go ahead and add the gap, you would have to account for ping latency in the sense that lets say the missing gap comes few minutes later then you will need to rewire this to also account for not only inserting gaps but also updating the row that you un-gapped.
Well, that is a start. Thank you. The problem is that the tables I am working with has 28 million records. I can;t tell how to modify your code to make it work in that database.
please test that piece of code provided and see if it works. if it works and you are happy with it, please post your target table where this data will be inserted into. the more info your provide the easier it is for us to help you.
here it is with some explainations. the code is accomplishing what you asked in the first post
--create a table variable that will hold range of numbers between 1 and 10000
declare @NumbersTest table (EPOCH int not null) ;
;WITH Nums(EPOCH) AS
(SELECT 1 AS Number
UNION ALL
SELECT EPOCH+1 FROM Nums where EPOCH < 10000 --you can make this smarter by grabbing the min max
)
insert into @NumbersTest(EPOCH)
select EPOCH from Nums option(maxrecursion 10000)
--declare two variables
declare @min int, @max int
--assign to @max the biggest number in 2015_Weekday_10_4
select @max = max(EPOCH) from dbo.2015_Weekday_10_4
--assign to @min now holds the smallest number in 2015_Weekday_10_4
select @min = min(EPOCH) from dbo.2015_Weekday_10_4
--insert into our target table _Weekday_10_4 the missing/gap EPOCH
insert into dbo.2015_Weekday_10_4(TMC, DATE, src.EPOCH, TTAV)
select distinct TMC, DATE, src.EPOCH, null TTAV
from dbo.2015_Weekday_10_4 tgt
cross apply @NumbersTest src
where src.EPOCH not in (select EPOCH from dbo.2015_Weekday_10_4) --select from table @NumbersTest the EPOCH(s) that are missing
and src.EPOCH between @min and @max --do this only for numbers between the biggest and smallest sampling otherwise it will insert numbers higher than @max also
