# Thread: finding the percentage difference

1. Registered User
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. Lost Boy
Join Date
Jan 2004
Location
Croatia, Europe
Posts
4,112
"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. Registered User
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. Registered User
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
•