Untitled

Set @TableTail = '</table></body></html>';

Set @TableHead = '<html><head>' +

                             '<p>Hello Team,</p><p> These are your open action items. Please note anything in yellow means the date is within 180 days and anything in red means the date is within 90 days.</p>' +

                  '<p><a class="btn btn-success" href="http://sjams2019.azurewebsites.net/ActionItems?sales=MW&status=openandpending&dueDate=all" target="_blank">Update</a></p>' +

                             '<style>' +

                  'td {border: padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} ' +

                  '</style>' +

                  '</head>' +

                  '<body><table cellpadding=0 cellspacing=0 border=0>' +

                  '<tr bgcolor=#FFEFD8><td align=left><b>Principal</b></td>' +

                  '<td align=left><b>Company</b></td>' +

                  '<td align=left><b>Action Item</b></td>' +

                  '<td align=left><b>Sales</b></td>' +

                             '<td align=left><b>Due Date</b></td>' +

                             '<td align=left><b>Updated</b></td>' +

                  '<td align=left><b>Owner</b></td></tr>';

    DECLARE @CNT as int, @SLS as NVARCHAR(10)
    select  [employeeid], [Sales], [Email Address]
    into #loctempemployee from tblEmployees

    Set @CNT =  (Select COUNT (Distinct EmployeeID) from #loctempemployee)
           While (@CNT > 0)

             begin

                Set @message=
                    (
                    SELECT tr.Principal As [TD], tr.[Company Name] As [TD], ai.[Action Item] As [TD], ai.Owners As [TD], LEFT((ai.[Due Date]),10) As [TD], LEFT((ai.Updated),10) As [TD], em.Sales As [TD]
                    FROM [tblActionItem] ai
                    INNER JOIN tblTripReport tr ON ai.TripReportID = tr.tripreportID
                    INNER JOIN tblCustomers cu ON cu.CustomerID = tr.[Customer ID]
                    INNER JOIN tblEmployees em ON em.EmployeeID = cu.EmployeeID
                    WHERE em.Sales = (Select sales from #loctempemployee Where EmployeeID = (Select top 1 EmployeeID from #loctempemployee))
                    For XML raw('tr'), Elements
                    )


                Select @Body = (@message)


                Set @Body = Replace(@Body, '_x0020_', space(1))

                Set @Body = Replace(@Body, '_x003D_', '=')

                Set @Body = Replace(@Body, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')

                Set @Body = Replace(@Body, '<TRRow>0</TRRow>', '')


                Select @Body = @TableHead + @Body + @TableTail