ALTER SESSION 
SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

CREATE table test_data
(
  caller VARCHAR2(15),
  friend VARCHAR2(15),
  call_type VARCHAR2(15),
  start_time DATE,
  duration VARCHAR2(15)
);

CREATE TABLE test_output
(
  caller VARCHAR2(15),
  friend VARCHAR2(15),
  call_no INTEGER,
  avg_sms_response_time VARCHAR2(15),
  avg_call_return_time VARCHAR2(15),
  direct_response INTEGER
);

CREATE OR REPLACE PROCEDURE avg_sms_response
IS

        sms_count_a INTEGER;
        sms_count_b INTEGER;
        sms_response_time_a NUMBER(10,4);
        sms_response_time_b NUMBER(10,4);
        avg_sms_response_time_a NUMBER(10,4);
        avg_sms_response_time_b NUMBER(10,4);
		
		call_count_a INTEGER;
		call_count_b INTEGER;
               
		caller_a VARCHAR2(15) := '12345';
		caller_b VARCHAR2(15) := '54321';
			   
        counter INTEGER;
        row_no INTEGER;
        
        start_time_temp DATE;

        CURSOR c1 IS 
        SELECT caller, friend, start_time, call_type FROM test_data 
        WHERE(caller = caller_a and friend = caller_b) OR (caller = caller_b and friend = caller_a)  
        ORDER BY start_time;
		        
		start_time_rec c1%ROWTYPE;
		
		
			--SELECT COUNT(*) AS sms_count_a FROM test_data 
			--WHERE call_type = 'SMS' AND caller = '0851234567' and friend = '0857654321';
        
			--SELECT COUNT(*) AS sms_count_b FROM test_data 
			--WHERE call_type = 'SMS' AND caller = '0857654321' and friend = '0851234567';

BEGIN

        OPEN c1;
		FETCH c1 INTO start_time_rec;
		
        counter := 0;
        SELECT COUNT(*) INTO row_no FROM test_data 
        WHERE call_type = 'SMS' AND (caller = caller_a and friend = caller_b) OR (caller = caller_b and friend = caller_a) ;

        
        WHILE counter <= row_no
        LOOP
				
			IF start_time_rec.call_type = 'SMS' THEN
				
				IF start_time_rec.caller = caller_a THEN
					
					sms_response_time_a := sms_response_time_a + to_number(start_time_rec.start_time - start_time_temp);
					start_time_temp := start_time_rec.start_time;
					sms_count_a := sms_count_a + 1;
            
				ELSIF start_time_rec.caller = caller_b THEN
						sms_count_b := sms_count_b + 1;
            
				END IF;
			END IF;
						
			IF start_time_rec.call_type = 'VOICE' THEN
				
				IF start_time_rec.caller = caller_a THEN
					call_count_a := call_count_a + 1;
				ELSIF start_time_rec.caller = caller_b THEN
					call_count_b := call_count_b + 1;
						
				END IF;
			END IF;
            			
			counter := counter + 1;		
		
		
        --UPDATE  test_output
        --SET avg_sms_response_time = avg_sms_response_time_a WHERE caller = caller_a and friend = caller_b;
                
        --avg_sms_response_time_b :=  sms_response_time_b / sms_count_b;
                
        --UPDATE  test_output
        --SET avg_sms_response_time = avg_sms_response_time_a WHERE caller = caller_b and friend = caller_a;

                

        EXIT WHEN c1%NOTFOUND;
        COMMIT;
        END LOOP;
		
		avg_sms_response_time_a :=  sms_response_time_a / sms_count_a;
		insert into test_output values (caller_a, caller_b, call_count_a, avg_sms_response_time_a, '', '');
		
        CLOSE c1;

END avg_sms_response;