how to send email to an individua executive via sql server

Question

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