Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2008
    Posts
    13

    Unanswered: filling in gaps in data

    I've got a query that returns the time, name, value over a given time period:

    Code:
    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
    However, sometimes there are gaps in the data. Since I'm looking at values that are ever increasing and building a chart based off this data, I'd like to just insert one bogus record at the end to basically 'flat-line' the data if there are gaps (which happens frequently when the machine is off- there will be no records for that time).

    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.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •