Records update data and sends new record via email

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'
							
							
							
							Slaughter Report Totals : Current Day
' +
							N'' +
							N'' +
					
							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'
							Farm_House
							Avg_Age_Of_Birds
							DOA
							Avg_Weight_Per_Bird
							DownTime
							Bird_Total
							Tons_Total
							';
							
---  DETAILED NUMBERS CURRENT DAY

DECLARE						@tableHTML  NVARCHAR(MAX) ;

SET							@tableHTML =
							N'
							
							
							
							Slaughter Report Detail : Current Day
' +
							N'' +
							N'' +
					
							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'
							FarmName
							House
							LoadNumber
							AgeOfBirds
							DOA
							TimeArrived
							Avg_Weight_Per_Bird
							Comment
							DownTime
							';

---	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

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Mithilesh Tata2y ago

Accepted answer

It seems like you want to update records in a table and then send those updated records as an email notification to designated users. However, you're encountering issues with duplicate data and comparing new data every minute.

To prevent duplicate data, you need to ensure that you're not inserting duplicate records into your destination table SystemX.dbo.tb_SX_Slaught_Data_Current_Day. One way to handle this is by checking if the record already exists before inserting it. You can use a MERGE statement to update existing records and insert new ones if they don't exist.

Here's how you can modify your stored procedure to achieve this:

-- Start of the stored procedure
CREATE PROCEDURE [dbo].[SFSP_SLAUGHT_DATA_CURRENT_UPDATE]
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Slaughter_Start DATE;
    DECLARE @Max_Load INT;

    -- Calculate the start of the slaughter date based on current time
    SET @Slaughter_Start = CASE
                                WHEN DATEPART(HOUR, SYSDATETIME()) < 4 THEN CAST(DATEADD(DAY, -1, GETDATE()) AS DATE)
                                ELSE CAST(GETDATE() AS DATE)
                            END;

    -- Get the maximum load number
    SELECT @Max_Load = MAX(LoadNumber) FROM SystemX.dbo.DailyBirdWeightDetails WHERE CAST([Date] AS DATE) = @Slaughter_Start;

    -- Merge the data into the destination table
    MERGE INTO SystemX.dbo.tb_SX_Slaught_Data_Current_Day AS target
    USING (
        -- Your select statement here to fetch the data you want to update/insert
        SELECT  FarmName,
                -- Other columns you want to select
        FROM    SystemX.dbo.DailyBirdWeightDetails
        WHERE   [Date] = @Slaughter_Start
                AND LoadNumber <= @Max_Load
    ) AS source ON target.FarmName = source.FarmName -- Adjust the join condition based on your schema
                -- Add more join conditions if needed
    WHEN MATCHED THEN
        -- Update the existing record if needed
        UPDATE SET 
            target.ColumnName = source.ColumnName -- Update columns as needed
    WHEN NOT MATCHED BY TARGET THEN
        -- Insert new records if they don't exist
        INSERT (FarmName, Column1, Column2, ...) -- List all columns you want to insert
        VALUES (source.FarmName, source.Column1, source.Column2, ...); -- Adjust values accordingly

    -- Your email notification code here
    -- You can send email using sp_send_dbmail

    SET NOCOUNT OFF;
END;

Replace ColumnName with actual column names in your table. Adjust the join condition in the ON clause according to your table schema.

Additionally, ensure that your procedure is being executed at appropriate intervals to avoid duplicate data issues. You may need to adjust the scheduling mechanism based on your requirements.