;with cte
as
(
select action.Id as intid, ex.ExecutiveName,cast(tkt.ActionDateTime
as date )as Date,DATENAME(dw,tkt.ActionDateTime) as Day
from TicketInteractionAction as action
join TicketInteraction as tkt
on action.TicketInteractionId=tkt.Id
join LoginDetails as logdet
on action.ActionTakenById=logdet.Id
join Executive as ex
on logdet.ExecutiveId=ex.Id
where ActionTakenByID=204
and action.TimeTaken<>'0.00'
)
select ExecutiveName,Date,Day,[dbo].[MinutesToHHMM](sum(ticketaction.TimeTaken )) as TotalTime from cte ct
join TicketInteractionAction ticketaction
on ticketaction.TicketInteractionId=ct.intid
where EntryDate is not null
and MONTH(ticketaction.EntryDate)=09
and YEAR(ticketaction.EntryDate)=2019
group by ExecutiveName,Date,Day
order by Date asc
Jignesh KumarPosted Mar 27, 2020, 5:23 AM