SQLTeam.com | Weblogs | Forums

Inserting and then filling blanks between database records

sql2008

#1

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.

TMC DATE EPOCH TTAV
113N04516 1212015 70 261
113N04516 1212015 71 268
113N04516 1212015 73 263
113N04516 1212015 74 239


#2

please post your data as follows so we can help you

declare @ahtdben(TMC varchar(50), DATE datetime, EPOCH varchar(50), TTAV varchar(50))
insert into @ahtdben
select '113N04516', '1212015', '70', '261'
union

etc


#3

That's not going to work because I don't control the server.


#4

no I am asking you to post sample data for us to try out so we can answer your question :slight_smile:


#5

So, like this?

declare @ahtdben(TMC varchar(50), DATE datetime, EPOCH varchar(50), TTAV varchar(50))
insert into @ahtdben
select '113N04516', '1212015', '70', '261'
union '113N04516', '1212015', '71', '268'
union '113N04516', '1212015', '73', '263'
union '113N04516', '1212015', '74', '239';

I need to insert a blank row between 71 and 73 then fill it with calculated data for TTAV.


#6

yes sir. so TTAV is varchar or int?


#7

Int.


#8

how about EPOCH


#9

Int as well.


#10

how is TTAV calculated for the missing gap numbers


#11

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.


#12

declare @NumbersTest table (Number int not null)

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

select * from @ahtdben


#13

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.


#14

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.


#15

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.


#16

Yes it worked on my sqlserver. The Table name is 2015_Weekday_10_4.


#17

I can barely not understand what your code is trying to accomplish.


#18

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