SQL & PL/SQL

I have the table below and data below ,

CREATE TABLE AAA_CDR
( ACCTTYPE NUMBER(1),MSID VARCHAR2(20 BYTE),ACCOUNTSESSION_ID VARCHAR2(64 BYTE), BEGINNINGSESSION NUMBER(1),
SESSIONCONTINUE NUMBER(1), ACCT_MULTI_SESSION_ID VARCHAR2(64 BYTE), DATAOCTETCOUNTORIGINATING NUMBER(20), DATAOCTETCOUNTTERMINATING NUMBER(20), ACCTSESSIONTIME NUMBER(20));

--SQL Statement which produced this data:
/* select ACCTTYPE,msid,accountsession_id,beginningsession,sessioncontinue,acct_multi_session_id,
dataoctetcountoriginating,dataoctetcountterminating,acctsessiontime
FROM AAA_CDR
where msid='5C4CA98EAC4C'
ORDER BY acct_multi_session_id,accountsession_id,accttype
*/

Insert into AAA_CDR (ACCTTYPE, MSID, ACCOUNTSESSION_ID, BEGINNINGSESSION, SESSIONCONTINUE, ACCT_MULTI_SESSION_ID, DATAOCTETCOUNTORIGINATING, DATAOCTETCOUNTTERMINATING, ACCTSESSIONTIME)
Values (1, '5C4CA98EAC4C', '190001E9', 1, 0, '100014E19775F169', 0, 0, 0);
Insert into AAA_CDR (ACCTTYPE, MSID, ACCOUNTSESSION_ID, BEGINNINGSESSION, SESSIONCONTINUE, ACCT_MULTI_SESSION_ID, DATAOCTETCOUNTORIGINATING, DATAOCTETCOUNTTERMINATING, ACCTSESSIONTIME)
Values (2, '5C4CA98EAC4C', '190001E9', 0, 0, '100014E19775F169', 47496, 182054, 70);
Insert into AAA_CDR (ACCTTYPE, MSID, ACCOUNTSESSION_ID, BEGINNINGSESSION, SESSIONCONTINUE, ACCT_MULTI_SESSION_ID, DATAOCTETCOUNTORIGINATING, DATAOCTETCOUNTTERMINATING, ACCTSESSIONTIME)
Values (1, '5C4CA98EAC4C', '290001C4', 1, 0, '100014E19E21F16C', 0, 0, 0);
Insert into AAA_CDR (ACCTTYPE, MSID, ACCOUNTSESSION_ID, BEGINNINGSESSION, SESSIONCONTINUE, ACCT_MULTI_SESSION_ID, DATAOCTETCOUNTORIGINATING, DATAOCTETCOUNTTERMINATING, ACCTSESSIONTIME)
Values (2, '5C4CA98EAC4C', '290001C4', 0, 0, '100014E19E21F16C', 3278162, 17540714, 14152);
Insert into AAA_CDR (ACCTTYPE, MSID, ACCOUNTSESSION_ID, BEGINNINGSESSION, SESSIONCONTINUE, ACCT_MULTI_SESSION_ID, DATAOCTETCOUNTORIGINATING, DATAOCTETCOUNTTERMINATING, ACCTSESSIONTIME)
Values (1, '5C4CA98EAC4C', '190001FC', 1, 0, '100014E1A949316E', 0, 0, 0);
Insert into AAA_CDR (ACCTTYPE, MSID, ACCOUNTSESSION_ID, BEGINNINGSESSION, SESSIONCONTINUE, ACCT_MULTI_SESSION_ID, DATAOCTETCOUNTORIGINATING, DATAOCTETCOUNTTERMINATING, ACCTSESSIONTIME)
Values (2, '5C4CA98EAC4C', '190001FC', 0, 1, '100014E1A949316E', 682734, 2935111, 1553);
Insert into AAA_CDR (ACCTTYPE, MSID, ACCOUNTSESSION_ID, BEGINNINGSESSION, SESSIONCONTINUE, ACCT_MULTI_SESSION_ID, DATAOCTETCOUNTORIGINATING, DATAOCTETCOUNTTERMINATING, ACCTSESSIONTIME)
Values (1, '5C4CA98EAC4C', '190001FD', 0, 0, '100014E1A949316E', 0, 0, 0);
Insert into AAA_CDR (ACCTTYPE, MSID, ACCOUNTSESSION_ID, BEGINNINGSESSION, SESSIONCONTINUE, ACCT_MULTI_SESSION_ID, DATAOCTETCOUNTORIGINATING, DATAOCTETCOUNTTERMINATING, ACCTSESSIONTIME)
Values (2, '5C4CA98EAC4C', '190001FD', 0, 1, '100014E1A949316E', 1821052, 9585090, 5368);
Insert into AAA_CDR (ACCTTYPE, MSID, ACCOUNTSESSION_ID, BEGINNINGSESSION, SESSIONCONTINUE, ACCT_MULTI_SESSION_ID, DATAOCTETCOUNTORIGINATING, DATAOCTETCOUNTTERMINATING, ACCTSESSIONTIME)
Values (1, '5C4CA98EAC4C', '190001FE', 0, 0, '100014E1A949316E', 0, 0, 0);
Insert into AAA_CDR (ACCTTYPE, MSID, ACCOUNTSESSION_ID, BEGINNINGSESSION, SESSIONCONTINUE, ACCT_MULTI_SESSION_ID, DATAOCTETCOUNTORIGINATING, DATAOCTETCOUNTTERMINATING, ACCTSESSIONTIME)
Values (2, '5C4CA98EAC4C', '190001FE', 0, 1, '100014E1A949316E', 42012, 505004, 213);
Insert into AAA_CDR (ACCTTYPE, MSID, ACCOUNTSESSION_ID, BEGINNINGSESSION, SESSIONCONTINUE, ACCT_MULTI_SESSION_ID, DATAOCTETCOUNTORIGINATING, DATAOCTETCOUNTTERMINATING, ACCTSESSIONTIME)
Values (1, '5C4CA98EAC4C', '19000200', 0, 0, '100014E1A949316E', 0, 0, 0);
Insert into AAA_CDR (ACCTTYPE, MSID, ACCOUNTSESSION_ID, BEGINNINGSESSION, SESSIONCONTINUE, ACCT_MULTI_SESSION_ID, DATAOCTETCOUNTORIGINATING, DATAOCTETCOUNTTERMINATING, ACCTSESSIONTIME)
Values (2, '5C4CA98EAC4C', '19000200', 0, 1, '100014E1A949316E', 50224, 144566, 729);
Insert into AAA_CDR (ACCTTYPE, MSID, ACCOUNTSESSION_ID, BEGINNINGSESSION, SESSIONCONTINUE, ACCT_MULTI_SESSION_ID, DATAOCTETCOUNTORIGINATING, DATAOCTETCOUNTTERMINATING, ACCTSESSIONTIME)
Values (1, '5C4CA98EAC4C', '19000201', 0, 0, '100014E1A949316E', 0, 0, 0);
Insert into AAA_CDR (ACCTTYPE, MSID, ACCOUNTSESSION_ID, BEGINNINGSESSION, SESSIONCONTINUE, ACCT_MULTI_SESSION_ID, DATAOCTETCOUNTORIGINATING, DATAOCTETCOUNTTERMINATING, ACCTSESSIONTIME)
Values (2, '5C4CA98EAC4C', '19000201', 0, 1, '100014E1A949316E', 62634, 378416, 392);
Insert into AAA_CDR (ACCTTYPE, MSID, ACCOUNTSESSION_ID, BEGINNINGSESSION, SESSIONCONTINUE, ACCT_MULTI_SESSION_ID, DATAOCTETCOUNTORIGINATING, DATAOCTETCOUNTTERMINATING, ACCTSESSIONTIME)
Values (1, '5C4CA98EAC4C', '19000202', 0, 0, '100014E1A949316E', 0, 0, 0);
Insert into AAA_CDR (ACCTTYPE, MSID, ACCOUNTSESSION_ID, BEGINNINGSESSION, SESSIONCONTINUE, ACCT_MULTI_SESSION_ID, DATAOCTETCOUNTORIGINATING, DATAOCTETCOUNTTERMINATING, ACCTSESSIONTIME)
Values (2, '5C4CA98EAC4C', '19000202', 0, 0, '100014E1A949316E', 1457286, 9141033, 7574);
commit;

I need to calculate sum(dataeoctetcountoriginating)+sum(dataoctetcountterminating)for each session
that's mean , the start of the session and the end of the session as below ,
for each ACCT_MULTI_SESSION_ID and MSID
the start of the session is when the accttype=1 and beginningsession=1
and end of the session is when the accttype=2 and SESSIONCONTINUE=0


1- Collect the first accounting start CDR that is generated during an online event of a user.
Collect the CDR whose AcctType is 1 and Beginning Session is 1 according to MSID. In
addition, record the value of the Acct-Multi-Session-ID field in the CDR.
2. Collect all the accounting stop CDRs that are generated during this online event of the user.
Collect the CDR whose AcctType is 2 according to MSID and Acct-Multi-Session-ID.
If the value of Session Continue in an accounting stop CDR is 0, it indicates that this CDR
is the last accounting stop CDR that is generated during the offline event of the user.
3. Calculate the total volume of the user during this online event.
Add the values of the Data Octet Count (Termination) fields and the values of the Data
Octet Count (Originating) fields in the collected accounting stop CDRs together to obtain
the total volume of this online event of the user.

how I can find each session then calculate the summation of this session ?
any help please

Edited by: user11309581 on Jul 13, 2011 10:01 PM