Thread: filling in gaps in data
04-08-10, 16:08 #1Registered User
- Join Date
- Nov 2008
Unanswered: filling in gaps in data
I've got a query that returns the time, name, value over a given time period:
Select smpl_time, sgnl_id, max(lastval-firstval) val from (SELECT TRUNC(t1.smpl_time,'MI') smpl_time, t2.name sgnl_id, (last_value(t1.float_val IGNORE NULLS) over (partition BY TRUNC(t1.smpl_time,'MI') order by TRUNC(t1.smpl_time,'MI'))) lastval, (first_value(t1.float_val IGNORE NULLS) over (partition BY TRUNC(t1.smpl_time, 'DD') order by t1.smpl_time)) firstval FROM chan_arch.sample t1, chan_arch.channel t2 WHERE t2.name IN('Sig556') AND t2.channel_id=t1.channel_id AND t1.smpl_time BETWEEN TRUNC(sysdate-2.0 ,'DD') AND sysdate ) WHERE smpl_time BETWEEN sysdate-2.0 AND sysdate group by smpl_time, sgnl_id
So I need to insert a single record which is more or less like this:
Select sysdate, 'Sig556', lastValidEntry FROM [above results]
So I don't even need to query for the time or name, since I'll insert the duplicated value at the current time. This seems like it should be easy. I've tried several UNION ALL queries but can't get anything to work.
04-08-10, 16:20 #2Registered User
Provided Answers: 1
- Join Date
- Aug 2003
- Where the Surf Meets the Turf @Del Mar, CA
You could (outer) join against a "calendar" table that would have every period desired.You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Good judgement comes from experience. Experience comes from bad judgement.