Results 1 to 6 of 6

Thread: syntax

  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: syntax

    Hi

    I have a table having a datetime column in which values come in every hour. These values are accumulative so I need to find the difference for every hour. Like the value at 2am - the value at 1am, similarly 3am -2am, 4am - 3am.....etc.

    Would appreciate if you could advise on how to write the query?

    I've attached a file 'Accum Values' as an example.

    Best Regards
    Shajju
    Attached Files Attached Files

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

  3. #3
    Join Date
    Aug 2008
    Posts
    464
    As always, you're right and I tried to use LAG but didn't get the expected results:

    FRPVCRXBYTES at (3AM):281284230343
    FRPVCRXBYTES at (4AM):281515035890

    To get the difference, I used:

    FRPVCRXBYTES-(NVL(LAG(FRPVCRXBYTES) OVER (ORDER BY TRUNC(DATETIME,'HH24')),0)) as FRPVCRXBYTES,

    but the result is not giving me the difference. I don't know what it's giving me.

    FRPVCRXBYTES should be:230805547

    but it giving me:11335515796

    Would appreciate your help.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Since I can not reproduce what you report, I can only suggest you continue to use LAG to simply report adjacent rows.
    Hopefully once you understand how to do that you can progress to computing the difference.

    http://asktom.oracle.com contains many fine coding examples.
    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.

  5. #5
    Join Date
    Aug 2008
    Posts
    464
    Thanks.

    I've tested the LAG function on 2 separate dbs on 2 different PCs. On one PC I manually created a table and inserted 15 random values in it between 0 and 10000.
    NVL(LAG(FRPVCRX) over (order by datetime),0) PrevValue

    gave me what I expected.

    But on the other PC (production), same 9i db though, the values are inserted into a table and are like: 234354657854 (12 digits).

    Using LAG in the same manner as above, I do not get the previous value in the PrevValue column. Not sure if there are any restrictions on the LAG. Really confused.

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    What is the format of your datetime columns. They look like number or character, not a date column. If it is NOT a date column then you have to convert to date before your subtract.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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