create stored procedure for this below query pleas

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

create stored procedure for this below query pleas

Author

Topic

ramu143
Yak Posting Veteran

64 Posts

Posted - 2008-07-08 : 06:32:41

drop table #t1,#t2,#t3,#t4,#t5

go

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,sitecode

go

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,sitecode

go

alter table #t1 add succalls int, failcalls int,oprrejct int,rlsreject int

go

update a

set a.succalls=b.succalls

from #t1 a, #t2 b

where a.operatorout=b.operatorout and a.terdomain=b.terdomain

go

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,sitecode

go

update a

set a.failcalls=b.failcalls

from #t1 a, #t3 b

where a.operatorout=b.operatorout and a.terdomain=b.terdomain

go

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,sitecode

go

update a

set a.oprrejct=b.oprrejct

from #t1 a, #t4 b

where a.operatorout=b.operatorout and a.terdomain=b.terdomain

go

SELECT 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,sitecode

go

update a

set a.rlsreject=b.rlsreject

from #t1 a, #t5 b

where a.operatorout=b.operatorout and a.terdomain=b.terdomain

select * from #t1

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-08 : 06:36:35

dup... (and wrong forum

)

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=106196

Em

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources