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.

1 answer

Your answer

Answer 1

Q&A Assist

AI generated content. This question contains an answer created with AI from Q&A Assist. Learn more

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.

Merit Sowards 0 Reputation points

2025-11-12T17:13:01.29+00:00

See comment
Merit Sowards 0 Reputation points

2025-11-12T17:26:48.6366667+00:00

Would this still allow the other months that are present in the silver_WH table to be deleted from the gold_WH table and then replaced in the gold_WH table? Example if fiscal month 12 is in the silver_WH table and the gold_WH table, the data would be deleted from the gold_WH table and replace with the data that is contained in the silver_WH table?
Kalyani Kondavaradala 4,600 Reputation points Microsoft External Staff Moderator

2025-11-18T11:30:25.67+00:00
Hi Merit Sowards,

If fiscal month 12 is within the @mindate and @maxdate range and exists in silver_WH, the IF EXISTS condition will be met.

The DELETE statement will then remove all records from FFF_GOLD_WH.Finance.GLStatisticsFact within the @mindate and @maxdate range.

Fiscal months that do exist in silver_WH (like fiscal month 12) will be deleted from gold_WH and replaced with the updated data from silver_WH.

Therefore, the IF EXISTS check ensures that you don't perform a deletion when no relevant data exists in silver_WH for the specified period, while still allowing the intended delete-and-replace cycle for existing months.
Merit Sowards 0 Reputation points

2025-11-18T17:11:36.67+00:00

Thank you for your response, Kalyani. But what keeps happening is that my data for fiscal month 11 keeps being deleted from my gold table even though it is not in the silver table, however fiscal month 11 is within the mindate and maxdate. How can I correct the code so that if it is only comparing the data that exists in the silver table to what is currently in the gold and remove it from gold only if the fiscal months are in the silver table, else do not remove from gold? I hope this makes sense

Kalyani Kondavaradala 4,600 Microsoft External Staff Moderator

Hi Merit Sowards, can you try the below code and make sure table names and CTEs

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;

-- Delete records from the Gold table only if the corresponding FiscalMonth exists in the Silver table
DELETE G
FROM FFF_GOLD_WH.Finance.GLStatisticsFact AS G
WHERE EXISTS (
    SELECT 1
    FROM silver_WH AS S
    WHERE 
        G.FiscalDate >= @mindate AND G.FiscalDate <= @maxdate
        AND MONTH(G.FiscalDate) = S.FiscalMonthNumber -- Assuming Silver has FiscalMonthNumber
        AND YEAR(G.FiscalDate) = S.FiscalYear -- Assuming Silver has FiscalYear, or join on a common date key
);

Kalyani Kondavaradala 4,600 Reputation points Microsoft External Staff Moderator

2025-11-21T10:38:44.3166667+00:00

Hi Merit Sowards, did you get a chance to try the above code? any queries

Share via

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

1 answer

Your answer