Say there are two quarters Q1, Q2, Q3, Q4
Q1 is 20160331 and Q2 is 20160630.
I need to write a query to load the table in such a way that every time I load the table, I need to delete the given quarter records and reload them with the given quarter records.
Once I move from Q1 to Q2 I should not touch Q1 records and only the Q2 records should be truncated and reloaded.
@UseDate is the variable which has the current given quarter date
QuarterKey has the quarter date for which the records are loaded.
Stg has the current given quarter data. Stg is always a truncate reload.
Fact should delete the current quarter date and reload it.
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[Fact]') AND type in (N'U')
AND (SELECT COUNT(*) FROM [dbo].[Fact]) >0)
BEGIN
DELETE FROM [dbo].[Fact]
WHERE (SELECT MAX(QuarterKey) FROM [Fact] ) = @UseDate
END
INSERT INTO [dbo].[Fact]
SELECT * from stg
Where (SELECT MAX(QuarterKey) FROM [Stg] ) = @UseDate
With this query when I’m loading Q2, Q1 data is getting deleted.
It’s a minor fix from what I think but I’m unable to fix it.
Can you please help me.