Home » SQL & PL/SQL » SQL & PL/SQL » Going through record set in oracle and doing calculations (Oracle)
Going through record set in oracle and doing calculations [message #574429] Thu, 10 January 2013 04:52 Go to next message
danuxz
Messages: 3
Registered: January 2013
Junior Member
Hi guys,

Im new to Oracle and I have this one task I have to do, but Im little stuck with it.

I have a set of records what looks similar like this

Caller Friend Call_type Start_time Duration
12345 54321 sms 1/JAN/2013 09:25:11 0
54321 12345 sms 1/JAN/2013 09:35:12 0
13579 97531 sms 1/JAN/2013 09:37:12 0
97531 13579 voice 1/JAN/2013 09:39:12 3:25
12345 54321 sms 1/JAN/2013 09:42:32 0
12345 54321 sms 1/JAN/2013 09:52:45 0
12345 11111 sms 1/JAN/2013 09:53:45 0
54321 12345 sms 1/JAN/2013 10:25:36 0
12345 54321 sms 1/JAN/2013 11:25:34 0
...

What i need to do is to go trough the record set and get output similar to this for every relationship

Caller Friend Call_no avg_sms_response_time
12345 54321 0 15.5min
54321 12345 0 0.5min
12345 11111 0 120.30min
...

The biggest problem at the moment I have is that I dont know how to go trough all record set having to read it in only once. And other is my calculations doesnt work and im not really sure why.

I have attached code as far as I have gotten.

Also this is the first time Im posting anything on forum, so please feel free to give an advice if I should do something different way.

Many Thanks

Report message to a moderator

Re: Going through record set in oracle and doing calculations [message #574432 is a reply to message #574429] Thu, 10 January 2013 05:17 Go to previous messageGo to next message
Michel Cadot
Messages: 54205
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel

Report message to a moderator

Re: Going through record set in oracle and doing calculations [message #574437 is a reply to message #574432] Thu, 10 January 2013 05:45 Go to previous messageGo to next message
danuxz
Messages: 3
Registered: January 2013
Junior Member
Thank you for advice Michel.

Im using
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

And I have attached some test data.

Report message to a moderator

Re: Going through record set in oracle and doing calculations [message #574440 is a reply to message #574437] Thu, 10 January 2013 05:51 Go to previous messageGo to next message
Michel Cadot
Messages: 54205
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 10 January 2013 12:17
...
With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel

Report message to a moderator

Re: Going through record set in oracle and doing calculations [message #574441 is a reply to message #574440] Thu, 10 January 2013 06:01 Go to previous messageGo to next message
danuxz
Messages: 3
Registered: January 2013
Junior Member
This is a sample output:

Caller  Friend  Call_no  avg_sms_response_time
12345   54321   0        15.5min 
54321   12345   0        0.5min
12345   11111   0        120.30min


Where a call_no is number of calls made within the data set
And average response time is average time it takes to responde to sms
average response time = total response time / total number of sms

So for example if a sends sms to b at 10:05 and b replys at 10:07 then b response time to a would be 2 minutes.

I hope this makes sense.

Regards,
D

Report message to a moderator

Re: Going through record set in oracle and doing calculations [message #574448 is a reply to message #574441] Thu, 10 January 2013 07:54 Go to previous messageGo to next message
Michel Cadot
Messages: 54205
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
This is a sample output:


I repeat once more:

Quote:
along with the result you want with these data then we will be able work with your table and data.


So give the result FOR THE DATE YOU POSTED.

Quote:
Where a call_no is number of calls made within the data set


How there can be an average if the call number is 0?
Define what is a "call" from your table definition.

Regards
Michel

Report message to a moderator

Re: Going through record set in oracle and doing calculations [message #574450 is a reply to message #574448] Thu, 10 January 2013 07:59 Go to previous messageGo to next message
Michel Cadot
Messages: 54205
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition post a VALID test case (valid for anyone):
SQL> ALTER SESSION 
  2  SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> 
SQL> CREATE table test_data
  2  (
  3    caller VARCHAR2(15),
  4    friend VARCHAR2(15),
  5    call_type VARCHAR2(15),
  6    start_time DATE,
  7    duration VARCHAR2(15)
  8  );

Table created.

SQL> INSERT INTO test_data (caller, friend, call_type, start_time, duration) VALUES ('0851234560', '0857654321', 'SMS', '1/JAN/2013 09:25:34', '0');
INSERT INTO test_data (caller, friend, call_type, start_time, duration) VALUES ('0851234560', '0857654321', 'SMS', '1/JAN/2013 09:25:34', '0')
                                                                                                                   *
ERROR at line 1:
ORA-01843: not a valid month

So ALWAYS use TO_DATE wit a format.
ALWAYS use numeric values and not names.

Regards
Michel

Report message to a moderator

Re: Going through record set in oracle and doing calculations [message #574451 is a reply to message #574450] Thu, 10 January 2013 08:04 Go to previous message
Michel Cadot
Messages: 54205
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Also specify is a SMS can be a reply to a VOICE, and vice-versa.
Also specify if a caller calls twice before a reply, does the reply is from the second or the first call?
If a caller say 1 calls 2 then 2 calls 1 and then 1 calls 2, does 1 a replier of 2?
There are many things that are not specified.
Please take time to think about ALL the cases and then come back with a clear and deterministic specification.

Regards
Michel

[Updated on: Thu, 10 January 2013 08:05]

Report message to a moderator

Previous Topic: Extracting part of a string
Next Topic: Index usage
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu May 23 07:10:45 CDT 2013

Total time taken to generate the page: 0.38681 seconds
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.