Database count to send mail daily reports CREATE PROC SEND_SEKURIT_PENDING_AUTOMAIL AS BEGIN SELECT A.ORD, A.NAMEE,A.AGEING,A.Pending_For_OCR_DW01,B.Pending_For_OCR_DW02,NULL as NC01, NULL as NC02,A.Pending_For_OCR_DW01+B.Pending_For_OCR_DW02 as Total,A.Responsibility INTO #TEMPSEKURITMAILALTER FROM ( SELECT 1 as ORD,'OCR Process' as NAMEE, '<2' AS AGEING, count(BARCODE_NO) AS Pending_For_OCR_DW01,'SSC' AS Responsibility FROM T_SEK_INVOICE_DETAILS WHERE INVOICE_STATUS ='SCANNED'AND COMPANY_CODE='DW01' AND convert(varchar,INVOICE_SCANNING_DATE,112)>getdate()-2 UNION SELECT 1 as ORD, 'OCR Process' as NAMEE, '>2'AS AGEING,count(BARCODE_NO)AS Pending_For_OCR_DW01,'SSC' AS Responsibility FROM T_SEK_INVOICE_DETAILS WHERE INVOICE_STATUS ='SCANNED'AND COMPANY_CODE='DW01' AND convert(varchar,INVOICE_SCANNING_DATE,112)getdate()-2 UNION SELECT 1 as ORD,'OCR Process' as NAMEE, '>2'AS AGEING,count(BARCODE_NO)AS Pending_For_OCR_DW02,'SSC' AS Responsibility FROM T_SEK_INVOICE_DETAILS WHERE INVOICE_STATUS ='SCANNED'AND COMPANY_CODE='DW02' AND convert(varchar,INVOICE_SCANNING_DATE,112)'1' AND convert(varchar,INVOICE_SCANNING_DATE,112)>getdate()-2 UNION SELECT 2 as ORD,'Clarification' as NAMEE,'>2'AS AGEING, count(BARCODE_NO)AS Rejection_DW01,'SSC' AS Responsibility FROM T_SEK_INVOICE_DETAILS WHERE INVOICE_STATUS NOT IN('REJECTED','DELETED') AND COMPANY_CODE='DW01' AND REJECT_REASON <>'1' AND convert(varchar,INVOICE_SCANNING_DATE,112)'1' )as C LEFT OUTER JOIN ( SELECT 2 as ORD, 'Clarification' as NAMEE,'<2'AS AGEING, count(BARCODE_NO)AS Rejection_OCR_DW02,'SSC' AS Responsibility FROM T_SEK_INVOICE_DETAILS WHERE INVOICE_STATUS NOT IN('REJECTED','DELETED') AND COMPANY_CODE='DW02' AND REJECT_REASON <>'1' AND convert(varchar,INVOICE_SCANNING_DATE,112)>getdate()-2 UNION SELECT 2 as ORD,'Clarification' as NAMEE,'>2'AS AGEING, count(BARCODE_NO)AS Rejection_DW02,'SSC' AS Responsibility FROM T_SEK_INVOICE_DETAILS WHERE INVOICE_STATUS NOT IN('REJECTED','DELETED') AND COMPANY_CODE='DW02' AND REJECT_REASON <>'1' AND convert(varchar,INVOICE_SCANNING_DATE,112)'1' )as D ON C.AGEING=D.AGEING UNION SELECT E.ORD,E.NAMEE,E.AGEING,E.EDI_Pending_DW01,F.EDI_Pending_DW02,NULL as NC01, NULL as NC02,E.EDI_Pending_DW01+F.EDI_Pending_DW02 as Total,E.Responsibility FROM ( SELECT 3 as ORD,'EDIFACT Creation' as NAMEE,'<2'AS AGEING, count(BARCODE_NO)AS EDI_Pending_DW01,'IT' AS Responsibility FROM T_SEK_INVOICE_DETAILS WHERE INVOICE_STATUS ='OCR' AND COMPANY_CODE='DW01' AND REJECT_REASON ='1' AND convert(varchar,INVOICE_SCANNING_DATE,112)>getdate()-2 UNION SELECT 3 as ORD,'EDIFACT Creation' as NAMEE,'>2'AS AGEING, count(BARCODE_NO)AS EDI_Pending_DW01,'IT' AS Responsibility FROM T_SEK_INVOICE_DETAILS WHERE INVOICE_STATUS ='OCR' AND COMPANY_CODE='DW01' AND REJECT_REASON ='1' AND convert(varchar,INVOICE_SCANNING_DATE,112)getdate()-2 UNION SELECT 3 as ORD,'EDIFACT Creation' as NAMEE, '>2'AS AGEING, count(BARCODE_NO)AS EDI_Pending_DW02,'IT' AS Responsibility FROM T_SEK_INVOICE_DETAILS WHERE INVOICE_STATUS ='OCR' AND COMPANY_CODE='DW02' AND REJECT_REASON ='1' AND convert(varchar,INVOICE_SCANNING_DATE,112)getdate()-2 UNION SELECT 4 as ORD,'SAP Integration' as NAMEE, '>2'AS AGEING, count(BARCODE_NO)AS AS2_Pending_DW01,'IT' AS Responsibility FROM T_SEK_INVOICE_DETAILS WHERE INVOICE_STATUS ='EDI' AND COMPANY_CODE='DW01' AND convert(varchar,INVOICE_SCANNING_DATE,112)getdate()-2 UNION SELECT 4 as ORD,'SAP Integration' as NAMEE, '>2'AS AGEING, count(BARCODE_NO)AS AS2_Pending_DW02,'IT' AS Responsibility FROM T_SEK_INVOICE_DETAILS WHERE INVOICE_STATUS ='EDI' AND COMPANY_CODE='DW02' AND convert(varchar,INVOICE_SCANNING_DATE,112)' SET @Body =@Body +'' SET @Body =@Body +'' SET @Body =@Body +' ' SET @Body =@Body +'' SET @Body =@Body +'' SET @Body =@Body +'' SET @Body =@Body +'' SET @Body =@Body +'' SET @Body =@Body +'' SET @Body =@Body +'' SET @Body =@Body +'' SET @Body =@Body +'' DECLARE SEKURIT_MAIL CURSOR FOR SELECT NAMEE,AGEING,PENDING_FOR_OCR_DW01,PENDING_FOR_OCR_DW02,NC01,NC02,TOTAL,RESPONSIBILITY FROM #TEMPSEKURITMAILALTER OPEN SEKURIT_MAIL FETCH NEXT FROM SEKURIT_MAIL INTO @NAME,@AGEING,@PENDING_FOR_OCR_DW01,@PENDING_FOR_OCR_DW02,@NC01,@NC02,@TOTAL,@RESPONSIBILITY WHILE (@@FETCH_STATUS = 0) BEGIN IF (@AGEING='Total') BEGIN SET @Body=@Body+'' IF(@NAME<>'OCR Process'AND @NAME<>'Clarification' AND @NAME<>'EDIFACT Creation'AND @NAME<>'SAP Integration ') BEGIN SET @Body=@Body+'' END SET @Body=@Body+'' SET @Body=@Body+'' SET @Body=@Body+'' SET @Body=@Body+'' SET @Body=@Body+'' SET @Body=@Body+'' SET @Body=@Body+'' SET @Body=@Body+'' END ELSE BEGIN SET @Body=@Body+'' IF(@row%2=1) SET @Body=@Body+'' SET @Body=@Body+' ' SET @Body=@Body+'' SET @Body=@Body+'' SET @Body=@Body+'' SET @Body=@Body+'' SET @Body=@Body+'' SET @Body=@Body+'' SET @row=@row+1 END FETCH NEXT FROM SEKURIT_MAIL INTO @NAME,@AGEING,@PENDING_FOR_OCR_DW01,@PENDING_FOR_OCR_DW02,@NC01,@NC02,@TOTAL,@RESPONSIBILITY END CLOSE SEKURIT_MAIL DEALLOCATE SEKURIT_MAIL SET @Body=@Body+ '

' SET @Body =@Body +'Sekurit-Pending Invoice Statistics
Status	Ageing	DW01	DW02	NC01	NC02	Total	Responsibility
'+ISNULL(@NAME,'')+'	'+ISNULL(@AGEING,'')+'	'+ISNULL(@PENDING_FOR_OCR_DW01,'')+'	'+ISNULL(@PENDING_FOR_OCR_DW02,'')+'	'+ISNULL(@NC01,0)+'	'+ISNULL(@NC02,0)+'	'+ISNULL(@TOTAL,0)+'	'+ISNULL(@RESPONSIBILITY,'')+'
'+ISNULL(@NAME,'')+'	'+ISNULL(@AGEING,'')+'	'+ISNULL(@PENDING_FOR_OCR_DW01,'')+'	'+ISNULL(@PENDING_FOR_OCR_DW02,'')+'	'+ISNULL(@NC01,'')+'	'+ISNULL(@NC02,'')+'	'+ISNULL(@TOTAL,0)+'	'+ISNULL(@RESPONSIBILITY,'')+'

' --SET @Body = @Body + '

*This is a system generated mail.Please donot reply to this.

' PRINT @Body DROP TABLE #TEMPSEKURITMAILALTER -- --EXEC msdb.dbo.sp_send_dbmail -- --@recipients = 'Vivek.Srivatsan@saint-gobain.com;Muralidaran.M@saint-gobain.com;Prithviraj.S@saint-gobain.com;Sujoy.Mukherjee@saint-gobain.com;Parthasarathi.S@saint-gobain.com;Gowdhami.M@saint-gobain.com;Chandra.G@saint-gobain.com', --@copy_recipients = 'Sundaram.DR@saint-gobain.com;Jagathratchagan.J@saint-gobain.com;Nagarajan.R2@saint-gobain.com', --@subject = @Subject, --@body = @Body, --@body_format = 'HTML' END