Home » SQL & PL/SQL » SQL & PL/SQL » Getting the difference of 2 rows
Getting the difference of 2 rows [message #418479] Sun, 16 August 2009 04:39 Go to next message
shajju
Messages: 29
Registered: August 2009
Junior Member
Hi

I have a table which contains values for each hour. These values are accumulative. I want to find the difference between 2 consecutive hours like how much the value has increased when the next value came in the following hour.

So something like the value of FRPVCRXBYTES at hour 'n' minus the value at hour 'n-1'.

Datetime MOID FRPVCRXBYTES FRPVCTXBYTES

08/15/2009 00:00:00 A 280208152178 50301256405
08/15/2009 01:00:00 A 280580249002 50311827732
08/15/2009 02:00:00 A 280951814842 50321824745
08/15/2009 03:00:00 A 281284230343 50349776153
08/15/2009 04:00:00 A 281515035890 50384262472
08/15/2009 05:00:00 A 281632621095 50397496517
08/15/2009 06:00:00 A 281841214783 50401828723
08/15/2009 07:00:00 A 282013234387 50418663084
08/15/2009 08:00:00 A 282150771515 50431446726
08/15/2009 09:00:00 A 282245528697 50446453271
08/15/2009 10:00:00 A 282410128844 50469200278
08/15/2009 11:00:00 A 282730735794 50501886790
08/15/2009 12:00:00 A 282899369538 50542086173

Regards
Re: getting the difference... [message #418482 is a reply to message #418479] Sun, 16 August 2009 06:23 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at LAG/LEAD functions.

Post a working Test case: create table and insert statements along with the result you want with these data.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: getting the difference... [message #418483 is a reply to message #418482] Sun, 16 August 2009 06:49 Go to previous messageGo to next message
shajju
Messages: 29
Registered: August 2009
Junior Member
Hi

Thanks for replying. Data is already being loaded into a table and I need to create a report from that data bearing in mind that the data is accumulative.

I have had a look at the lag/lead functions but didn't fully understand how to use them.

I'm sorry if I haven't replied as you expected.

Regards
Sheraz
Re: getting the difference... [message #418488 is a reply to message #418483] Sun, 16 August 2009 08:51 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I have had a look at the lag/lead functions but didn't fully understand how to use them.

Use SQL*Plus and copy and paste what you already tried.

Quote:
Data is already being loaded into a table and I need to create a report from that data bearing in mind that the data is accumulative.

Anyway, build a test case that represents your problem it does not need to be the real data and table just close enough to them that what we may post cen be applied to your case.

Regards
Michel
Re: getting the difference... [message #418490 is a reply to message #418488] Sun, 16 August 2009 09:11 Go to previous messageGo to next message
shajju
Messages: 29
Registered: August 2009
Junior Member
You must forgive my naiveness, but if you could guide me a bit like what to include in the test case because I don't know how else to explain. Thanks.

Situation:
Data comes in every hour and every time it comes in it is accumulative (meaning the previous value is incremented).

Required:
I need to only query the increment.

Query:

select datetime,moid,frpvcrxbytes,frpvctxbytes,

round(((FRPVCRXBYTES*8)/1000)/3600,5) "RX_Kb/Sec",

round(((((FRPVCRXBYTES*8)/1000)/3600)/(832))*100,5) "RX_UTILIZATION%",

round(((FRPVCTXBYTES*8)/1000)/3600,5) "TX_Kb/Sec",

round(((((FRPVCTXBYTES*8)/1000)/3600)/(832))*100,5) "TX_UTILIZATION%"

from schema.table

where

datetime between trunc(sysdate)-1 and trunc(sysdate)-1/24

order by moid, datetime

How can query FRPVCRXBYTES and FRPVCTXBYTES so that I only get the increment in every hour?

RESULT attached.
  • Attachment: Result.txt
    (Size: 2.20KB, Downloaded 83 times)
Re: getting the difference... [message #418491 is a reply to message #418490] Sun, 16 August 2009 09:19 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't see you tried the LAG function in what you posted. Frm what (I understand of what) you posted I don't see any reason why you can't use LAG function.

You have the table definition, we don't.
You have the data, we don't.
Without a test case I can't help.

Please read the link I posted and format your post.

Regards
Michel
Re: getting the difference... [message #418493 is a reply to message #418479] Sun, 16 August 2009 10:36 Go to previous messageGo to next message
shajju
Messages: 29
Registered: August 2009
Junior Member
Michel

Can you help me create a test case please?

or use the LAG function.

Apologies if I sound clueless.

Regards
Re: getting the difference... [message #418494 is a reply to message #418493] Sun, 16 August 2009 10:43 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create table t (dt date, val integer);

Table created.

SQL> insert into t
  2  select trunc(sysdate,'hh24')+level/24 dt, 
  3         1000*level+round(dbms_random.value(100,500)) val
  4  from dual
  5  connect by level <= 10
  6  /

10 rows created.

SQL> select * from t order by dt
  2  /
DT                         VAL
------------------- ----------
16/08/2009 18:00:00       1355
16/08/2009 19:00:00       2394
16/08/2009 20:00:00       3432
16/08/2009 21:00:00       4361
16/08/2009 22:00:00       5335
16/08/2009 23:00:00       6207
17/08/2009 00:00:00       7388
17/08/2009 01:00:00       8330
17/08/2009 02:00:00       9176
17/08/2009 03:00:00      10248

10 rows selected.

SQL> select dt, val,
  2         val-nvl(lag(val) over (order by dt),0) diff
  3  from t
  4  order by dt
  5  /
DT                         VAL       DIFF
------------------- ---------- ----------
16/08/2009 18:00:00       1355       1355
16/08/2009 19:00:00       2394       1039
16/08/2009 20:00:00       3432       1038
16/08/2009 21:00:00       4361        929
16/08/2009 22:00:00       5335        974
16/08/2009 23:00:00       6207        872
17/08/2009 00:00:00       7388       1181
17/08/2009 01:00:00       8330        942
17/08/2009 02:00:00       9176        846
17/08/2009 03:00:00      10248       1072

10 rows selected.

Regards
Michel
Re: getting the difference... [message #418495 is a reply to message #418479] Sun, 16 August 2009 11:29 Go to previous messageGo to next message
shajju
Messages: 29
Registered: August 2009
Junior Member
Thanks Michel...I tried using your example:

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 (3AM):281284230343
FRPVCRXBYTES (4AM):281515035890

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

FRPVCRXBYTES should be:230805547

but it giving me:11335515796

Would appreciate your help.

Re: getting the difference... [message #418496 is a reply to message #418495] Sun, 16 August 2009 11:33 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste your session.

And once again FORMAT YOUR POST.
I will no more answer if your next post is not.

Regards
Michel

[Updated on: Sun, 16 August 2009 11:34]

Report message to a moderator

Re: getting the difference... [message #418497 is a reply to message #418479] Sun, 16 August 2009 11:48 Go to previous messageGo to next message
shajju
Messages: 29
Registered: August 2009
Junior Member
I'm sorry Michel..I'm not used to SQLPLUS Sad Can use Toad if you like.

Why wouldn't your example work in my case!!!! Sorry for being very awkward.
Re: getting the difference... [message #418498 is a reply to message #418479] Sun, 16 August 2009 11:57 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
You provide us

No DDL for your table
No DML for test data
No actual SQL to produce your results.
Therefore no assistance for you is possible at this time.
Re: getting the difference... [message #418499 is a reply to message #418498] Sun, 16 August 2009 12:19 Go to previous messageGo to next message
shajju
Messages: 29
Registered: August 2009
Junior Member
Apologies if the format is not correct once again but I honestly don't know how to format it.

This is how I created the table I am trying to populate:

CREATE TABLE SGSN_FRAMERELAY_HR (
DATETIME DATE,
MOID VARCHAR(25),
PERIOD_DURATION NUMBER,
FRPVCRXBECNS NUMBER,
FRPVCRXBYTES NUMBER,
FRPVCRXDE NUMBER,
FRPVCRXFECNS NUMBER,
FRPVCRXFRAMES NUMBER,
FRPVCRXSTOPS NUMBER,
FRPVCTXBYTES NUMBER,
FRPVCTXDE NUMBER,
FRPVCTXFRAMES NUMBER,
FRPVCTXSTOPS NUMBER,
ENTRIES
)
NOLOGGING
PCTFREE 60
TABLESPACE ERI_SGSN_FRAMERELAY_AGG_D
PARTITION BY RANGE (DATETIME)
(
PARTITION P19900101 VALUES LESS THAN (TO_DATE('1990-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
);

-- CONSTRAINT
-- ----------
ALTER TABLE SGSN_FRAMERELAY_HR ADD (CONSTRAINT SGSN_FRAMERELAY_HR_PK PRIMARY KEY(DATETIME,MOID) USING INDEX LOCAL TABLESPACE ERI_SGSN_FRAMERELAY_AGG_I);

-- GRANT
-- -----
GRANT SELECT ON SGSN_FRAMERELAY_HR TO SHAJJU;

I did mention the SQL (sorry again for no formatting):

SELECT
TRUNC(DATETIME,'HH24') DATETIME, MOID,
SUM(NVL(PERIOD_DURATION,0)) PERIOD_DURATION,


--val-nvl(lag(val) over (order by dt),0) diff


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


COUNT(*) ENTRIES
FROM ERICSSON_GPRS.SGSN_FRAMERELAY
WHERE DATETIME BETWEEN trunc(sysdate)-1 and trunc(sysdate) and
MOID IN

('2.3.1.1.401','2.4.1.1.411','2.3.3.1.402','2.4.3.1.412',
'2.5.1.1.403','2.6.1.1.413','2.5.3.1.404','2.6.3.1.414','2.7.1.1.405','2.8.1 .1.415','2.7.3.1.406','2.8.3.1.416','1.6.1.1.407',
'1.7.1.1.417','1.6.3.1.408','1.7.3.1.418')


GROUP BY TRUNC(DATETIME,'HH24'), FRPVCRXBYTES, MOID
order by moid, datetime

[Updated on: Sun, 16 August 2009 12:23] by Moderator

Report message to a moderator

Re: getting the difference... [message #418500 is a reply to message #418499] Sun, 16 August 2009 12:22 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
(sorry again for no formatting):

You have no excuse when it has been asked more than 3 times and you stay in the same behaviour.

Michel Cadot wrote on Sun, 16 August 2009 13:23
...
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Also always post your Oracle version with 4 decimals.

Regards
Michel


[Updated on: Sun, 16 August 2009 12:24]

Report message to a moderator

Re: getting the difference... [message #418502 is a reply to message #418499] Sun, 16 August 2009 12:39 Go to previous messageGo to next message
shajju
Messages: 29
Registered: August 2009
Junior Member
CREATE TABLE SGSN_FRAMERELAY_HR (
DATETIME DATE,
MOID VARCHAR(25),
PERIOD_DURATION NUMBER,
FRPVCRXBECNS NUMBER,
FRPVCRXBYTES NUMBER,
FRPVCRXDE NUMBER,
FRPVCRXFECNS NUMBER,
FRPVCRXFRAMES NUMBER,
FRPVCRXSTOPS NUMBER,
FRPVCTXBYTES NUMBER,
FRPVCTXDE NUMBER,
FRPVCTXFRAMES NUMBER,
FRPVCTXSTOPS NUMBER,
ENTRIES
)
NOLOGGING
PCTFREE 60
TABLESPACE ERI_SGSN_FRAMERELAY_AGG_D
PARTITION BY RANGE (DATETIME)
(
PARTITION P19900101 VALUES
LESS THAN (TO_DATE('1990-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
);

-- CONSTRAINT
-- ----------
ALTER TABLE SGSN_FRAMERELAY_HR
ADD (CONSTRAINT SGSN_FRAMERELAY_HR_PK PRIMARY KEY(DATETIME,MOID)
USING INDEX LOCAL TABLESPACE ERI_SGSN_FRAMERELAY_AGG_I);

-- GRANT
-- -----
GRANT SELECT ON SGSN_FRAMERELAY_HR TO SHAJJU;
Re: getting the difference... [message #418503 is a reply to message #418500] Sun, 16 August 2009 12:41 Go to previous messageGo to next message
shajju
Messages: 29
Registered: August 2009
Junior Member
[code]
SELECT
TRUNC(DATETIME,'HH24') DATETIME, MOID,
SUM(NVL(PERIOD_DURATION,0)) PERIOD_DURATION,


--val-nvl(lag(val) over (order by dt),0) diff


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


COUNT(*) ENTRIES
FROM ERICSSON_GPRS.SGSN_FRAMERELAY
WHERE DATETIME BETWEEN trunc(sysdate)-1
and trunc(sysdate) and
MOID IN

('2.3.1.1.401','2.4.1.1.411',
'2.3.3.1.402','2.4.3.1.412',
'2.5.1.1.403','2.6.1.1.413',
'2.5.3.1.404','2.6.3.1.414',
'2.7.1.1.405','2.8.1 .1.415',
'2.7.3.1.406','2.8.3.1.416',
'1.6.1.1.407','1.7.1.1.417',
'1.6.3.1.408','1.7.3.1.418')


GROUP BY TRUNC(DATETIME,'HH24'), FRPVCRXBYTES, MOID

order by moid, datetime
Re: getting the difference... [message #418511 is a reply to message #418479] Sun, 16 August 2009 20:07 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
>CREATE TABLE SGSN_FRAMERELAY_HR
>FROM ERICSSON_GPRS.SGSN_FRAMERELAY

Why do you post DDL for SGSN_FRAMERELAY_HR and SELECT * FROM SGSN_FRAMERELAY?




Re: getting the difference... [message #418525 is a reply to message #418479] Mon, 17 August 2009 00:49 Go to previous messageGo to next message
shajju
Messages: 29
Registered: August 2009
Junior Member
Sorry but that's the query I'm using....I'm kind of new to SQL so if you could kindly walk me through what you need, I'll be very grateful.
Re: getting the difference... [message #418526 is a reply to message #418479] Mon, 17 August 2009 00:51 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
>..I'm kind of new to SQL so if you could kindly walk me through what you need, I'll be very grateful.

I do not need anything.
Re: getting the difference... [message #418529 is a reply to message #418525] Mon, 17 August 2009 01:14 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
shajju wrote on Mon, 17 August 2009 07:49
Sorry but that's the query I'm using....I'm kind of new to SQL so if you could kindly walk me through what you need, I'll be very grateful.

Michel Cadot wrote on Sun, 16 August 2009 13:23
...

Post a working Test case: create table and insert statements along with the result you want with these data.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel


Getting the difference of 2 rows [message #421288 is a reply to message #418479] Mon, 07 September 2009 05:41 Go to previous messageGo to next message
shajju
Messages: 29
Registered: August 2009
Junior Member
Hi

I'm trying to get the difference of two rows out of 4 identified by OM-OM_225 and OM-OM_20

DATETIME DEST CALLS_ANSWERED CALL_ATTEMPTS
CALLING_MINS

9/7/2009 1:00:00 PM OM-PSTN 7372 95388 13309.17
9/7/2009 1:00:00 PM OM-OM_225 33104 101003 68570.83
9/7/2009 1:00:00 PM OM-INT 10417 64670 87971.67
9/7/2009 1:00:00 PM OM-OM_20 96767 231884 184952.5

Can anyone help me use the LAG/LEAD functions or any other way?

Thanks


Re: Getting the difference of 2 rows [message #421289 is a reply to message #421288] Mon, 07 September 2009 05:43 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
difference in terms of what exactly?
Re: getting the difference... [message #421292 is a reply to message #418500] Mon, 07 September 2009 05:52 Go to previous message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Sun, 16 August 2009 19:22
Quote:
(sorry again for no formatting):

You have no excuse when it has been asked more than 3 times and you stay in the same behaviour.

Michel Cadot wrote on Sun, 16 August 2009 13:23
...
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Also always post your Oracle version with 4 decimals.

Regards
Michel




Previous Topic: Issue in Bulk Collect
Next Topic: TOP FOUR SALARIES OF AN EMPL0YEE
Goto Forum:
  


Current Time: Mon Sep 26 21:29:10 CDT 2016

Total time taken to generate the page: 0.11595 seconds