Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: finding the percentage difference

    Hi

    I need to find the % increase or decrease between a value of a KPI between yesterday and today everyday.

    The first query giving a value of KPI1 is:

    Code:
    SELECT to_char(DATETIME,'mm/dd/yyyy hh24:mi')datetime, BSC, CELL, KPI1
    FROM SCHEMA.TABLE
    WHERE DATETIME BETWEEN trunc(SYSDATE)-1 and trunc(SYSDATE)-1/3600 and cell='CELLA'
    and the second query for the same KPI a day before is:

    Code:
    SELECT to_char(DATETIME,'mm/dd/yyyy hh24:mi')datetime, BSC, CELL
    , KPI2
    FROM SCHEMA.TABLE
    WHERE DATETIME BETWEEN trunc(SYSDATE)-2 and trunc(SYSDATE)-1-1/3600 and cell='CELLA')
    How can I find the % difference between KPI1 and KPI2? so that I get

    Code:
    datetime, bsc, cell, %difference(KPI name)
    Thanks & Regards

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    "Yesterday" and "today" sound like "previous record" and "this record". If that's the case, research use of LEAD and LAG functions; they might help get the result.

  3. #3
    Join Date
    Aug 2008
    Posts
    464
    Thanks for the tip.

    Basically, I have 1000 cells for the same datetime listed in order and corresponding values of KPI1. I want to find the difference in the value of KPI1 for the same cell for 2 consecutive days which means I have to put an offset of 1000
    BUT
    The problem is that this number '1000' is dynamic and the cells increase/decrease dynamically so I'm looking for a way to make the LAG (or any other function for that matter) to remember the cell when it computes the difference.

    Hope I've not confused you.

  4. #4
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Well I'm confused.

    Aren't there many values of a cell over the course of a day? So wouldn't you want to take the average (or max, min, whatever) of each cell and compare to the average for each cell for the previous day.

    Code:
    WITH base AS (
      SELECT  t.cell, trunc(t.datetime) AS dt, AVG(kpi1) kpi
      FROM schema.table t
      GROUP BY t.cell, trunc(t.datetime) 
      WHERE t.datetime > trunc(SYSDATE) - 7 -- limit to last week
    ) 
    SELECT d.cell, d.dt, (d.kpi - prev_d.kpi) / prev_d.kpi pct_diff
    FROM base d
    JOIN base prev_d ON d.cell = prev_d.cell AND d.dt = prev_d + 1

Posting Permissions

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