Scribd
Upload
35 views8 pages

Network Performance Metrics Analysis

This document contains a SELECT statement that retrieves metrics related to the performance of an RNC (Radio Network Controller) including KPIs like accessibility, retainability, traffic, volume and availability for both CS (Circuit Switched) and PS (Packet Switched) domains over different time periods. The metrics are calculated from collected PM (Performance Management) counter values.

Uploaded by

Marcos Dominguez
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
35 views8 pages

Network Performance Metrics Analysis

This document contains a SELECT statement that retrieves metrics related to the performance of an RNC (Radio Network Controller) including KPIs like accessibility, retainability, traffic, volume and availability for both CS (Circuit Switched) and PS (Packet Switched) domains over different time periods. The metrics are calculated from collected PM (Performance Management) counter values.

Uploaded by

Marcos Dominguez
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

SELECT

DATE_ID,
HOUR_ID,
--BHTYPE,
--BUSYHOUR,
RNC,
Utrancell,
RncFunction,
N_Cells=count(DISTINCT Utrancell),
N_ROP=count(DISTINCT MIN_ID),

--Accesibilidad
CSSR_CS=convert(decimal(5,4),RRC_SUCC_CS*NAS_SUCC_CS*RAB_SUCC_CS),
CSSR_PS=convert(decimal(5,4),RRC_SUCC_PS*NAS_SUCC_PS*RAB_SUCC_PS),
CS_ACC_FAILS=CS_RRC_DEN-CS_RRC_NUM+CS_NAS_DEN-CS_NAS_NUM+CS_RAB_DEN-CS_RAB_NUM,
PS_ACC_FAILS=PS_RRC_DEN-PS_RRC_NUM+PS_NAS_DEN-PS_NAS_NUM+PS_RAB_DEN-PS_RAB_NUM,
RRC_SUCC_CS=convert(decimal(5,4),CS_RRC_NUM/CS_RRC_DEN),
RRC_SUCC_PS=convert(decimal(5,4),PS_RRC_NUM/PS_RRC_DEN),
RAB_SUCC_CS=convert(decimal(5,4),CS_RAB_NUM/CS_RAB_DEN),
RAB_SUCC_PS=convert(decimal(5,4),PS_RAB_NUM/PS_RAB_DEN),
NAS_SUCC_CS=convert(decimal(5,4),CS_NAS_NUM/CS_NAS_DEN),
NAS_SUCC_PS=convert(decimal(5,4),PS_NAS_NUM/PS_NAS_DEN),
CS_RRC_NUM=Sum(pmTotNoRrcConnectReqCsSucc),
CS_RRC_DEN=Sum(pmTotNoRrcConnectReqCs),
-- CS_RRC_DEN=Sum(pmTotNoRrcConnectReqCs-pmNoLoadSharingRrcConnCs),
CS_RAB_NUM=Sum(pmNoRabEstablishSuccessSpeech),
CS_RAB_DEN=Sum(pmNoRabEstablishAttemptSpeech),
--CS_RAB_DEN=Sum(pmNoRabEstablishAttemptSpeech-pmNoDirRetryAtt),
PS_RRC_NUM=Sum(pmTotNoRrcConnectReqPsSucc),
PS_RRC_DEN=Sum(pmTotNoRrcConnectReqPs-pmNoLoadSharingRrcConnPs),
PS_RAB_NUM=Sum(pmNoRabEstablishSuccessPacketInteractive),
PS_RAB_DEN=Sum(pmNoRabEstablishAttemptPacketInteractive),
CS_NAS_NUM=sum(pmNoNormalNasSignReleaseCs),
CS_NAS_DEN=sum(pmNoNormalNasSignReleaseCs+pmNoSystemNasSignReleaseCs),
PS_NAS_NUM=sum(pmNoNormalNasSignReleasePs),
PS_NAS_DEN=sum(pmNoNormalNasSignReleasePs+pmNoSystemNasSignReleasePs),

PS_HS_RABSUCCRATE=convert(decimal(5,4),sum(pmNoRabEstablishSuccessPacketInteractive
Hs)/sum(pmNoRabEstablishAttemptPacketInteractiveHs)),
HS_RAB_ATT=sum(pmNoRabEstablishAttemptPacketInteractiveHs),

PS_EUL_RABSUCCRATE=convert(decimal(5,4),sum(pmNoRabEstablishSuccessPacketInteractiv
eEul)/sum(pmNoRabEstablishAttemptPacketInteractiveEul)),
EUL_RAB_ATT=sum(pmNoRabEstablishAttemptPacketInteractiveEul),

--Retenibilidad
CS_DROP=convert(decimal(5,4),CS_DROP_NUM/CS_DROP_DEN),
CS_DROP_NUM=Sum(pmNoSystemRabReleaseSpeech),
CS_DROP_DEN=Sum(pmNoNormalRabReleaseSpeech+pmNoSystemRabReleaseSpeech),
PS_DROP=convert(decimal(5,4),PS_DROP_NUM/PS_DROP_DEN),
PS_DROP_NUM=Sum(pmNoSystemRabReleasePacket-pmNoSystemRabReleasePacketUra-
(pmChSwitchAttemptFachUra-pmChSwitchSuccFachUra)-
(pmChSwitchAttemptDchUra-pmChSwitchSuccDchUra)-(pmChSwitchAttemptHsUra-
pmChSwitchSuccHsUra)),
PS_DROP_DEN=Sum(pmNoNormalRabReleasePacket-
pmNoNormalRabReleasePacketUra+pmNoSystemRabReleasePacket-
pmNoSystemRabReleasePacketUra+
pmChSwitchSuccFachUra+pmChSwitchSuccDchUra+pmChSwitchSuccHsUra),
--Accesibilidad y Retenibilidad
CCSR_CS=convert(decimal(5,4),CSSR_CS*(1-CS_DROP)), --Call Completion Success Rate
(Accesibilidad x Retenibilidad)
CCSR_PS=convert(decimal(5,4),CSSR_PS*(1-PS_DROP)), --Call Completion Success Rate
(Accesibilidad x Retenibilidad)

--Trafico Primario
ERL_SPEECH=convert(decimal(15,3),sum((
pmSumBestCs12Establish/pmSamplesBestCs12Establish
+pmSumBestAmr4750RabEstablish/pmSamplesBestAmr4750RabEstablish
+pmSumBestAmr5900RabEstablish/pmSamplesBestAmr5900RabEstablish
+pmSumBestAmr7950RabEstablish/pmSamplesBestAmr7950RabEstablish
+pmSumBestAmr12200RabEstablish/pmSamplesBestAmr12200RabEstablish
+pmSumBestAmrNbMmRabEstablish/pmSamplesBestAmrNbMmRabEstablish
+pmSumBestAmrWbRabEstablish/pmSamplesBestAmrWbRabEstablish
)*convert(decimal(15,5),period_duration)/60)),
ERL_CS64=convert(decimal(15,3),sum((
pmSumBestCs64RabEstablish/pmSamplesBestCs64RabEstablish
)*convert(decimal(15,5),period_duration)/60)),
ERL_HS=convert(decimal(15,3),sum((
pmSumBestPsHsAdchRabEstablish/pmSamplesBestPsHsAdchRabEstablish
+pmSumBestPsEulRabEstablish/pmSamplesBestPsEulRabEstablish
)*convert(decimal(15,5),period_duration)/60)),

ERL_EUL=convert(decimal(15,3),sum((pmSumBestPsEulRabEstablish/pmSamplesBestPsEulRab
Establish)*convert(decimal(15,10),period_duration)/60)),

ERL_R99=convert(decimal(15,3),sum((pmSumBestDchPsIntRabEstablish/pmSamplesFachPsInt
RabEstablish)*convert(decimal(15,10),period_duration)/60)),

ERL_FACH=convert(decimal(15,3),sum((pmSumFachPsIntRabEstablish/pmSamplesFachPsIntRa
bEstablish)*convert(decimal(15,10),period_duration)/60)),

--Tr�fico Total
AVGNUMCONN_SPEECH=convert(decimal(15,3),sum((
pmSumCs12RabEstablish/pmSamplesCs12RabEstablish
+pmSumAmr4750RabEstablish/pmSamplesAmr4750RabEstablish
+pmSumAmr5900RabEstablish/pmSamplesAmr5900RabEstablish
+pmSumAmr7950RabEstablish/pmSamplesAmr7950RabEstablish
+pmSumAmr12200RabEstablish/pmSamplesAmr12200RabEstablish
+pmSumAmrNbMmRabEstablish/pmSamplesAmrNbMmRabEstablish
+pmSumAmrWbRabEstablish/pmSamplesAmrWbRabEstablish
)*convert(decimal(15,5),period_duration)/60)),
AVGNUMCONN_HS=convert(decimal(15,3),sum((
pmSumPsHsAdchRabEstablish/pmSamplesPsHsAdchRabEstablish
+pmSumPsEulRabEstablish/pmSamplesPsEulRabEstablish
)*convert(decimal(15,3),period_duration)/60)),

AVGNUMCONN_EUL=convert(decimal(15,5),sum((pmSumPsEulRabEstablish/pmSamplesPsEulRabE
stablish)*convert(decimal(15,10),period_duration)/60)),

AVGNUMCONN_R99=convert(decimal(15,5),sum((pmSumPsInteractive/pmSamplesPsInteractive
)*convert(decimal(15,10),period_duration)/60)),
AVGNUMCONN_CS64=convert(decimal(15,3),sum((
pmSumCs64RabEstablish/pmSamplesCs64RabEstablish
)*convert(decimal(15,5),period_duration)/60)),

--Volumen de datos
PAYLOAD_DL_MB=convert(decimal(15,5),Sum(
pmDlTrafficVolumePsCommon
+pmDlTrafficVolumePs64
+pmDlTrafficVolumePs128
+pmDlTrafficVolumePs384
+pmDlTrafficVolumePs8
+pmDlTrafficVolumePs16
+pmDlTrafficVolumePsIntHs
)/8000),
PAYLOAD_UL_MB=convert(decimal(15,5),Sum(
pmUlTrafficVolumePs384
+pmUlTrafficVolumePs8
+pmUlTrafficVolumePs16
+pmUlTrafficVolumePsCommon
+pmUlTrafficVolumePs64
+pmUlTrafficVolumePs128
+pmUlTrafficVolumePsIntEul
)/8000),
PAYLOAD_DL_HS_MB=convert(decimal(15,5),Sum(
pmDlTrafficVolumePsIntHs
)/8000),
PAYLOAD_UL_EUL_MB=convert(decimal(15,5),Sum(
pmUlTrafficVolumePsIntEul
)/8000),
PAYLOAD_DL_R99_MB=convert(decimal(15,5),Sum(
+pmDlTrafficVolumePs64
+pmDlTrafficVolumePs128
+pmDlTrafficVolumePs384
+pmDlTrafficVolumePs8
+pmDlTrafficVolumePs16
)/8000),
PAYLOAD_UL_R99_MB=convert(decimal(15,5),Sum(
pmUlTrafficVolumePs384
+pmUlTrafficVolumePs8
+pmUlTrafficVolumePs16
+pmUlTrafficVolumePs64
+pmUlTrafficVolumePs128
)/8000),

--FACH Traffic
Avg_FACH_TRAFFICVOLUME_Kbps=convert(decimal(15,5),sum(pmDlTrafficVolumePsCommon)/
(60*sum(period_duration))),
Avg_RACH_TRAFFICVOLUME_Kbps=convert(decimal(15,5),sum(pmUlTrafficVolumePsCommon)/
(60*sum(period_duration))),
FACH_TRAFFICVOLUME_MB=convert(decimal(15,5),sum(pmDlTrafficVolumePsCommon)/8000),
RACH_TRAFFICVOLUME_MB=convert(decimal(15,5),sum(pmUlTrafficVolumePsCommon)/8000),
--Disponibilidad

DOWNTIME_COMB=convert(decimal(5,4),sum(pmCellDowntimeAuto+pmCellDowntimeMan)/sum(pe
riod_duration*60)),

DOWNTIME_AUTO=convert(decimal(5,4),sum(pmCellDowntimeAuto)/sum(period_duration*60))
,
DOWNTIME_MAN=convert(decimal(5,4),sum(pmCellDowntimeMan)/sum(period_duration*60)),
DOWNTIME_AUTO_Seconds=convert(decimal(28,0),sum(pmCellDowntimeAuto)),
DOWNTIME_MAN_Seconds=convert(decimal(28,0),sum(pmCellDowntimeMan)),
DOWNTIME_Measurement_Time=sum(convert(decimal(28,0),period_duration)*60),
--Bloqueos RRC
RRC_REQDEN_CODE=convert(decimal(15,0),Sum(pmNoRrcReqDeniedAdmDlChnlCode)),
RRC_REQDEN_DLHW=convert(decimal(15,0),Sum(pmNoRrcReqDeniedAdmDlHw)),
RRC_REQDEN_DLPWR=convert(decimal(15,0),Sum(pmNoRrcReqDeniedAdmDlPwr)),
RRC_REQDEN_ULHW=convert(decimal(15,0),Sum(pmNoRrcReqDeniedAdmUlHw)),

RRC_BLOCK_NODE=convert(decimal(15,0),Sum(pmNoRrcConnReqBlockNodeCs+pmNoRrcConnReqBl
ockNodePs)),

RRC_BLOCK_TN=convert(decimal(15,0),Sum(pmNoRrcConnReqBlockTnCs+pmNoRrcConnReqBlockT
nPs)),
RRC_REJ_MPLOAD=convert(decimal(15,0),Sum(pmNoRejRrcConnMpLoadC)),
--Bloqueos RAB
RAB_REQDEN_ULASE=convert(decimal(15,0),Sum(pmNoFailedRabEstAttemptLackUlAse)),
RAB_REQDEN_DLASE=convert(decimal(15,0),Sum(pmNoFailedRabEstAttemptLackDlAse)),
RAB_REQDEN_CODE=convert(decimal(15,0),Sum(pmNoFailedRabEstAttemptLackDlChnlCode)),
RAB_REQDEN_DLPOWER=convert(decimal(15,0),Sum(pmNoFailedRabEstAttemptLackDlPwr)),

RAB_REQDEN_SFHYST=convert(decimal(15,0),Sum(pmNoFailedRabEstAttemptExceedConnLimit)
),
RAB_REQDEN_ULHW=convert(decimal(15,0),Sum(pmNoFailedRabEstAttemptLackUlHwBest)),
RAB_REQDEN_DLHW=convert(decimal(15,0),Sum(pmNoFailedRabEstAttemptLackDlHwBest)),
RAB_BLOCK_NODE=
convert(decimal(15,0),Sum(pmNoRabEstBlockNodeSpeechBest+pmNoRabEstBlockNodeCs57Best
+pmNoRabEstBlockNodeCs64Best+pmNoRabEstBlkNodePsIntNonHsBest
+pmNoRabEstBlkNodePsStrNonHsBest+pmNoRabEstBlockNodePsIntHsBest
+pmNoRabEstBlockNodePsStrHsBest)),
RAB_BLOCK_TN=convert(decimal(15,0),
Sum(pmNoRabEstBlockTnSpeech+pmNoRabEstBlockTnCs57+pmNoRabEstBlockTnCs64
+pmNoRabEstBlockTnPsIntNonHs+pmNoRabEstBlockTnPsStrNonHs
+pmNoRabEstBlockTnPsIntHs+pmNoRabEstBlockTnPsStrHs)),
--Bloqueos/Fallas RRC+RAB
REQDEN_ADM=convert(decimal(15,0),Sum(pmNoReqDeniedAdm)),
FAILED_AFTERADM=convert(decimal(15,0),Sum(pmNoFailedAfterAdm)),

NONHO_REQDEN_SPEECH_RATE=convert(decimal(5,4),sum(pmNoOfNonHoReqDeniedSpeech)/sum(p
mNoRabEstablishAttemptSpeech)),
NONHO_REQDEN_SPEECH_EVENTS=sum(pmNoOfNonHoReqDeniedSpeech),

NONHO_REQDEN_PS_NON_HS_RATE=convert(decimal(5,4),sum(pmNoOfNonHoReqDeniedInteractiv
e)/sum(pmNoRabEstablishAttemptPacketInteractive)),
NONHO_REQDEN_PS_NON_HS_EVENTS=sum(pmNoOfNonHoReqDeniedInteractive),

NONHO_REQDEN_HS_RATE=convert(decimal(5,4),sum(pmNoOfNonHoReqDeniedHs)/sum(pmNoRabEs
tablishAttemptPacketInteractiveHs)),
NONHO_REQDEN_HS_EVENTS=sum(pmNoOfNonHoReqDeniedHs),

NONHO_REQDEN_EUL_RATE=convert(decimal(5,4),sum(pmNoOfNonHoReqDeniedEul)/sum(pmNoRab
EstablishAttemptPacketInteractiveEul)),
NONHO_REQDEN_EUL_EVENTS=sum(pmNoOfNonHoReqDeniedEul),
EUL_SC_ADM_REJ=sum(pmNoServingCellReqDeniedEul),
EUL_SC_ADM_REJ_2MS=sum(pmNoServingCellReqDeniedEulTti2),
--SHO

SHO_SUCC_TARGET=convert(decimal(5,4),Sum(pmNoTimesRlAddToActSet)/Sum(pmNoTimesRlAdd
ToActSet+pmNoTimesCellFailAddToActSet)),

SHO_SUCC_SOURCE=convert(decimal(5,4),Sum(pmRlAddSuccessBestCellSpeech)/Sum(pmRlAddA
ttemptsBestCellSpeech)),
RL_DEN_ADM=convert(decimal(15,0),Sum(pmNoRlDeniedAdm)),
--IFHO / IFLS
IfhoRlAddToActSet=Sum(pmNoTimesIfhoRlAddToActSet),
IF_HO_FAIL=convert(decimal(5,4),Sum(pmNoTimesIfhoCellFailAddToActSet)/Sum(pmNoTimes
IfhoRlAddToActSet+pmNoTimesIfhoCellFailAddToActSet)),
LoadSharingRrcConnCs=convert(decimal(15,0),Sum(pmNoLoadSharingRrcConnCs)),
LoadSharingRrcConnPs=convert(decimal(15,0),Sum(pmNoLoadSharingRrcConnPs)),
LoadSharingRrcConn=convert(decimal(15,0),sum(pmNoLoadSharingRrcConn)),

IFLS_ACTIVITY_RATE=convert(decimal(5,4),Sum(pmNoLoadSharingRrcConn)/Sum(pmTotNoRrcC
onnectReq)),

IFLS_ACTIVITY_PS=convert(decimal(5,4),Sum(pmNoLoadSharingRrcConnCs)/Sum(pmTotNoRrcC
onnectReqCs)),

IFLS_ACTIVITY_CS=convert(decimal(5,4),Sum(pmNoLoadSharingRrcConnPs)/Sum(pmTotNoRrcC
onnectReqPs)),

IFLS_RETURN_RATE=convert(decimal(5,4),sum(pmNoOfReturningRrcConn)/sum(pmNoLoadShari
ngRrcConn)),
--Causas Speech Drop
SPEECH_DROPS_SHO=convert(decimal(15,0),Sum(pmNoSysRelSpeechSoHo)-
Sum(pmNoSysRelSpeechNeighbr)),
SPEECH_DROPS_ULSYNC=convert(decimal(15,0),Sum(pmNoSysRelSpeechUlSynch)),
SPEECH_DROPS_CONG=convert(decimal(15,0),Sum(pmNoOfTermSpeechCong)),
SPEECH_DROPS_NEIGHB=convert(decimal(15,0),Sum(pmNoSysRelSpeechNeighbr)),
SPEECH_DROPS_OTHER= convert(decimal(15,0),Sum(pmNoSystemRabReleaseSpeech)-
Sum(pmNoSysRelSpeechSoHo)-Sum(pmNoSysRelSpeechUlSynch)-Sum(pmNoOfTermSpeechCong)),
--Este no incluye Drops por IRAT
--RSSI
RSSI_dBm=convert(decimal(8,3),-112+(Sum(pmSumUlRssi)/Sum(pmSamplesUlRssi))/10),
--Directed Retry

DR_ACTIVITY=convert(decimal(5,4),sum(pmNoDirRetryAtt)/sum(pmNoRabEstablishAttemptSp
eech)),
DR_SUCC_RATE=convert(decimal(5,4),sum(pmNoDirRetrySuccess)/sum(pmNoDirRetryAtt)),
DR_ATT=sum(pmNoDirRetryAtt),
DR_SUCC=sum(pmNoDirRetrySuccess),
--HSPA Mobility
HS_CC_SUCC_RATE=convert(decimal(5,4),sum(pmNoHsCcSuccess)/Sum(pmNoHsCcAttempt)),
HS_CC_SUCC=Sum(pmNoHsCcSuccess),
HS_CC_ATT=Sum(pmNoHsCcAttempt),

EUL_CC_SUCC_RATE=convert(decimal(5,4),sum(pmNoEulCcSuccess)/Sum(pmNoEulCcAttempt)),
EUL_CC_SUCC=Sum(pmNoEulCcSuccess),
EUL_CC_ATT=Sum(pmNoEulCcAttempt),
--Cell Update

CELLUPD_SUCC_RATE=convert(decimal(5,4),sum(pmNoCellUpdSuccess)/sum(pmNoCellUpdAttem
pt)),
CELLUPD_ATT=sum(pmNoCellUpdAttempt),
--URA Update

URA_UPD_SUCC_RATE=convert(decimal(5,4),sum(pmNoUraUpdSuccess)/sum(pmNoUraUpdAttempt
)),
URA_UPD_ATT=sum(pmNoUraUpdAttempt),
--PS Drop (RAB)
DROPRATE_PS_NON_HS=convert(decimal(5,4),sum(pmNoSystemRabReleasePacket-
pmNoSystemRbReleaseHs-pmNoSystemRabReleasePacketUra)/
sum(pmNoNormalRabReleasePacket-pmNoNormalRabReleasePacketUra-
pmNoNormalRbReleaseHs
+pmNoSystemRabReleasePacket-pmNoSystemRabReleasePacketUra-
pmNoSystemRbReleaseHs

+pmUpSwitchFachHsSuccess+pmNoSuccRbReconfOrigPsIntDch+pmChSwitchSuccFachUra)),
DROPRATE_PS_HS=convert(decimal(5,4),sum(pmNoSystemRbReleaseHs)/

sum(pmNoNormalRbReleaseHs+pmNoSystemRbReleaseHs+pmNoSuccRbReconfPsIntDch+pmPsIntHsT
oFachSucc)),
DROPRATE_PS_EUL=convert(decimal(5,4),sum(pmNoSystemRbReleaseEul)/

sum(pmNoNormalRbReleaseEul+pmNoSystemRbReleaseEul+pmNoSuccRbReconfOrigPsIntEul+pmPs
IntHsToFachSucc)),
SystemRbReleaseHs=sum(pmNoSystemRbReleaseHs),
SystemRbReleaseEul=sum(pmNoSystemRbReleaseEul),
NormalRbReleaseHs=sum(pmNoNormalRbReleaseHs),
NoNormalRbReleaseEul=sum(pmNoNormalRbReleaseEul),
SuccRbReconfPsIntDch=sum(pmNoSuccRbReconfPsIntDch),
PsIntHsToFachSucc=sum(pmPsIntHsToFachSucc),
SystemRabReleasePacket=sum(pmNoSystemRabReleasePacket),
SystemRabReleasePacketUra=sum(pmNoSystemRabReleasePacketUra),
--Drops RRC

RRC_DROPRATE_PS=convert(decimal(5,4),sum(pmNoPacketDchDiscAbnorm)/sum(pmNoPacketDch
DiscNormal+pmNoPacketDchDiscAbnorm)),
RRC_DROPS_PS=sum(pmNoPacketDchDiscAbnorm),

RRC_DROPRATE_FACH=convert(decimal(5,4),sum(pmNoCellFachDisconnectAbnorm)/sum(pmNoCe
llFachDisconnectAbnorm+pmNoCellFachDisconnectNormal)),
RRC_DROP_FACH=sum(pmNoCellFachDisconnectAbnorm),

RRC_DROPRATE_DCH=convert(decimal(5,4),sum(pmNoCellDchDisconnectAbnorm)/sum(pmNoCell
DchDisconnectAbnorm+pmNoCellDchDisconnectNormal)),
RRC_DROP_DCH=sum(pmNoCellDchDisconnectAbnorm),
--Channel Switching
SWITCH_FACHIDLE_COUNT=sum(pmChSwitchFachIdle),
UPSWITCH_FAILRATE_FACH_HS=convert(decimal(5,4),1-
sum(pmUpswitchFachHsSuccess)/sum(pmUpswitchFachHsAttempt)),
SWITCH_FAILRATE_FACH_URA=convert(decimal(5,4),1-
sum(pmChSwitchSuccFachUra)/sum(pmChSwitchAttemptFachUra)),
ChSwitchAttemptFachUra=sum(pmChSwitchAttemptFachUra),
SWITCH_FAILRATE_URA_FACH=convert(decimal(5,4),1-
sum(pmChSwitchSuccUraFach)/sum(pmChSwitchAttemptUraFach)),
ChSwitchAttemptUraFach=sum(pmChSwitchAttemptUraFach),
UPSWITCH_FAILRATE_DL_HS=convert(decimal(5,4),1-
sum(pmDlUpswitchSuccessHs)/sum(pmDlUpswitchAttemptHs)),
DlUpswitchAttemptHs=sum(pmDlUpswitchAttemptHs),
HS_DCH_FAIL_RATE=convert(decimal(5,4),1-
sum(pmHsToDchSuccess)/sum(pmHsToDchAttempt)),
HsToDchAttempt=sum(pmHsToDchAttempt),
UPSWITCH_FAILRATE_UL_EUL=convert(decimal(5,4),1-
sum(pmUlUpswitchSuccessEul)/sum(pmUlUpswitchAttemptEul)),
UlUpswitchAttemptEul=sum(pmUlUpswitchAttemptEul),
EUL_DCH_FAIL_RATE=convert(decimal(5,4),1-
sum(pmEulToDchSuccess)/sum(pmEulToDchAttempt)),
EulToDchAttempt=sum(pmEulToDchAttempt),
RECONF_FAILRATE_DCHFACH=convert(decimal(5,4),1-
sum(pmPsIntDchToFachSucc)/sum(pmPsIntDchToFachAtt)),
PsIntDchToFachAtt=sum(pmPsIntDchToFachAtt),
RECONF_FAILRATE_HSFACH=convert(decimal(5,4),1-
sum(pmPsIntHsToFachSucc)/sum(pmPsIntHsToFachAtt)),
PsIntHsToFachAtt=sum(pmPsIntHsToFachAtt),
--I-RAT Cell Reselection

IRATCR_RRCSUCCRATE=convert(decimal(5,4),sum(pmTotNoRrcConnectSuccessIratCellResel)/
sum(pmTotNoRrcConnectAttIratCellResel)),
IRATCR_ATT=sum(pmTotNoRrcConnectAttIratCellResel),

IRATCR_RRCFAILRATE_Congestion=convert(decimal(5,4),sum(pmTotNoRrcConnectFailCongIra
tCellResel)/sum(pmTotNoRrcConnectAttIratCellResel)),
IRATCR_FAILURES_CONG=sum(pmTotNoRrcConnectFailCongIratCellResel),
--ASE

DL_ASE_USAGE=convert(decimal(15,5),0.001*sum(pmSumOfSampAseDl)/sum(pmNoOfSampAseDl)
),

UL_ASE_USAGE=convert(decimal(15,5),0.001*sum(pmSumOfSampAseUl)/sum(pmNoOfSampAseUl)
)

FROM
--dc.DC_E_RAN_UCELL_DAYBH
dc.DC_E_RAN_UCELL_RAW
--dc.DC_E_RAN_UCELL_DAY

WHERE
--DATE_ID>=getdate()-7
--and (BHTYPE='CELL_PP3' or BHTYPE='CELL_PP4')
DATE_ID between '01-28-2016' and '02-16-2016'
--and HOUR_ID between 08 and 21
-- AND RNC LIKE 'RNC20MAR'

--AND Utrancell LIKE '3CON%'


--AND Utrancell LIKE '3SIG%'
--AND Utrancell LIKE '3SIV%'
--AND Utrancell LIKE '3SJA%'
--AND Utrancell LIKE '3VEL%'
AND (Utrancell like '3CON%' OR Utrancell like '3SIG%' OR Utrancell like '3SIV%' OR
Utrancell like '3SJA%' OR Utrancell like '3VEL%' )
--AND (Utrancell like '3CON%' OR Utrancell like '3SIG%' OR Utrancell like '3SIV%'
OR Utrancell like '3SJA%' OR Utrancell like '3VEL%' OR Utrancell like 'PQUEA%' OR
Utrancell like 'MYCEN%' OR Utrancell like 'AVBOL%' OR Utrancell like 'TERMI%' OR
Utrancell like 'CANAA%' OR Utrancell like 'LIMON%' OR Utrancell like 'UCVMC%' OR
Utrancell like 'BCOCO%' OR Utrancell like 'FCOMI%' OR Utrancell like 'RIOBL%' OR
Utrancell like 'STARI%' OR Utrancell like 'BARRA%' OR Utrancell like 'SJACI%' OR
Utrancell like 'AVARA%' OR Utrancell like 'HAMAC%' OR Utrancell like 'PINON%' OR
Utrancell like 'SAMAN%' OR Utrancell like 'SROSA%' OR Utrancell like 'SVICE%' OR
Utrancell like 'PALOD%' OR Utrancell like 'SCRUD%' OR Utrancell like 'UCMAR%')

GROUP BY
DATE_ID,
HOUR_ID,
RncFunction,
--BHTYPE,
--BUSYHOUR,
RNC,
Utrancell

ORDER BY
DATE_ID,
HOUR_ID,
--BHTYPE,
--BUSYHOUR,
-- RNC--,
Utrancell

You might also like