Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2016
    Posts
    7

    Question Answered: How to "duplicate" rows as result of select for missed record

    Hi all
    maybe someone can help me..

    I have as input table the following


    MYDATE MYTIME MYVALUE
    2015-12-20 16:00:00 100
    2015-12-20 16:02:00 102
    2015-12-20 16:05:00 80
    2015-12-21 15:58:00 77
    2015-12-21 16:01:00 88
    2015-12-21 16:03:00 66



    I want to "virtually" have all record by date with a range time between for example 15:57:00 to 16:07:00 and for the missed record have the previous closer value
    all with 1 minutes frequency

    so


    MYDATE MYTIME MYVALUE
    2015-12-20 15:57:00 0 (no previous information available)
    2015-12-20 15:58:00 0
    2015-12-20 15:59:00 0
    2015-12-20 16:00:00 100
    2015-12-20 16:01:00 100
    2015-12-20 16:02:00 102
    2015-12-20 16:03:00 102
    2015-12-20 16:04:00 102
    2015-12-20 16:05:00 80
    2015-12-20 16:06:00 80
    2015-12-20 16:07:00 80
    2015-12-21 15:57:00 80 (but I can accept also 0 )
    2015-12-21 15:58:00 77
    2015-12-21 15:59:00 77
    2015-12-21 15:60:00 77
    2015-12-21 16:01:00 88
    2015-12-21 16:02:00 88
    2015-12-21 16:03:00 66
    2015-12-21 16:04:00 66
    2015-12-21 16:05:00 66
    2015-12-21 16:06:00 66
    2015-12-21 16:07:00 66



    I'm using db2 UDB 10.5.x
    Any idea is very appreciated..

    Thanks in advance!

  2. Best Answer
    Posted by mark.b

    "Hi,

    try this:
    Code:
    with s(mytime) as (
    values time('15:57:00')
      union all
    select mytime + 1 minute  
    from s
    where mytime<time('16:07:00')
    )
    , t as (
    select *
    from table(values
      (date('2015-12-20'), time('16:00:00'), 100)
    , (date('2015-12-20'), time('16:02:00'), 102)
    , (date('2015-12-20'), time('16:05:00'), 80)
    , (date('2015-12-21'), time('15:58:00'), 77)
    , (date('2015-12-21'), time('16:01:00'), 88)
    , (date('2015-12-21'), time('16:03:00'), 66)
    ) t (MYDATE, MYTIME, MYVALUE)
    ) 
    select d.MYDATE, s.MYTIME
    , t.MYVALUE MYVALUE_ORIGINAL
    , coalesce(t.MYVALUE, LAG(t.myvalue, 1, cast(NULL as int), 'IGNORE NULLS') over (order by d.MYDATE, s.MYTIME), 0) MYVALUE
    from s
    cross join (select distinct mydate from t) d
    left join t on s.mytime=t.mytime and d.mydate=t.mydate
    order by d.mydate, s.mytime
    ;
    "


  3. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    consider creating a numbers tables and LEFT JOIN from that table to yourdata table. that way rounds you get a row for every timeslot and matching data.
    it may need refining to handle hour crossovers

    but the general process is:-
    find the start time period for the sample, calculate / derive or find the endpoint of the sample
    perform the join using a where to limit the rows from the numbers table to the range you want / need
    I'd rather be riding on the Tiger 800 or the Norton

  4. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    a couple of questions:

    1. why in world would you want to
    2. aren't you lacking a LOT of entries? Based on what you specified, why don't we have almost 24 hours of minutes between these two entries?
    2015-12-20 16:07:00 80
    2015-12-21 15:57:00 80
    3. should the entries go until current time
    4. why wouldn't you use your presentation layer to show this, since the values do not change until your next entry.
    5. What time is this entry??? 15:60:00
    Dave

  5. #4
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    try this:
    Code:
    with s(mytime) as (
    values time('15:57:00')
      union all
    select mytime + 1 minute  
    from s
    where mytime<time('16:07:00')
    )
    , t as (
    select *
    from table(values
      (date('2015-12-20'), time('16:00:00'), 100)
    , (date('2015-12-20'), time('16:02:00'), 102)
    , (date('2015-12-20'), time('16:05:00'), 80)
    , (date('2015-12-21'), time('15:58:00'), 77)
    , (date('2015-12-21'), time('16:01:00'), 88)
    , (date('2015-12-21'), time('16:03:00'), 66)
    ) t (MYDATE, MYTIME, MYVALUE)
    ) 
    select d.MYDATE, s.MYTIME
    , t.MYVALUE MYVALUE_ORIGINAL
    , coalesce(t.MYVALUE, LAG(t.myvalue, 1, cast(NULL as int), 'IGNORE NULLS') over (order by d.MYDATE, s.MYTIME), 0) MYVALUE
    from s
    cross join (select distinct mydate from t) d
    left join t on s.mytime=t.mytime and d.mydate=t.mydate
    order by d.mydate, s.mytime
    ;
    Regards,
    Mark.

  6. #5
    Join Date
    May 2012
    Location
    Canberra, Australia
    Posts
    176
    Provided Answers: 6
    seems like a uni exercise to me
    Andy

    "All parts should go together without forcing. You must remember that the parts you are reassembling were disassembled by you. Therefore, if you can’t get them together again, there must be a reason. By all means, do not use hammer.” — IBM maintenance manual, 1975 "

  7. #6
    Join Date
    Jan 2016
    Posts
    7
    Hi Dave, to reply your questions.. only fyi (and this is not an uni exercise)

    a couple of questions:
    1. why in world would you want to
    This are data receiving from external system.
    They are information sent from physical machine sent to and ERP (really ERP ask data to machine.. but machine in production don't have anytime the 1 min frequency)

    2. aren't you lacking a LOT of entries? Based on what you specified, why don't we have almost 24 hours of minutes between these two entries?
    2015-12-20 16:07:00 80
    2015-12-21 15:57:00 80
    Really data are by "working" time.. so they are working from 10:00 AM to 6:00 PM (18:00)

    3. should the entries go until current time
    No.. inside the working time

    4. why wouldn't you use your presentation layer to show this, since the values do not change until your next entry.
    Data with a frequency of minutes must be used for a batchprocessing compating other data coming from another machine.. and the comparation should be
    at the same minutes.. but 2 machine can send or not sent the same "time".. and I need to join them.. so like virtually have missed data by minutes based on the last we know

    5. What time is this entry??? 15:60:00
    This is a clear mistake when I post the question.. it should be 16:00:00 ... in fact 16:00:00 is missed and there is 16:01:00

    Thanks to all and special thanks to Mark for the fully working example.

    Regards

Posting Permissions

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