I Have This this Query
---------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[spVoIPCalldetailMeraPartitioned]
@userId int=null, -- filter: user id. overrides custid when supplied
@custId int=null, -- filter: customer id
@vcsId int=null, -- filter: status
@vdsId int=null, -- filter: destination id
@beginDt datetime=null, -- filter: begin date of call
@endDt datetime=null, -- filter: end date of call
@srchFor nvarchar(100)=null, -- filter: searches all result fields for given text
@sortBy varchar(200)=null, -- order by comma-seperated sort fields
@qryMode int=1, -- 0=details 1=grandtotal 2=accountcode 3=DialCode 4:carrier 5=Country+Breakout 6=callmonth 7=calldate 8=dayofweek 9=hour 10=vendor 11=Country 12=Country & Mobile Carrier
@detailMode int=null, -- one of above qryMode values for which detail is requested
@detailFilter nvarchar(500)=null, -- grouping filter for detail view
@vendor varchar(10)=null, -- grouping filter for detail view
@vendorUserId varchar(10)=null, -- grouping filter for detail view
@systemid varchar(3)=null -- grouping filter for detail view
as
-- set local variables
declare @q varchar(4200) -- query
declare @f varchar(3100) -- filter
declare @s varchar(250) -- search string
declare @c nvarchar(2) set @c=char(13) -- carriage return
declare @b nvarchar(25) -- begin date
declare @e nvarchar(25) -- end date
declare @dd nvarchar(100) set @dd='Sunday Monday Tuesday Wednesday Thursday Friday Saturday'
declare @mm nvarchar(100) set @mm='January February March April May June July August September October November December'
declare @t nvarchar(900) -- filter name for summary
declare @g nvarchar(600) -- group name for summary
declare @g1 nvarchar(15) set @g1='''All Calls'''
declare @g2 nvarchar(25) set @g2='cdr.cdrAccountCode'
declare @g3 nvarchar(200) set @g3='isNull(ds.vdsName + isnull('' - ''+ds.vdsType,'''') + isnull('' - ''+ds.vdsMobileCarrier,'''') + isnull('' - ''+ds.vdsDescription,'''') + '' - '' + ds.vdsDialcode, ''Unknown - System Default'')'
declare @g4 nvarchar(55) set @g4='isnull(ds.vdsDescription,''Unknown'')'
declare @g5 nvarchar(200) set @g5=' isNull(ds.vdsName + isnull('' - ''+ds.vdsType,'''') + isnull('' - ''+ds.vdsMobileCarrier,'''') + isnull('' - ''+ds.vdsDescription,''''), ''Unknown - System Default'') '
declare @g6 nvarchar(95) set @g6=' mm.value + '', '' + right(year(cdr.cdrCallDate),4)'
declare @g7 nvarchar(250) set @g7=' ltrim(year(cdr.cdrCallDate))+right(''0''+ltrim(day (mm.idx+1)),2)+right(''0''+ltrim(day(cdr.cdrCallDa te)),2)+'' - '' + mm.value + '' '' + right(''0''+ltrim(day(cdr.cdrCallDate)),2) + '', '' + ltrim(year(cdr.cdrCallDate)) '
declare @g8 nvarchar(85) set @g8=' ltrim(dd.idx+1)+'' - ''+dd.value '
declare @g9 nvarchar(250) set @g9=' CONVERT(datetime, (CONVERT(varchar, datepart(mm,cdr.cdrCallDate)) + ''/'' + CONVERT(varchar, datepart(dd,cdr.cdrCallDate)) + ''/'' + CONVERT(varchar, datepart(yyyy,cdr.cdrCallDate)))) , datepart(hh,cdr.cdrCallDate) '
declare @g10 nvarchar(350) set @g10=' ISNULL(gateways.[description], ISNULL(cdr.vrUserName, ''Unknown - System Default'' )) '
declare @g11 nvarchar(200) set @g11='isNull(ds.vdsName + isnull('' - ''+ds.vdsType,''''), ''Unknown - System Default'') '
declare @g12 nvarchar(200) set @g12='isNull(ds.vdsName + isnull('' - ''+ds.vdsType,'''') + isnull('' - ''+ds.vdsMobileCarrier,''''), ''Unknown - System Default'') '
print @systemid
print @sortBy
-- fix qryMode & detailMode
if @qryMode<0 or @qryMode>12 set @qryMode=1
if @detailMode<0 or @detailMode>12 set @detailMode=null
-- fix requested IDs
if @userId>0 set @custId=null if @custId>0 set @userId=null
-- fix requested dates
if len(@beginDt)>0 or len(@endDt)>0
begin
set @beginDt=isnull(@beginDt,'jan 01, 2000') set @endDt=isnull(@endDt,getdate()+1)
if @beginDt>@endDt set @endDt=@beginDt
set @b=cast(month(@beginDt) as varchar)+'/'+cast(day(@beginDt) as varchar)+'/'+cast(year(@beginDt) as varchar)+' '+cast(datepart(hh,@beginDt) as varchar)+':'+cast(datepart(n,@beginDt) as varchar)+':00.01'
set @e=cast(month(@endDt) as varchar)+'/'+cast(day(@endDt) as varchar)+'/'+cast(year(@endDt) as varchar)+' '+cast(datepart(hh,@endDt) as varchar)+':'+cast(datepart(n,@endDt) as varchar)+':59.59' -- construct date filter
set @f='where cdr.cdrCallDate between '''+@b+''' and '''+@e+''' '+@c
end
-- fix requested search strings
set @s=replace(replace(replace(ltrim(rtrim(@srchFor)), '''',''''''),'_','[_]'),'%','[%]')
if len(@detailFilter)>0 set @detailFilter=replace(replace(replace(ltrim(rtrim( @detailFilter)),'''',''''''),'_','_'),'%','[%]')
-- assign data grouping based on request mode
if @qryMode=0
begin
if @detailMode=1 begin set @t=@g1 end
else if @detailMode=2 begin set @t=@g2 end
else if @detailMode=3 begin set @t=@g3 end
else if @detailMode=4 begin set @t=@g4 end
else if @detailMode=5 begin set @t=@g5 end
else if @detailMode=6 begin set @t=@g6 end
else if @detailMode=7 begin set @t=@g7 end
else if @detailMode=8 begin set @t=@g8 end
else if @detailMode=9 begin set @t= ' CONVERT(datetime, (CONVERT(varchar, datepart(mm,cdr.cdrCallDate)) + ''/'' + CONVERT(varchar, datepart(dd,cdr.cdrCallDate)) + ''/'' + CONVERT(varchar, datepart(yyyy,cdr.cdrCallDate))) + '' '' + convert(varchar,datepart(hh,cdr.cdrCallDate)) + '':00'') >= convert(datetime, '''+@detailFilter+''' + '':00'') AND CONVERT(datetime, (CONVERT(varchar, datepart(mm,cdr.cdrCallDate)) + ''/'' + CONVERT(varchar, datepart(dd,cdr.cdrCallDate)) + ''/'' + CONVERT(varchar, datepart(yyyy,cdr.cdrCallDate))) + '' '' + convert(varchar,datepart(hh,cdr.cdrCallDate)) + '':00'') <= convert(datetime, '''+@detailFilter+''' + '':59:59'') ' end
else if @detailMode=10 begin set @t=@g10 end
else if @detailMode=11 begin set @t=@g11 end
else if @detailMode=12 begin set @t=@g12 end
end
else if @qryMode=1 begin set @g=@g1 end
else if @qryMode=2 begin set @g=@g2 end
else if @qryMode=3 begin set @g=@g3 end
else if @qryMode=4 begin set @g=@g4 end
else if @qryMode=5 begin set @g=@g5 end
else if @qryMode=6 begin set @g=@g6 end
else if @qryMode=7 begin set @g=@g7 end
else if @qryMode=8 begin set @g=@g8 end
else if @qryMode=9 begin set @g=@g9 end
else if @qryMode=10 begin set @g=@g10 end
else if @qryMode=11 begin set @g=@g11 end
else if @qryMode=12 begin set @g=@g12 end
-- construct search filter
if len(@s)>0
begin
if len(@f)>0 set @f=@f +' and ' else set @f='where '
set @f=@f +' (u.userFN like ''%'+@s+'%'' or u.userLN like ''%'+@s+'%'' or u.userUnixName like ''%'+@s+'%'' '+@c
set @f=@f +' or cdr.cdrSrcNum like ''%'+@s+'%'' or cdr.cdrSrcChannel like ''%'+@s+'%'' or cdr.cdrDstNum like ''%'+@s+'%'' '+@c
set @f=@f +' or ''1007'' like ''%'+@s+'%'' or ds.vdsName like ''%'+@s+'%'' or s.vcsName like ''%'+@s+'%'' )'+@c
end
-- construct detail filter
if @qryMode=0 and @detailMode>0 and @detailMode<=12 and len(@detailFilter)>0
begin
if len(@f)>0 set @f=@f +' and ' else set @f='where '
if @detailMode=3 set @f=@f +'cdr.vdsId = '''+@detailFilter+''' ' +@c
else set @f=@f +@t +' = '''+@detailFilter+''' ' +@c
end
else if @qryMode=0 and @detailMode=9 and len(@detailFilter)>0
begin
if len(@f)>0 set @f=@f +' and ' else set @f='where '
if @detailMode=3 set @f=@f +'cdr.vdsId = '''+@detailFilter+''' ' +@c
else set @f=@f +@t +@c
end
-- get fields from calls table
set @q='select '+@c
if @qryMode=0 -- detail mode
begin
set @q=@q +'c.custId, u.userId, u.userUnixName, u.userUnixName AS callSummaryName, u.userFN+'' ''+left(u.userLN,1)+''.'' userShortname, '+@c
set @q=@q +'cdr.cdrCallDate AS vcdStartDt, cdr.cdrSrcNum, cdr.cdrSrcChannel, cdr.cdrAccountCode, cdr.cdrDstChannel, ''1007'' As vcdDstPrefix, cdr.cdrDstNum, isNull(ds.vdsName + isnull('' - ''+ds.vdsType,'''') + isnull('' - ''+ds.vdsMobileCarrier,'''') + isnull('' - ''+ds.vdsDescription,'''') , ''Unknown - System Default'') AS vdsName, cdr.cdrCallGK, cdr.vrUserName, '+@c
set @q=@q + ' (CONVERT(varchar, datepart(mm,cdr.cdrCallDate)) + ''/'' + CONVERT(varchar, datepart(dd,cdr.cdrCallDate)) + ''/'' + CONVERT(varchar, datepart(yyyy,cdr.cdrCallDate))) AS CallDate, '
set @q=@q +'s.vcsName, s.vcsColor, '+@c
set @q=@q +' q.dcqId, q.dcqDescription, q.dcqNote, '+@c
set @q=@q +'cdr.cdrBillSec, ''0'' AS callRate, ''0'' AS callAdjRate, ''0'' AS callCost, cdr.vdsId AS vdsId, cdr.meraRouteRetries, cdr.meraPDDTime '+@c
end -- summary modes: add specific output fields
else if @qryMode=3 begin set @q=@q +' cdr.vdsId, ' end
if @qryMode>0 -- summary modes: add counts
begin
set @q=@q + @g +' callSummaryName, '+@c
set @q=@q +'min(cdr.cdrCallDate) callMinDt, '+@c
set @q=@q +'max(cdr.cdrCallDate) callMaxDt, '+@c
set @q=@q +'count(*) callTotalCount, '+@c
set @q=@q + ' min((CONVERT(varchar, datepart(mm,cdr.cdrCallDate)) + ''/'' + CONVERT(varchar, datepart(dd,cdr.cdrCallDate)) + ''/'' + CONVERT(varchar, datepart(yyyy,cdr.cdrCallDate)))) AS CallDate, '
set @q=@q +'isnull(sum(case cdr.vcsId when 2 then 1 else 0 end),0) callTotalAnswered, '+@c
set @q=@q +'isnull(sum(case cdr.vcsId when 2 then 1 else 0 end),0)*100/(case count(*) when 0 then 1 else count(*) end) callTotalASR, '+@c
set @q=@q +'isnull(sum(cdr.cdrBillSec),0)/(case isnull(sum(case cdr.vcsId when 2 then 1 else 0 end),0) when 0 then 1 else isnull(sum(case cdr.vcsId when 2 then 1 else 0 end),0) end) callTotalACD, '+@c
set @q=@q +'isnull(sum(cdrBillSec),0) callTotalSec, '+@c
set @q=@q +' AVG(CAST(IsNull(cdr1.NumberOfRetries, 0) AS Numeric)) averageRouteRetries, '+@c
set @q=@q +' AVG(IsNull(cdr.meraPDDTime, 0)) averagePDD, '+@c
set @q=@q + ' isnull(sum(CASE WHEN (cdr.cdrBillSec = 0) THEN (0) WHEN isnull(cdr.cdrBillSec,0) <= (6) THEN (6) ELSE ((( CASE WHEN (isnull(cdr.cdrBillSec,0)%(6))=0 then (isnull(cdr.cdrBillSec,0)%(6)) Else 6-(isnull(cdr.cdrBillSec,0)%(6)) End))+(isnull(cdr.cdrBillSec, 0)))END),0) callTotalFinal, ' + @c
set @q=@q + ' isnull(sum(CASE WHEN (cdr.cdrBillSec = 0) THEN (0) WHEN isnull(cdr.cdrBillSec,0) <= (30) THEN (30) ELSE ((( CASE WHEN (isnull(cdr.cdrBillSec,0)%(6))=0 then (isnull(cdr.cdrBillSec,0)%(6)) Else 6-(isnull(cdr.cdrBillSec,0)%(6)) End ))+(isnull(cdr.cdrBillSec,0)))END),0) callTotalFinal2,' + @c
set @q=@q + ' isnull(sum(CASE WHEN (cdr.cdrBillSec = 0) THEN (0) WHEN isnull(cdr.cdrBillSec,0) <= (30) THEN (30) ELSE isnull(cdr.cdrBillSec,0) END),0) callTotalFinal3, ' + @c
set @q=@q + ' isnull(sum(CASE WHEN (cdr.cdrBillSec = 0) THEN (0) WHEN isnull(cdr.cdrBillSec,0) <= (60) THEN (60) ELSE ((( CASE WHEN (isnull(cdr.cdrBillSec,0)%(60))=0 then (isnull(cdr.cdrBillSec,0)%(60)) Else 60-(isnull(cdr.cdrBillSec,0)%(60)) End))+(isnull(cdr.cdrBillSec, 0)))END),0) callTotalFinal4, ' + @c
set @q=@q +' 0 AS callTotalCost , MAX(cdr.vdsId) AS vdsId, '+@c
set @q=@q + ' SUM(case meraDisconnectCodeQ931 when 16 then
1 else 0 end) AS NCC,
SUM(case meraDisconnectCodeQ931 when 1 then
1
when 28 then
1 else 0 end) AS WN,
SUM(case meraDisconnectCodeQ931
when 34 then
1
when 17 then
1 else 0 end) AS Busy,
SUM(case
when (meraDisconnectCodeQ931<>1) AND (meraDisconnectCodeQ931<>28) AND (meraDisconnectCodeQ931<>34) AND (meraDisconnectCodeQ931<>17) AND (meraDisconnectCodeQ931<>16) then
1 else 0 end) AS Other '
end
-- get cdr table
If @qryMode=0
begin
set @q=@q +' from tblVoIPCDRs cdr WITH (NOLOCK) left join tblUser u on (cdr.cdrUserName=u.userUnixName) left JOIN tblCustomer c on (u.CustId = c.CustId) Left join X1Customer x on (c.x1CustId = x.Id) left Join tblVoIPDestination ds on (cdr.vdsId = ds.vdsId) left join tblVoIPCallStatus s on (cdr.vcsId=s.vcsId) left outer join tblVoIPDisconnectCodeQ931 q on (cdr.meraDisconnectCodeQ931 = q.dcqId) left join tblUser uu on (cdr.vrUserName=uu.userUnixName) left JOIN tblCustomer cc on (uu.CustId = cc.CustId) ' + @c
end
Else
begin
set @q=@q +' from (Select MAX(cddr.cdrNativeId) AS cdrNativeId, count(*) AS NumberOfRetries from tblVoIPCDRs cddr where cddr.cdrCallDate between '''+@b+''' and '''+@e+''' group by cddr.meraRecordId) AS cdr1 join tblvoipcdrs AS cdr WITH (NOLOCK) on (cdr1.cdrNativeId = cdr.cdrNativeId) left join tblUser u on (cdr.cdrUserName=u.userUnixName) left JOIN tblCustomer c on (u.CustId = c.CustId) Left join X1Customer x on (c.x1CustId = x.Id) left Join tblVoIPDestination ds on (cdr.vdsId = ds.vdsId) left join tblVoIPCallStatus s on (cdr.vcsId=s.vcsId) left join tblUser uu on (cdr.vrUserName=uu.userUnixName) left JOIN tblCustomer cc on (uu.CustId = cc.CustId) ' + @c
end
if @qryMode=10 OR @detailMode=10
begin
set @q=@q +' left outer JOIN (SELECT * FROM openquery(routing, ''SELECT * FROM gateways'') ) gateways ON gateways.[Name] = (case when patindex(''sip/%'',lower(cdr.cdrDstChannel)) > 0 then (substring(cdr.cdrDstChannel, 5, (len(cdr.cdrDstChannel)-4) - patindex(''%-%'',reverse(cdr.cdrDstChannel)))) end) ' +@c
end
--if @qryMode=10 begin set @q=@q + ' join tblCustomer cust on (vv.custID = c.custId) join X1Customer x1c on (c.x1CustId = x.ID) '+@c end
-- join user table
--set @q=@q +'join tblUser u on u.userId=cd.userId '
-- join status, destination tables
--set @q=@q +'join tblVoIPCallstatus cs on cs.vcsId=cd.vcsId '
if @vcsId>0
begin
if len(@f)>0 set @f=@f +' and ' else set @f='where '
set @f=@f +' cdr.vcsId='+cast(@vcsId as varchar)+' '+@c
end
else
begin
set @f=@f +@c
end
--if @userId>0 set @q=@q +'and u.userId='+cast(@userId as varchar)+' '
--if @custId>0 set @q=@q +'and u.custId='+cast(@custId as varchar)+' '+@c else set @q=@q +@c
if @userId>0
begin
if len(@f)>0 set @f=@f +' and ' else set @f='where '
set @f=@f +' u.userId='+cast(@userId as varchar)+' '+@c
end
else
begin
set @f=@f +@c
end
if @custId>0
begin
if len(@f)>0 set @f=@f +' and ' else set @f='where '
set @f=@f +' c.custId='+cast(@custId as varchar)+' '+@c
end
else
begin
set @f=@f +@c
end
--set @q=@q +' join tblVoIPRate vr on ((vr.vtrID = cd.vtrID) AND (vr.vvdId = cd.vvdId) AND (vr.vdsID = cd.vdsID)) ' --- Join voipRATE table
--set @q=@q + ' join tblVoIPBillingStructure vbs on (vbs.vbsID = vr.vbsID) ' --- Join voipBillingStructure table
if @vdsId>0
begin
if len(@f)>0 set @f=@f +' and ' else set @f='where '
set @f=@f +' ds.vdsId='+cast(@vdsId as varchar)+' '+@c
end
else
begin
set @f=@f +@c
end
if len(@f)>0 set @f=@f +' and ' else set @f='where '
if @systemid='1' set @f=@f +' ( '
set @f=@f +' uu.systemid='''+(@systemid)+''' '+@c
if @systemid='1' set @f=@f +' OR uu.systemid IS NULL ) '+@c
if len(@f)>0 set @f=@f +' and ' else set @f='where '
set @f=@f +' cdrActive=1'+@c
if len(@vendor)>1
begin
if len(@f)>0 set @f=@f +' and ' else set @f='where '
set @f=@f +' cc.x1CustId='''+(@vendor)+''' '+@c
end
if len(@vendorUserId)>1
begin
if len(@f)>0 set @f=@f +' and ' else set @f='where '
set @f=@f +' uu.userId='''+(@vendorUserId)+''' '+@c
end
-- join month and day soft tables
--if @qryMode=6 OR @qryMode=7 OR @qryMode=8 OR @qryMode=9
--Begin
set @q=@q +'join fnSplit('''+@mm+''','' '') mm on mm.idx=month(cdr.cdrCallDate)-1 ' +@c
set @q=@q +'join fnSplit('''+@dd+''','' '') dd on dd.idx=datepart(dw,cdr.cdrCallDate)-1 ' +@c
--End
-- add filter
if len(@f)>0 set @q=@q +@f
-- add grouping for summary modes
if @qryMode=1 set @q=@q +'' +@c
else if @qryMode=3 begin set @q=@q +'group by cdr.vdsId, '+@g+' ' +@c end
else if @qryMode in (2,4,5,6,7,8,9,10,11,12) begin set @q=@q +'group by '+@g+' ' +@c end
-- add sorting to detail mode
if len(@sortBy)>0 and @qryMode<>1 set @q=@q +'order by ' +@sortBy +@c
-- exec
print @q
exec(@q)
could any one tell me what the ouput of this query
and i want to optimized this query by removing joins and make Temp Table that hold all required data