alter PROCEDURE proc_GetWeeklyWorklog
AS
BEGIN
TRY
BEGIN TRANSACTION
DROP TABLE IF EXISTS dbo.#temp
DROP TABLE IF EXISTS dbo.#temp2
create table #temp
(
exid int,
id int,
executivename varchar(500),
timetaken decimal,
entrydate datetime,
day varchar(50)
)
insert into #temp
select id ,null,ExecutiveName,0,null,null from Executive where id
not in
(select distinct ActionTakenByID from TicketInteractionAction as Exe
)
and Detag=0
DECLARE @StartDateTime DATETIME
DECLARE @EndDateTime DATETIME
SET @StartDateTime = GETDATE()-7
SET @EndDateTime = GETDATE()
;WITH cte(ids,id,executivename,timetaken,enterydate,day) AS
(
SELECT exid,0,executivename,null,@StartDateTime as Date,null
from #temp
UNION ALL
SELECT ids,0,executivename,null, DATEADD(d,1,enterydate),null
FROM cte
WHERE enterydate < @EndDateTime
)
SELECT ids,id,executivename,timetaken,enterydate,day into #temp2
FROM cte
order by ids
select distinct t.ExecutiveName as Name, CONVERT(VARCHAR(10),t.enterydate, 120) as Date
,DATENAME(DW,t.enterydate) as Day
,[dbo].[MinutesToHHMM](sum(isnull(timetaken,0)) )as Total_Hours
from
(
select ids,id,executivename,timetaken,enterydate,cast(day as varchar(200)) as day
from #temp2
union
--drop table #temp2
select Exe.Id as exId,ticketaction.Id , Exe.executivename, timetaken,
CONVERT(VARCHAR(10), ticketaction.entrydate, 120) as entrydate
,DATENAME(dw,tkt.ActionDateTime) as Day
from Executive as Exe
join LoginDetails as logdet
on Exe.Id=logdet.ExecutiveID and Exe.Detag=0 and logdet.Detag=0
join TicketInteractionAction as ticketaction
on logdet.Id=ticketaction.ActionTakenByID
join TicketInteraction as tkt
on ticketaction.TicketInteractionId=tkt.Id
--where ticketaction.entrydate between '2020-03-01' and '2020-03-31'
where ticketaction.entrydate between GETDATE()-7 and GETDATE()
) t
group by ExecutiveName, t.enterydate,DATENAME(DW,t.enterydate)
COMMIT
print 'Weekly Worklog'
END TRY
BEGIN CATCH
ROLLBACK
print 'no record found'
END CATCH
Arti SonkarPosted Apr 7, 2020, 5:51 AM
Rajanikant HawaldarPosted Apr 7, 2020, 5:35 AM