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
 can u please create stored procedure for below que

Author  Topic 

ramu143
Yak Posting Veteran

64 Posts

Posted - 2008-07-08 : 06:33:27
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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-08 : 06:42:29
Have a look at CREATE PROCEDURE syntax

http://msdn.microsoft.com/en-us/library/ms187926.aspx
Go to Top of Page
   

- Advertisement -