The below query helps you to trigger mail to respective supervisors of the empoyees containing office In-time violation details
DECLARE @LastDay_PreviousWeek DATETIME
DECLARE @FirstDay_PreviousWeek DATETIME
SELECT @LastDay_PreviousWeek = DATEADD(DAY,5,DATEADD(WEEK, DATEDIFF(WEEK,0,GETDATE())-1,0))
SELECT @FirstDay_PreviousWeek = DATEADD(DAY,-1,DATEADD(WEEK, DATEDIFF(WEEK,0,GETDATE())-1,0))
DECLARE @COUNTER INT
DECLARE @Max INT
SET @Counter = 1
DECLARE @TEMP1 TABLE
(SNO INT, Department Varchar(255), Employee NVARCHAR(255), SupervisorName NVARCHAR(255), SupervisorKey INT, SupervisorMail NVARCHAR(255)
,[Date] DATETIME, ExpectedTime VARCHAR(20), ActualEntryTime NVARCHAR(20), GraceTime NVARCHAR(20), Status1 VARCHAR(50), Status2 VARCHAR(50))
INSERT INTO @Temp1
SELECT DENSE_RANK() OVER (ORDER BY SupervisorEmail) AS SNO
,DDE.DepartmentName
,DE.Firstname
,DE.SupervisorName
,DE.SupervisorKey
,DE.SupervisorEMail
,DD.[Date]
,DE.ExpectedTime
,MIN(FH.EntryTime) AS ActualEntryTime
,CONVERT(VARCHAR(8),DATEADD(n, 10, DE.ExpectedTime), 114) AS GraceTime
,CASE WHEN DE.ExpectedTime < MIN(FH.EntryTime) THEN 'Violated' ELSE 'Normal' END AS [STATUS1]
,CASE WHEN CONVERT(VARCHAR(8),DATEADD(n, 10, DE.ExpectedTime), 114) < MIN(FH.EntryTime) THEN 'Violated' ELSE 'Normal' END AS [STATUS2]
FROM [devsql2000\CITBI].[CITGlobal Warehouse].dbo.[DimEmployee] DE
JOIN [devsql2000\CITBI].[CITGlobal Warehouse].dbo.FactHolder FH ON FH.Employeekey = DE.EmployeeKey
JOIN [devsql2000\CITBI].[CITGlobal Warehouse].dbo.DimDepartment DDE ON DDE.DepartmentKey = DE.DepartmentKey
JOIN [devsql2000\CITBI].[CITGlobal Warehouse].dbo.DimDate DD ON DD.DateKey = FH.ActualInDateKey
WHERE DATEPART(Week, GETDATE())-1 = DD.[WeekOfYear]
AND DD.[year] = YEAR(GETDATE()) AND DE.DepartmentKey in (105)
GROUP BY DD.[Date]
,DDE.DepartmentName
,DE.Firstname
,DE.SupervisorName
,DE.SupervisorKey
,DE.ExpectedTime
,SupervisorEMail
SELECT @Max = (SELECT MAX(SNO) FROM @TEMP1)
--SELECT * from @Temp1
WHILE (@COUNTER <= @Max)
BEGIN
DECLARE @tableHTML NVARCHAR(MAX)
DECLARE @LeadMailID NVARCHAR(MAX)
SELECT @LeadMailID = SupervisorMail FROM @Temp1
WHERE SNO = @COUNTER
SET @tableHTML =
N'<span color=Red style="font-weight:bold;font-size:1.5em;font-family:Calibri;color:Red">
In-Time Violation Alert for Last Week </span>' +
'<span color=Red style="font-weight:bold;font-size:1em;font-family:Calibri;color:Red"> (From ' + CONVERT(VARCHAR(50), @FirstDay_PreviousWeek,106 ) + ' to ' + CONVERT(VARCHAR(50), @LastDay_PreviousWeek, 106 ) + ')</span>' +
N'<table cellspacing="0" cellpadding="5" border="1" style="font-family:Calibri">' +
N'<tr bgcolor="#87CEFA" style="font-weight:bold;"><td>SNo</td>' +
N'<td>Department</td>' +
N'<td>Employee</td>' +
N'<td>Date</td>' +
N'<td>Expected Time</td>' +
N'<td>Actual Time</td>' +
N'<td>Time Difference (min)</td></tr>' +
CAST ( ( SELECT td = ROW_NUMBER() OVER (ORDER BY [Department], Employee,[Date]), ''
,td = [Department], ''
,td = [Employee], ''
,td = CONVERT(VARCHAR(10),[Date],101),''
,td = [ExpectedTime], ''
,td = [ActualEntryTime], ''
,td = DATEDiff(n,[ExpectedTime], [ActualEntryTime]), ''
FROM (SELECT Department
,Employee
,[Date]
,ExpectedTime
,ActualEntryTime
FROM @Temp1
WHERE Status2 ='Violated' AND SNO = @Counter) TT
ORDER BY Department
,Employee
,[Date]
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
-- print @tableHTML
EXEC msdb.dbo.sp_send_dbmail
@subject = 'Employee In-Time Violation Alert for Last Week',
@profile_name = 'InTimeViolationAlert',
@recipients = @LeadMailID,
@copy_recipients = 'mahadevan.v@congruentindia.com',
@body = @tableHTML,
@body_format = 'HTML' ;
SET @Counter = @Counter + 1
END
