select count(charegeable_duration),  sum ( case when length(charegeable_duration) = 6 then

to_number((substr(charegeable_duration,1,2)*60)*60) + to_number(substr(charegeable_duration,3,2)*60) + to_number(substr(charegeable_duration,5,2))

when length(charegeable_duration) = 5 then

to_number((substr(charegeable_duration,1,1)*60)*60) + to_number(substr(charegeable_duration,2,2)*60) + to_number(substr(charegeable_duration,4,2))

when length(charegeable_duration) = 4 then

to_number(substr(charegeable_duration,1,2)*60) + to_number(substr(charegeable_duration,3,2))

when length(charegeable_duration) = 3 then

to_number(substr(charegeable_duration,1,1)*60) + to_number(substr(charegeable_duration,2,2))

when length(charegeable_duration) = 2 then

to_number(substr(charegeable_duration,1,2))

when length(charegeable_duration) = 1 then

to_number(substr(charegeable_duration,1,1))

else 0

end ) seconds, substr(incoming_rout,1,3),date_start_charge
 
FROM MSC_SEPTEMBER_2008

     WHERE

    (INCOMING_ROUT like 'AWCC%'
AND (calling_party_num not like '11009370%' AND calling_party_num not like '11070%')
AND ( calling_party_num not like '12070%' AND calling_party_num not like '13070%' AND calling_party_num not like '14070%')
AND  ( calling_party_num not like '1170%' AND calling_party_num not like '1270%' AND calling_party_num not like '1370%')
AND (calling_party_num not like '1470%' AND calling_party_num not like '129370%' AND calling_party_num not like '139370%')
AND  ( calling_party_num not like '119370%' AND calling_party_num not like '149370%' AND calling_party_num not like '12009370%')
AND (calling_party_num not like '13009370%' AND calling_party_num not like '14009370%' AND calling_party_num not like '12010070%')
AND (calling_party_num not like '13010070%' AND calling_party_num not like '14010070%' AND calling_party_num not like '11010070%')
AND (calling_party_num not like '1201070%' AND calling_party_num not like '1301070%' AND calling_party_num not like '1401070%')
AND (calling_party_num not like '1101070%' AND calling_party_num not like '12010009370%' AND calling_party_num not like '13010009370%')
AND (calling_party_num not like '14010009370%' AND calling_party_num not like '11010009370%' AND calling_party_num not like '120109370%')
AND (calling_party_num not like '110109370%' AND calling_party_num not like '130109370%' AND calling_party_num not like '140109370%' )
AND (call_type = 'transit'))

OR
         (INCOMING_ROUT like 'ROSH%' AND
  (calling_party_num not like '11009379%' AND calling_party_num not like '11079%')
 AND ( calling_party_num not like '12079%' AND calling_party_num not like '13079%' AND calling_party_num not like '14079%')
 AND ( calling_party_num not like '1179%' AND calling_party_num not like '1279%' AND calling_party_num not like '1379%')
 AND (calling_party_num not like '1479%' AND calling_party_num not like '129379%' AND calling_party_num not like '139379%')
 AND ( calling_party_num not like '119379%' AND calling_party_num not like '149379%' AND calling_party_num not like '12009379%')
 AND (calling_party_num not like '13009379%' AND calling_party_num not like '14009379%' AND calling_party_num not like '12010079%' )
 AND (calling_party_num not like '13010079%' AND calling_party_num not like '14010079%' AND calling_party_num not like '11010079%')
 AND (calling_party_num not like '1201079%' AND calling_party_num not like '1301079%' AND calling_party_num not like '1401079%' )
 AND (calling_party_num not like '1101079%' AND calling_party_num not like '12010009379%' AND calling_party_num not like '13010009379%')
 AND (calling_party_num not like '14010009379%' AND calling_party_num not like '11010009379%' AND calling_party_num not like '120109379%')
 AND (calling_party_num not like '110109379%' AND calling_party_num not like '130109379%' AND calling_party_num not like '140109379%' )
 AND (call_type = 'transit'))
   
   
 OR
   
   (INCOMING_ROUT = 'AFTLI' AND
   (calling_party_num IS NULL) AND
   (call_type = 'transit'))
   or
   
   (INCOMING_ROUT like 'MED%'
 AND   call_type = 'transit')
or
           (INCOMING_ROUT like 'PTC%'
    AND call_type = 'transit')
   
    OR
   
   
   (INCOMING_ROUT like 'ETI%'
 AND (calling_party_num not like '11009378%' AND calling_party_num not like '11078%')
 AND ( calling_party_num not like '12078%' AND calling_party_num not like '13078%' AND calling_party_num not like '14078%')
 AND ( calling_party_num not like '1178%' AND calling_party_num not like '1278%' AND calling_party_num not like '1378%')
 AND (calling_party_num not like '1478%' AND calling_party_num not like '129378%' AND calling_party_num not like '139378%')
 AND ( calling_party_num not like '119378%' AND calling_party_num not like '149378%' AND calling_party_num not like '12009378%' )
 AND (calling_party_num not like '13009378%' AND calling_party_num not like '14009378%' AND calling_party_num not like '12010078%' )
 AND (calling_party_num not like '13010078%' AND calling_party_num not like '14010078%' AND calling_party_num not like '11010078%')
 AND (calling_party_num not like '1201078%' AND calling_party_num not like '1301078%' AND calling_party_num not like '1401078%' )
 AND (calling_party_num not like '1101078%' AND calling_party_num not like '12010009378%' AND calling_party_num not like '13010009378%')
 AND (calling_party_num not like '14010009378%' AND calling_party_num not like '11010009378%' AND calling_party_num not like '120109378%')
 AND (calling_party_num not like '110109378%' AND calling_party_num not like '130109378%' AND calling_party_num not like '140109378%' )
 AND (call_type = 'transit'))
   
    GROUP BY DATE_START_CHARGE,substr(incoming_rout,1,3)