Require modification to a dynamic query

Question

Hi,

I have this query running at specific hours of the day to provide us details about exceptions observed with call recording. Now our requirement is to report us only those exceptions which are associated with a call and not with voicemails or emails etc.. The column that holds the value as "call" is "MediaType" in a view "vwReserved". Now I want to join this view with the table with alias "d" on vwReserved.iInteractionID=d.iInteractionID with a condition where vwReserved.MediaType='call'. Could you please let me know how can I add this in to the existing query. I did try this myself but getting an error invalid column name 'call'.

declare @tablename1 varchar(20);
declare @tablename2 varchar(20);
declare @tablename3 varchar(20);
declare @tablename4 varchar(20);
declare @tablename5 varchar(20);
declare @number varchar(4)
set @number=(select MAX(vcSetNumber) from tblInteractionCatalog where GETDATE() between [dtMinStartTime] and [dtMaxStartTime])
set @tablename1='tblexception' + @number
set @tablename2='tblrecording' + @number
set @tablename3='tblinteraction' + @number
set @tablename4='tblparticipant' + @number
set @tablename5='tblcallinteraction' + @number
declare @alenzi nvarchar(max) = N'Select distinct a.iinteractionid,d.dtInteractionLocalStartTime,d.dtInteractionLocalstopTime,b.ilogger as LOGGER,
IC.nvcResourceHost as IC_SERVER, a.iExceptionTypeID as EXceptions,a.vcExceptionDetail as Exp_Detail,
par.nvcFormattedName, c.nvcStation as Station_ID, c.iSwitchID as Switch_ID, e.tiCallDirectionTypeID
from ' + @tablename1 + ' as a inner join ' + @tablename2 + ' as b on a.iinteractionid=b.iinteractionid
inner join ' + @tablename3 + ' as d on a.iInteractionID = d.iInteractionID
inner join ' + @tablename4 + ' as c on a.iinteractionid=c.iInteractionID
inner join nice_admin..tblUser as PAR on PAR.iUserId=c.iUserID
inner join nice_admin..tblSystemResource as IC on IC.iResourceId=d.siICID
inner join ' + @tablename5 + ' as e on e.iInteractionID = d.iInteractionID
where iexceptiontypeid=12 and d.dtInteractionLocalStartTime >= DATEADD (minute,-30, Getdate()) and e.tiCallDirectionTypeID >0
and c.iSwitchID >0 and b.tiRecordingRecordedTypeID in (0,2)
order by d.dtInteractionLocalStartTime desc'
print @alenzi
execute sp_executeSQL @alenzi ;

Answer 1

declare @tablename1 varchar(20);
declare @tablename2 varchar(20);
declare @tablename3 varchar(20);
declare @tablename4 varchar(20);
declare @tablename5 varchar(20);
declare @number varchar(4)
set @number=(select MAX(vcSetNumber) from tblInteractionCatalog where GETDATE() between [dtMinStartTime] and [dtMaxStartTime])
set @tablename1='tblexception' + @number
set @tablename2='tblrecording' + @number
set @tablename3='tblinteraction' + @number
set @tablename4='tblparticipant' + @number
set @tablename5='tblcallinteraction' + @number
declare @sql nvarchar(max) = N'Select distinct a.iinteractionid,d.dtInteractionLocalStartTime,d.dtInteractionLocalstopTime,b.ilogger as LOGGER,
IC.nvcResourceHost as IC_SERVER, a.iExceptionTypeID as EXceptions,a.vcExceptionDetail as Exp_Detail,
par.nvcFormattedName, c.nvcStation as Station_ID, c.iSwitchID as Switch_ID, e.tiCallDirectionTypeID
from ' + @tablename1 + ' as a inner join ' + @tablename2 + ' as b on a.iinteractionid=b.iinteractionid
inner join ' + @tablename3 + ' as d on a.iInteractionID = d.iInteractionID
inner join vwReserved as r ON r.iInteractionID=d.iInteractionID AND r.MediaType=''call''
inner join ' + @tablename4 + ' as c on a.iinteractionid=c.iInteractionID
inner join nice_admin..tblUser as PAR on PAR.iUserId=c.iUserID
inner join nice_admin..tblSystemResource as IC on IC.iResourceId=d.siICID
inner join ' + @tablename5 + ' as e on e.iInteractionID = d.iInteractionID
where iexceptiontypeid=12 and d.dtInteractionLocalStartTime >= DATEADD (minute,-30, Getdate()) and e.tiCallDirectionTypeID >0
and c.iSwitchID >0 and b.tiRecordingRecordedTypeID in (0,2)
order by d.dtInteractionLocalStartTime desc'
print @sql
execute sp_executeSQL @sql;

Answer 2

Hi @Debadas Nayak ,

I'm glad you got help in the community. If you have any questions, you are welcome to come to the community for help.

Regards
Echo