Sending Mail Alert to respective Supervisor using SQL

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

Use following code to allign values in cell:
          ,"td/@align" = 'right', td =  TotalHours, ''
          ,"td/@align" = 'right', td =  TotalRevenue,''
          ,"td/@align" = 'right', td =  TotalCost,''
          ,"td/@align" = 'right', td =  GrossMargin,''

Use following code to format values in cell:

       ,"td/@align" = 'right', "td/@bgcolor" = CASE WHEN CurrMonth_IdleCost > PrevMonth_IdleCost
           THEN 'RED' END, td = CurrMonth_IdleCost, ''
         ,"td/@align" = 'right', td = PrevWeek_IdleHrs, ''
         ,"td/@align" = 'right', td = PrevWeek_IdleCost, ''
On executing the above stored procs a mail will be sent to the respective supervisors. The format will be as like below: