March 14, 2018 at 5:45 am
Hello,
We have several Stored Procedures running to split amounts over different date ranges, to do this we load the Start and End dates into parameters and then use a While loop to insert a row for each day. After each Insert we do the following...
SET @Days = @Days-1
@Days being set prior to the While loop using the following...
SET @Days = SUM(CAST(@Date_To as INT)-CAST(@Date_From as INT))
This has worked with no issues for months if not years however over the past two months we have started to get duplicate entries in the final table for each day completely at random when the job runs overnight. If I run the stored procedures manually the next day there are no duplicates created.
The only thing we are seeing on the Job when this happens is a Null value warning...
"Warning: Null value is eliminated by an aggregate or other SET operation"
I've tried Googling the problem however it quite a specific issue so posts are hard to come by. I did find something on Parallelism however I'm not sure this is effecting the process as it doesn't come up if I check the execution plan for the code.
Any help would be greatly appreciated...
Thanks,
Matt
March 14, 2018 at 6:00 am
Matt
Does the job run at exactly the same time each day, or does it sometimes run before midnight and sometimes after?
Of course, the wider problem here is that you're using a WHILE loop do this at all. If you show us your code, one of us should be able to show you how to insert all the rows in a single operation.
John
March 14, 2018 at 6:10 am
Hi John,
The job is scheduled to run at the same time every evening at 21:00.
Here's the code, thank you.
Declare @Costtype varchar(50)
Declare @ACCTID int
Declare @VER int
DECLARE @RDC varchar(10)
DECLARE @Item varchar(10)
DECLARE @Amount decimal(18,2)
DECLARE @YellowNo varchar(10)
DECLARE @Curr varchar(10)
DECLARE @Date_From_Original datetime
DECLARE @Date_From datetime
DECLARE @Date_To datetime
DECLARE @Days as INT
DECLARE @DaysTotal as INT
Select @VER = Version_Code from [Version] where Version_Name = 'Actual'
Set @Costtype = 'Media (including Online)'
Select @ACCTID = Account_id from [Account] where Account = @Costtype
DELETE FROM dbo.S_Input_SalesSupport_Media
WHERE YellowNumber IS NULL
UPDATE dbo.S_Input_SalesSupport_Media
SET [Version] = 1
UPDATE dbo.S_Input_SalesSupport_Media
SET Processed = Null
FROM S_Input_SalesSupport_Media, Control_Periods
Where DATEPART(YEAR,EndDate) > ExtractYear OR (DATEPART(MONTH,EndDate) >= ExtractMonth and DATEPART(YEAR,EndDate) >= ExtractYear)
UPDATE IL
SET IL.Item_Idx = P.item_Idx
FROM dbo.S_Input_SalesSupport_Media IL INNER JOIN
Dim_Prod_STD_item P ON IL.ItemCode = P.item_Code
UPDATE IL
SET IL.Cust_Idx = C.Cust_Idx
FROM dbo.S_Input_SalesSupport_Media IL INNER JOIN
Dim_Cust_STD_Cust C ON IL.CustomerCode = C.Cust_Code
Update IL
set IL.RDC_Code = rdc.rdc_code, IL.RDC_idx = rdc.rdc_idx
from S_Input_SalesSupport_Media IL INNER JOIN
v_Top1_CustRDC rdc ON IL.CustomerCode = rdc.Cust_Code
-- Clear out existing Fact in Finance Fact Table for the account for this period moving forward
Delete Fact_Finance
from Fact_Finance FA
INNER JOIN
dbo.Control_Periods CP ON SUBSTRING(CONVERT(char(8), FA.Transaction_Date), 1, 4) > CP.ExtractYear OR
(SUBSTRING(CONVERT(char(8), FA.Transaction_Date), 1, 4) >= CP.ExtractYear AND SUBSTRING(CONVERT(char(8), FA.Transaction_Date), 5, 2) >= CP.ExtractMonth)
where Acct_Std_Idx =@ACCTID and [Version] = @VER
WHILE EXISTS (SELECT TOP 1 YellowNumber FROM S_Input_SalesSupport_Media as Media WHERE Processed Is Null Order By CustomerCode, ItemCode, StartDate, EndDate)
BEGIN
SET @Date_From_Original = (SELECT TOP 1 StartDate FROM S_Input_SalesSupport_Media, Control_Periods
WHERE (Processed Is Null and DATEPART(YEAR,EndDate) >= ExtractYear) OR (Processed Is Null and DATEPART(MONTH,EndDate) >= ExtractMonth and DATEPART(YEAR,EndDate) >= ExtractYear)
Order By CustomerCode, ItemCode, StartDate, EndDate)
SET @Date_From = (SELECT TOP 1 StartDate FROM S_Input_SalesSupport_Media, Control_Periods
WHERE (Processed Is Null and DATEPART(YEAR,EndDate) >= ExtractYear) OR (Processed Is Null and DATEPART(MONTH,EndDate) >= ExtractMonth and DATEPART(YEAR,EndDate) >= ExtractYear)
Order By CustomerCode, ItemCode, StartDate, EndDate)
SET @Date_To = (SELECT TOP 1 EndDate FROM S_Input_SalesSupport_Media, Control_Periods
WHERE (Processed Is Null and DATEPART(YEAR,EndDate) >= ExtractYear) OR (Processed Is Null and DATEPART(MONTH,EndDate) >= ExtractMonth and DATEPART(YEAR,EndDate) >= ExtractYear)
Order By CustomerCode, ItemCode, StartDate, EndDate)
SET @RDC = (SELECT TOP 1 RDC_Idx FROM S_Input_SalesSupport_Media, Control_Periods
WHERE (Processed Is Null and DATEPART(YEAR,EndDate) >= ExtractYear) OR (Processed Is Null and DATEPART(MONTH,EndDate) >= ExtractMonth and DATEPART(YEAR,EndDate) >= ExtractYear)
Order By CustomerCode, ItemCode, StartDate, EndDate)
SET @Item = (SELECT TOP 1 Item_Idx FROM S_Input_SalesSupport_Media, Control_Periods
WHERE (Processed Is Null and DATEPART(YEAR,EndDate) >= ExtractYear) OR (Processed Is Null and DATEPART(MONTH,EndDate) >= ExtractMonth and DATEPART(YEAR,EndDate) >= ExtractYear)
Order By CustomerCode, ItemCode, StartDate, EndDate)
SET @Amount = (SELECT TOP 1 Amount FROM S_Input_SalesSupport_Media, Control_Periods
WHERE (Processed Is Null and DATEPART(YEAR,EndDate) >= ExtractYear) OR (Processed Is Null and DATEPART(MONTH,EndDate) >= ExtractMonth and DATEPART(YEAR,EndDate) >= ExtractYear)
Order By CustomerCode, ItemCode, StartDate, EndDate)
SET @YellowNo = (SELECT TOP 1 YellowNumber FROM S_Input_SalesSupport_Media, Control_Periods
WHERE (Processed Is Null and DATEPART(YEAR,EndDate) >= ExtractYear) OR (Processed Is Null and DATEPART(MONTH,EndDate) >= ExtractMonth and DATEPART(YEAR,EndDate) >= ExtractYear)
Order By CustomerCode, ItemCode, StartDate, EndDate)
SET @Curr = (SELECT TOP 1 Curr.Curr_Idx FROM S_Input_SalesSupport_Media, Control_Periods, Dim_CURR_CURR_Curr Curr
WHERE S_Input_SalesSupport_Media.Currency = Curr.Curr_Code AND
((Processed Is Null and DATEPART(YEAR,EndDate) >= ExtractYear) OR (Processed Is Null and DATEPART(MONTH,EndDate) >= ExtractMonth and DATEPART(YEAR,EndDate) >= ExtractYear))
Order By CustomerCode, ItemCode, StartDate, EndDate)
IF (SELECT (CASE WHEN DATEPART(YEAR,@Date_From) < ExtractYear OR (DATEPART(MONTH,@Date_From) < ExtractMonth and DATEPART(YEAR,@Date_From) <= ExtractYear) THEN 1 ELSE 0 END) FROM Control_Periods) = 1
BEGIN
SET @Date_From = (SELECT ExtractYear+'-'+(CASE When LEN(CAST(ExtractMonth as varchar)) = 1 Then '0'+CAST(ExtractMonth as varchar) Else CAST(ExtractMonth as varchar) End)+'-'+'01 00:00:00.000' From Control_Periods)
END
SET @Days = SUM(CAST(@Date_To as INT)-CAST(@Date_From as INT))
SET @DaysTotal = SUM(CAST(@Date_To as INT)-CAST(@Date_From_Original as INT))+1
WHILE @Days > -1
BEGIN
WITH Exchange (ExchRate) AS (
Select ExchRate
From CurrencytoGBP C
Where SUBSTRING(CONVERT(char(8), CAST(Convert(varchar,GETDATE(),112) as INT)), 1, 4) = C.Year
AND SUBSTRING(CONVERT(char(8), CAST(Convert(varchar,GETDATE(),112) as INT)), 5, 2) = C.Monthnumber
AND c.currency='EUR')
Insert into Fact_Finance
(Transaction_Date,Acct_STD_Idx,Cust_STD_RDC_Idx,Prod_Std_Item_Idx,Whse_Whse_Whse_Idx,Curr_Curr_Curr_idx,Amount,AmountEUR,UOM,Rcode,Accum_Type,[Version], PromoFlag, order_prefix,yellow_ref/*,invoice_no,invoice_item*/)
SELECT Top 1
CAST(Convert(varchar,@Date_From+@Days,112) as INT) as Transaction_Date,
@ACCTID as Acct_STD_Idx,
@RDC as Cust_STD_RDC_Idx,
@Item as Prod_Std_Item_Idx,
'6' as Whse_Whse_Whse_Idx,
@Curr as Curr_Curr_Curr_idx,
(CASE When @Curr = '7' Then (@Amount/@DaysTotal)/(CASE When C.ExchRate = 0.00 Then E.ExchRate Else C.ExchRate End) Else @Amount/@DaysTotal End) as Amount,
NULL as AmountEur,
'CASE' as UOM,
'SSM' as Rcode,
1 as Accum_Type,
1 as [Version],
0 as PromoFlag,
'STD' as order_prefix,
@YellowNo as YellowNumber
From CurrencytoGBP C, Exchange E
Where SUBSTRING(CONVERT(char(8), CAST(Convert(varchar,@Date_From+@Days,112) as INT)), 1, 4) = C.Year
AND SUBSTRING(CONVERT(char(8), CAST(Convert(varchar,@Date_From+@Days,112) as INT)), 5, 2) = C.Monthnumber
AND c.currency='EUR'
SET @Days = @Days-1
END
UPDATE S_Input_SalesSupport_Media
SET Processed = 1 Where Processed Is Null and CustomerCode+ItemCode+Convert(varchar,StartDate,103)+Convert(varchar,EndDate,103) =
(Select TOP 1 CustomerCode+ItemCode+Convert(varchar,StartDate,103)+Convert(varchar,EndDate,103) From S_Input_SalesSupport_Media Where Processed Is Null
Order By CustomerCode, ItemCode, StartDate, EndDate)
END
March 14, 2018 at 9:20 am
There's a lot to worry about in that code, I'm afraid. The loop within a loop will mean it'll run really slow. And you have a SELECT TOP 1 clause without an ORDER BY, so you don't know which row you're going to get. That also means I can't help you to eliminate the loops, since I can't tell what the code is meant to do. With regard to the duplicate rows, I suspect it's due to anomalies in your data, or perhaps there's a glitch in the code. These are the statements that determine the start and end dates: do you have any way of reverting the database to just before you got a duplicate and running them to see why you're getting overlapping ranges?
SET @Date_From = (SELECT TOP 1 StartDate FROM S_Input_SalesSupport_Media, Control_Periods
WHERE (Processed Is Null and DATEPART(YEAR,EndDate) >= ExtractYear) OR (Processed Is Null and DATEPART(MONTH,EndDate) >= ExtractMonth and DATEPART(YEAR,EndDate) >= ExtractYear)
Order By CustomerCode, ItemCode, StartDate, EndDate)
SET @Date_To = (SELECT TOP 1 EndDate FROM S_Input_SalesSupport_Media, Control_Periods
WHERE (Processed Is Null and DATEPART(YEAR,EndDate) >= ExtractYear) OR (Processed Is Null and DATEPART(MONTH,EndDate) >= ExtractMonth and DATEPART(YEAR,EndDate) >= ExtractYear)
Order By CustomerCode, ItemCode, StartDate, EndDate)
Actually, you mentioned the NULL value warning in your original post. Do you only see it when you get the duplicates? The only aggregate functions I can see in your code are where you set the values of @Days and @DaysTotal. The SUM function is totally superfluous anyway - there's nothing to add up so there's no need for it. I suspect it was put there by a lazy programmer when he or she started having problems with NULLs!
John
March 14, 2018 at 10:25 am
Thanks for this, I have removed the SUM's. The data itself is pretty straight forward and we have had issue with this before that have been rectified. The odd thing is that the code runs with no issues when I run the Stored Procedure from SSMS so the Data and the Dates appear to be fine. The jobs run every night so we will be able to see tomorrow morning if the changes have helped.
March 14, 2018 at 10:28 am
Sorry forgot to say, it does appear that we only get the duplicates when we see the Null Value Warning, we have five of these stored procedures that run one after the other and two of them had the messages this morning and both had duplicate entries in their output.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply