SQL.txt

select   o_bsc.name

   ,c.cell_id

   ,to_char(a.period_start_time,'yyyymmddhh24') s_time

   ,decode(SUM(a.SDCCH_ASSIGN+SDCCH_HO_SEIZ),0,0,100*SUM(SDCCH_RADIO_FAIL+SDCCH_RF_OLD_HO+SDCCH_USER_ACT+SDCCH_BCSU_RESET

   +SDCCH_NETW_ACT+SDCCH_ABIS_FAIL_CALL+SDCCH_ABIS_FAIL_OLD+SDCCH_BTS_FAIL+SDCCH_LAPD_FAIL+SDCCH_A_IF_FAIL_CALL

   +SDCCH_A_IF_FAIL_OLD)/SUM(a.SDCCH_ASSIGN+SDCCH_HO_SEIZ)) SD_dorp_rate

   ,sum(a.SDCCH_ASSIGN)

   ,sum(SDCCH_SEIZ_ATT)

   ,sum(SDCCH_BUSY_ATT)

   ,sum(T3101_EXPIRED)

   ,sum(SDCCH_RADIO_FAIL)

   ,sum(SDCCH_RF_OLD_HO)

   ,sum(SDCCH_USER_ACT)

   ,sum(SDCCH_BCSU_RESET)

   ,sum(SDCCH_NETW_ACT)

   ,sum(SDCCH_ABIS_FAIL_CALL)

   ,sum(SDCCH_ABIS_FAIL_OLD)

   ,sum(SDCCH_BTS_FAIL)

   ,sum(SDCCH_LAPD_FAIL)

   ,sum(SDCCH_A_IF_FAIL_CALL)

   ,sum(SDCCH_A_IF_FAIL_OLD)

   ,sum(sdcch_ho_seiz)

from p_nbsc_traffic a,p_nbsc_service b,objects o_bsc,objects o_bcf,objects o_bts,c_bts c

where o_bts.parent_int_id=o_bcf.int_id and o_bcf.parent_int_id=o_bsc.int_id

        and a.bts_int_id=o_bts.int_id and a.bts_int_id=c.int_id

        and b.bts_int_id=o_bts.int_id and b.bts_int_id=c.int_id

        and a.int_id=b.int_id 

        and a.int_id=o_bsc.int_id

        and a.bts_int_id=b.bts_int_id

        and c.int_id=o_bts.int_id

        and o_bsc.name like 'BSC6%'

        and c.cell_id in ('11','12')

        and a.period_start_time=b.period_start_time

        and to_char(a.period_start_time,'yyyymmddhh24')>= &start_date

        and to_char(a.period_start_time,'yyyymmddhh24')<  &end_date

group by 

        o_bsc.name,c.cell_id,to_char(a.period_start_time,'yyyymmddhh24')