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