Why did my code delete my Fiscal month 11 when it was not present in my silver table?

Question

Why did my code delete my Fiscal month 11 when it was not present in my silver table?

Merit Sowards 0

Any assistance is great appreciated. Here is what is happening. My table "statistics" imported into my bronze_LH, and it moved to my silver_WH as expected. The file did not contain any data for fiscal month 11. But when it moved to the gold_WH all the current data for fiscal month 11 was deleted from the gold_WH table and since the file did not contain any data for fiscal month 11 it is now gone.

I know that it is an error in the code but I am not quite sure where to correct it. Here is what should happen: The statistics file contains 3 months worth of data which is receive daily. The data in the gold table is supposed to be deleted and replaced if the fiscal months are within a 3 month range and if the fiscal month is present in the silver_WH table, if the fiscal month is not present than it should not be deleted from the gold_WH table.

Here is the current code:

DECLARE @maxdate date 
DECLARE @mindate date

;With dd as 
(Select 
*
From dbo.DateDim
where Cast(DateValue as date) = cast(GETDATE() as date)
)

SELECT
@maxdate = [maxmonth]
,@mindate = [minMonth]
from
( 
SELECT
cast(FiscalMonthNumber as varchar(max)) + '/01/' + Cast(FiscalYear as varchar(max)) [minMonth],
(Select cast(FiscalMonthNumber as varchar(max)) + '/01/' + Cast(FiscalYear as varchar(max))FRom dd) [maxmonth]
from dbo.DateDim
where 
    CAST(DateValue as date) = (Select dateadd(MONTH,-3,DateValue) from dd)
) a

DELETE FROM FFF_GOLD_WH.Finance.GLStatisticsFact where FiscalDate Between @mindate and @maxdate

I had originally changed the (Month, -3...) from (Month, -2) because it was duplicating data.

Any help would be greatly appreciated.

0 comments

1 answer

Your answer

Answer 1

It seems that the issue with your SQL code is related to how you are determining which records to delete from the FFF_GOLD_WH.Finance.GLStatisticsFact table. Currently, your code deletes records based on the fiscal date range defined by @mindate and @maxdate, which is calculated based on the current date and the fiscal months. If the fiscal month is not present in the silver_WH table, it should not trigger a deletion in the gold_WH table.

To prevent the deletion of fiscal month 11 when it is not present in the silver_WH table, you need to add a condition that checks for the existence of the fiscal month in the silver_WH table before executing the delete operation. Here’s a suggestion to modify your code:

DECLARE @maxdate date 
DECLARE @mindate date

;WITH dd AS 
(SELECT * FROM dbo.DateDim WHERE CAST(DateValue AS date) = CAST(GETDATE() AS date))

SELECT
    @maxdate = [maxmonth],
    @mindate = [minMonth]
FROM
( 
    SELECT
        CAST(FiscalMonthNumber AS varchar(max)) + '/01/' + CAST(FiscalYear AS varchar(max)) AS [minMonth],
        (SELECT CAST(FiscalMonthNumber AS varchar(max)) + '/01/' + CAST(FiscalYear AS varchar(max)) FROM dd) AS [maxmonth]
    FROM dbo.DateDim
    WHERE 
        CAST(DateValue AS date) = (SELECT DATEADD(MONTH, -3, DateValue) FROM dd)
) a

-- Check if fiscal months exist in silver_WH before deleting
IF EXISTS (SELECT 1 FROM silver_WH WHERE FiscalMonthNumber BETWEEN @mindate AND @maxdate)
BEGIN
    DELETE FROM FFF_GOLD_WH.Finance.GLStatisticsFact 
    WHERE FiscalDate BETWEEN @mindate AND @maxdate
END

This modification includes an IF EXISTS check to ensure that the fiscal months you are about to delete from the gold_WH table exist in the silver_WH table. If they do not exist, the delete operation will not be executed, thus preserving the data for fiscal month 11.

Make sure to test this code in a safe environment before applying it to your production database to ensure it behaves as expected.