ramu143
Yak Posting Veteran
64 Posts |
Posted - 2008-07-08 : 06:33:27
|
drop table #t1,#t2,#t3,#t4,#t5go SELECT operatorout,sitecode 'terdomain',COUNT(*)'totcalls' into #t1 from cdx(nolock)where calldd=26 and machineid in ('C22','F15','H95','c13','c21','E77','H99','H21','H92','c25','c27','J10','H15','C24','E64','H16','E80','E72','F81','F91','F14','F83','F85','F86','F90','F17','H93','H96','H22','C06','C19','H20','J07','E71','H12','E73','E68','F88','J02','C14','C08','J09','C09','C07')and substring(sitecode,2,1) like 'b%'and operatorout not in ('lyc_ger','lyc_ita','LYC_OUT','LYC_OUT1','L_TALK','cv_ger','cv_ita','cv_spa','ws_vtsp','cv_den','cv_swe','ws_sil','lyc_spa','che_c','che_f','che_r','che_s','qdial','walb_out','WS_LUC7','ws_rad','flg_out1','WS_COMM','wa_lyc_3')group by operatorout,sitecodego SELECT operatorout,sitecode 'terdomain',COUNT(*)'succalls' into #t2 from cdx(nolock)where calldd=26 and connectflag=1 and machineid in ('C22','F15','H95','c13','c21','E77','H99','H21','H92','c25','c27','J10','H15','C24','E64','H16','E80','E72','F81','F91','F14','F83','F85','F86','F90','F17','H93','H96','H22','C06','C19','H20','J07','E71','H12','E73','E68','F88','J02','C14','C08','J09','C09','C07')and substring(sitecode,2,1) like 'b%'and operatorout not in ('lyc_ger','lyc_ita','LYC_OUT','LYC_OUT1','L_TALK','cv_ger','cv_ita','cv_spa','ws_vtsp','cv_den','cv_swe','ws_sil','lyc_spa','che_c','che_f','che_r','che_s','qdial','walb_out','WS_LUC7','ws_rad','flg_out1','WS_COMM','wa_lyc_3') group by operatorout,sitecodego alter table #t1 add succalls int, failcalls int,oprrejct int,rlsreject int goupdate aset a.succalls=b.succallsfrom #t1 a, #t2 bwhere a.operatorout=b.operatorout and a.terdomain=b.terdomaingo SELECT operatorout,sitecode 'terdomain',COUNT(*)'failcalls' into #t3 from cdx(nolock)where calldd=26 and connectflag=0 and machineid in ('C22','F15','H95','c13','c21','E77','H99','H21','H92','c25','c27','J10','H15','C24','E64','H16','E80','E72','F81','F91','F14','F83','F85','F86','F90','F17','H93','H96','H22','C06','C19','H20','J07','E71','H12','E73','E68','F88','J02','C14','C08','J09','C09','C07')and substring(sitecode,2,1) like 'b%'and operatorout not in ('lyc_ger','lyc_ita','LYC_OUT','LYC_OUT1','L_TALK','cv_ger','cv_ita','cv_spa','ws_vtsp','cv_den','cv_swe','ws_sil','lyc_spa','che_c','che_f','che_r','che_s','qdial','walb_out','WS_LUC7','ws_rad','flg_out1','WS_COMM','wa_lyc_3')group by operatorout,sitecodego update aset a.failcalls=b.failcallsfrom #t1 a, #t3 bwhere a.operatorout=b.operatorout and a.terdomain=b.terdomaingo SELECT operatorout,sitecode 'terdomain',COUNT(*)'oprrejct' into #t4 from cdx(nolock)where calldd=26 and connectflag=0 and machineid in ('C22','F15','H95','c13','c21','E77','H99','H21','H92','c25','c27','J10','H15','C24','E64','H16','E80','E72','F81','F91','F14','F83','F85','F86','F90','F17','H93','H96','H22','C06','C19','H20','J07','E71','H12','E73','E68','F88','J02','C14','C08','J09','C09','C07')and (devbname like 'dac%' or devbname like 'pipe%') and substring(sitecode,2,1) like 'b%'and operatorout not in ('lyc_ger','lyc_ita','LYC_OUT','LYC_OUT1','L_TALK','cv_ger','cv_ita','cv_spa','ws_vtsp','cv_den','cv_swe','ws_sil','lyc_spa','che_c','che_f','che_r','che_s','qdial','walb_out','WS_LUC7','ws_rad','flg_out1','WS_COMM','wa_lyc_3')group by operatorout,sitecodego update aset a.oprrejct=b.oprrejctfrom #t1 a, #t4 bwhere a.operatorout=b.operatorout and a.terdomain=b.terdomain goSELECT operatorout,sitecode 'terdomain',COUNT(*)'rlsreject' into #t5 from cdx(nolock)where calldd=26 and connectflag=0 and machineid in ('C22','F15','H95','c13','c21','E77','H99','H21','H92','c25','c27','J10','H15','C24','E64','H16','E80','E72','F81','F91','F14','F83','F85','F86','F90','F17','H93','H96','H22','C06','C19','H20','J07','E71','H12','E73','E68','F88','J02','C14','C08','J09','C09','C07')and cast(dbo.GetReservedbValue(4,reservedb,':') as int)in (4,-4) and substring(sitecode,2,1) ='b'and operatorout not in ('lyc_ger','lyc_ita','LYC_OUT','LYC_OUT1','L_TALK','cv_ger','cv_ita','cv_spa','ws_vtsp','cv_den','cv_swe','ws_sil','lyc_spa','che_c','che_f','che_r','che_s','qdial','walb_out','WS_LUC7','ws_rad','flg_out1','WS_COMM','wa_lyc_3')group by operatorout,sitecodego update aset a.rlsreject=b.rlsrejectfrom #t1 a, #t5 bwhere a.operatorout=b.operatorout and a.terdomain=b.terdomain select * from #t1 |
|