Hi Team
I need some help, i have a procedure that updates record and send them to the destignated users as table. Current problem, it duplicates the data and we often then find it difficult to compare new data every 1 minute. Instead it must when new records is inserted it must update it and send this record via to the table as email by notifiy users.
USE [SystemX] GO /****** Object: StoredProcedure [dbo].[SFSP_SLAUGHT_DATA_CURRENT_UPDATE] Script Date: 2024/03/04 14:30:18 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /* Modified BY: LEONARD LETSOALO DATE CREATED: 28 April 2020 REASON: Requested to remove a member from mailing list. REQUESTED BY: Christine Massyn */ --ALTER PROCEDURE [dbo].[SFSP_SLAUGHT_DATA_CURRENT_UPDATE] --AS -- [CHH - 20100819] = Get the current date in our required format (date-only, with time being zero) DECLARE @CurDate DATETIME, @PriorDate DATETIME, @Slaughter_Start DATE, @Slaughter_End DATE, @Max_Load INT -------------------------------------------------------------------------------------------------------------------------------------------------------- SET @CurDate = CONVERT(DATETIME, CAST(DATEPART(dd, GETDATE()) AS VARCHAR(2)) + '/' + CAST(DATEPART(mm, GETDATE()) AS VARCHAR(2)) + '/' + CAST(DATEPART(yyyy, GETDATE()) AS VARCHAR(4)), 103) SET @PriorDate = CONVERT(DATETIME, CAST(DATEPART(dd, DATEADD(DAY,-1,GETDATE())) AS VARCHAR(2)) + '/' + CAST(DATEPART(mm, DATEADD(DAY,-1,GETDATE())) AS VARCHAR(2)) + '/' + CAST(DATEPART(yyyy, DATEADD(DAY,-1,GETDATE())) AS VARCHAR(4)), 103) SET @Slaughter_Start = (CASE WHEN (SELECT DATEPART(HOUR,SYSDATETIME())) < 4 THEN DATEADD(DAY,-1,GETDATE()) WHEN (SELECT DATEPART(HOUR,SYSDATETIME())) > 4 THEN CAST(GETDATE() AS DATE) END) SET @Slaughter_End = (CAST(GETDATE() AS DATE)) SET @Max_Load = (SELECT MAX(LoadNumber) FROM SystemX.dbo.DailyBirdWeightDetails WHERE CAST(DATE AS DATE) BETWEEN @Slaughter_Start AND @Slaughter_End) -------------------------------------------------------------------------------------------------------------------------------------------------------- -- [CHH - 20100819] = Now, get the counts from SystemX & dw_Staging for the current date DECLARE @SysXCount INT DECLARE @StagingCount INT -------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------/If the loads are from the prior day, include in this report/------------------------------------------------------------- IF (SELECT DATEPART(HOUR,SYSDATETIME())) < 4 -------------------------------------------------------------------------------------------------------------------------------------------------------- BEGIN -------------------------------------------------------------------------------------------------------------------------------------------------------- -------/Set SystemX count/----- SELECT @SysXCount = COUNT(*) FROM SystemX.dbo.DailyBirdWeightDetails WHERE [Date] = @PriorDate -------/Set staging count/----- SELECT @StagingCount = COUNT(*) FROM SystemX.dbo.tb_SX_Slaught_Data_Current_Day WHERE [Date] = @PriorDate END -----------------------------/If the morning has exceeded 4am, move on to current day's information/---------------------------------------------------- IF (SELECT DATEPART(HOUR,SYSDATETIME())) > 4 -------------------------------------------------------------------------------------------------------------------------------------------------------- BEGIN -------------------------------------------------------------------------------------------------------------------------------------------------------- -------/Set SystemX count/----- SELECT @SysXCount = COUNT(*) FROM SystemX.dbo.DailyBirdWeightDetails WHERE [Date] = @CurDate -------/Set Staging count/----- SELECT @StagingCount = COUNT(*) FROM SystemX.dbo.tb_SX_Slaught_Data_Current_Day WHERE [Date] = @CurDate -------------------------------------------------------------------------------------------------------------------------------------------------------- END -------------------------------------------------------------------------------------------------------------------------------------------------------- --SELECT FarmName, -- CASE -- WHEN LEFT(ISNULL(House,'0'),1) NOT IN ('0','1','2','3','4','5','6','7','8','9') AND -- RIGHT(LEFT(ISNULL(House,'0'),2),1) NOT IN ('0','1','2','3','4','5','6','7','8','9') -- THEN RIGHT(ISNULL(House,'0'),1) -- ELSE -- CASE -- WHEN LEFT(ISNULL(House,'0'),1) NOT IN ('0','1','2','3','4','5','6','7','8','9') AND -- RIGHT(LEFT(ISNULL(House,'0'),2),1) IN ('0','1','2','3','4','5','6','7','8','9') -- THEN RIGHT(isnull(House,'0'),2) -- ELSE ISNULL(House,'0') -- END END AS House, -- CAST(AgeOfBirds AS DECIMAL(18,0)), -- DOA, -- TimeArrived, -- CAST(((GrossWeight - Tare)/(Actualcounter + DOA)) -- AS DECIMAL (18,3)) As AVG_WEIGHT_PER_BIRD, -- CASE -- WHEN Comment = ' ' -- THEN ' ' -- ELSE -- CASE -- WHEN ISNULL(Comment,'None') = 'None' -- THEN ' ' -- ELSE Comment -- END -- END AS Comment, -- CAST(LoadNumber AS DECIMAL(18,0)) AS LoadNumber, -- ISNULL(TimeLost,0) AS TimeLost, -- [Date] -- FROM SystemX.dbo.DailyBirdWeightDetails -- WITH (NOLOCK) -- WHERE DATE BETWEEN @Slaughter_Start AND @Slaughter_End -- AND LoadNumber <= @Max_Load -- [CHH - 20100819] = Only execute the stored procedure code if there are more records in SystemX than in dw_Staging IF @SysXCount > @StagingCount BEGIN TRUNCATE TABLE SystemX.dbo.tb_SX_Slaught_Data_Current_Day INSERT INTO SystemX.dbo.tb_SX_Slaught_Data_Current_Day SELECT FarmName, CASE WHEN LEFT(ISNULL(House,'0'),1) NOT IN ('0','1','2','3','4','5','6','7','8','9') AND RIGHT(LEFT(ISNULL(House,'0'),2),1) NOT IN ('0','1','2','3','4','5','6','7','8','9') THEN RIGHT(ISNULL(House,'0'),1) ELSE CASE WHEN LEFT(ISNULL(House,'0'),1) NOT IN ('0','1','2','3','4','5','6','7','8','9') AND RIGHT(LEFT(ISNULL(House,'0'),2),1) IN ('0','1','2','3','4','5','6','7','8','9') THEN RIGHT(isnull(House,'0'),2) ELSE ISNULL(House,'0') END END AS House, CAST(AgeOfBirds AS DECIMAL(18,0)), DOA, TimeArrived, CAST(((GrossWeight - Tare)/NULLIF((Actualcounter + DOA),0)) AS DECIMAL (18,3)) As AVG_WEIGHT_PER_BIRD, CASE WHEN Comment = ' ' THEN ' ' ELSE CASE WHEN ISNULL(Comment,'None') = 'None' THEN ' ' ELSE Comment END END AS Comment, CAST(LoadNumber AS DECIMAL(18,0)), ISNULL(TimeLost,0) AS TimeLost, [Date] FROM SystemX.dbo.DailyBirdWeightDetails WITH (NOLOCK) WHERE DATE = @Slaughter_Start -- TOTALS FOR CURRENT DAY DECLARE @tableHTML02 NVARCHAR(MAX); SET @tableHTML02 = N'<html> <body style="text-align:center; font-family:arial"> <H1 style="text-align:center; font-family:arial; font-size:18"> <u> Slaughter Report Totals : Current Day<u/></H1>' + N'<table border="1" width=100% style="text-align:center; font-family:arial">' + N'<tr> <td style="text-align:center; font-family:arial; color:White; font:bold" bgcolor="#800000">Farm_House</td> <td td style="text-align:center; font-family:arial; color:White; font:bold" bgcolor="#800000">Avg_Age_Of_Birds</td> <td td style="text-align:center; font-family:arial; color:White; font:bold" bgcolor="#800000">DOA</td> <td td style="text-align:center; font-family:arial; color:White; font:bold" bgcolor="#800000">Avg_Weight_Per_Bird</td> <td td style="text-align:center; font-family:arial; color:White; font:bold" bgcolor="#800000">DownTime</td> <td td style="text-align:center; font-family:arial; color:White; font:bold" bgcolor="#800000">Bird_Total</td> <td td style="text-align:center; font-family:arial; color:White; font:bold" bgcolor="#800000">Tons_Total</td> </tr>' + CAST (( SELECT td = B.Farm_House, ' ', td = B.Avg_Age_Of_Birds, ' ', td = B.DOA, ' ', td = CAST(B.TONS / (CAST(B.Bird_Numbers AS decimal(18,3)) + DOA) AS decimal(18,3)), ' ', td = B.DownTime, ' ', td = B.Bird_Numbers, ' ', td = B.Tons, ' ' FROM ( SELECT ISNULL(CONVERT(NVARCHAR(50),A.FARM_HOUSE),'GRAND TOTAL') AS FARM_HOUSE, CAST(AVG(A.AGEOFBIRDS) AS DECIMAL(18,0)) AS AVG_AGE_OF_BIRDS, SUM(A.DOA) AS DOA, SUM(CAST(A.TONS / (CAST(A.NO_OF_BIRDS AS decimal(18,3)) + DOA) AS decimal(18,3))) AS Avg_Weight_Per_Bird, CAST(SUM(ISNULL(A.DOWNTIME,0)) AS INT) AS DOWNTIME, CAST(SUM(A.NO_OF_BIRDS) AS INT) AS Bird_Numbers, CAST(SUM(A.TONS) AS INT) AS TONS FROM ( SELECT ISNULL((CONVERT(NVARCHAR(50),FarmName)+ ' ' + CONVERT(NVARCHAR(50),House)),'GRAND TOTAL') AS FARM_HOUSE, CAST((SUM(ActualCounter * AgeOfBirds)/ NULLIF(SUM(ActualCounter),0)) AS DECIMAL(18,3))AS AGEOFBIRDS, SUM(DOA) AS DOA, MAX(TimeArrived) AS TIME_ARRIVED, CASE WHEN SUM(ISNULL((ActualCounter + DOA),0)) = 0 THEN 0 ELSE CAST((((GrossWeight - Tare)/NULLIF((Actualcounter + DOA),0))) AS DECIMAL (18,3)) END AS AVG_WEIGHT_PER_BIRD, MAX(CAST(LoadNumber AS DECIMAL(18,0))) AS LOAD_NUMBER, SUM(TimeLost) AS DOWNTIME, SUM(ACTUALCOUNTER) AS NO_OF_BIRDS, SUM(GROSSWEIGHT-TARE) AS TONS FROM SystemX.dbo.DailyBirdWeightDetails WHERE CAST(DATE AS DATE) = @Slaughter_Start GROUP BY Farmname,House,GrossWeight,Tare,ActualCounter,DOA )A GROUP BY FARM_HOUSE WITH ROLLUP)B FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX)) + N'</table>'; --- DETAILED NUMBERS CURRENT DAY DECLARE @tableHTML NVARCHAR(MAX) ; SET @tableHTML = N'<html> <body style="text-align:center; font-family:arial"> <H1 style="text-align:center; font-family:arial; font-size:18"> <u> Slaughter Report Detail : Current Day<u/></H1>' + N'<table border="1" width=100% style="text-align:center; font-family:arial">' + N'<tr> <td style="text-align:center; font-family:arial; color:White; font:bold" bgcolor="#800000">FarmName</td> <td td style="text-align:center; font-family:arial; color:White; font:bold" bgcolor="#800000">House</td> <td td style="text-align:center; font-family:arial; color:White; font:bold" bgcolor="#800000">LoadNumber</td> <td td style="text-align:center; font-family:arial; color:White; font:bold" bgcolor="#800000">AgeOfBirds</td> <td td style="text-align:center; font-family:arial; color:White; font:bold" bgcolor="#800000">DOA</td> <td td style="text-align:center; font-family:arial; color:White; font:bold" bgcolor="#800000">TimeArrived</td> <td td style="text-align:center; font-family:arial; color:White; font:bold" bgcolor="#800000">Avg_Weight_Per_Bird</td> <td td style="text-align:center; font-family:arial; color:White; font:bold" bgcolor="#800000">Comment</td> <td td style="text-align:center; font-family:arial; color:White; font:bold" bgcolor="#800000">DownTime</td> </tr>' + CAST (( SELECT td = FarmName, ' ', td = House, ' ', td = Load_Number, ' ', td = AgeOfBirds, ' ', td = DOA, ' ', td = Time_Arrived, ' ', --'td/@bgcolor'=CASE WHEN AVG_WEIGHT_PER_BIRD >= 1.85 THEN 'Green' --ELSE 'Red' END, td = AVG_WEIGHT_PER_BIRD, ' ', td = Comment, ' ', td = DownTime, ' ' FROM SystemX.dbo.tb_SX_Slaught_Data_Current_Day A WHERE CAST(A.Date AS DATE) = @Slaughter_Start ORDER BY Load_Number ASC FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX)) + N'</table>'; --- COMBINE SEPERATE TABLES ON TO ONE E-MAIL --DECLARE @ALL_TABLES NVARCHAR(MAX); --SET @ALL_TABLES = (@tableHTML + @tableHTML02) --EXEC msdb.dbo.sp_send_dbmail -- @recipients = '[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected]', -- @subject = 'Live_Birds_Update', -- @body = @ALL_TABLES, -- @body_format = 'HTML' END