exec GetWorklogDetails @ExecutiveId='186,192',@Date='2020-05-11',@CompanyId='17',@ProductId='172,163'
Alter procedure GetWorklogDetails
(
@ExecutiveId nvarchar(max)=null
,@Date nvarchar(max)
--,@FromDate as date
--,@ToDate as date
,@CompanyId varchar(max)=null
,@ProductId varchar(max)=null
)
as
begin
Declare @sql as nvarchar(max)
set @sql='
select * from(
select ex.Id as id,cntct.Id as cid,prdct.id as pid
,tkt.TicketNumber
,ex.ExecutiveName ActionTakenBy
,actntaken.ActionDesc ActionTaken
,cast(tktintact.EntryDate as date) as Date
,cntct.Name Company
,prdctcatgry.CategoryName Product
from TicketInteractionAction tktintact
join TicketInteraction tktint
on tktint.Id=tktintact.TicketInteractionId
join LoginDetails ld
on ld.Id=tktintact.ActionTakenByID
join Executive ex
on ex.Id=ld.ExecutiveID
join Ticket tkt
on tkt.Id=tktint.TicketId
join Contact cntct
on cntct.Id=tkt.CompanyId and cntct.ContactType=1
join Product prdct
on prdct.Id=tkt.ProductId
join ProductCategory prdctcatgry
on prdctcatgry.Id=prdct.SubCategoryId
join Action actntaken
on actntaken.id=tktint.ActionTakenId
where tkt.TicketStatusId=8
--and day(tktintact.EntryDate)=07 and MONTH(tktintact.EntryDate)=05 and year(tktintact.EntryDate)=2020
) as t
where
t.id in('+@ExecutiveId+' )
and t.cid in('+@CompanyId+') and
t.pid in('+@ProductId+')
--t.date between cast(getdate()-2 as date) and cast(getdate() as date)
and t.date= @Date
--where t.id in(186,192,236)
--and t.cid in(3591,419,17)
'
exec (@sql)
end
Rajanikant HawaldarPosted May 14, 2020, 6:59 AM
Arti SonkarPosted May 14, 2020, 5:22 AM
Rajanikant HawaldarPosted May 14, 2020, 5:02 AM