sql server - SQL- Current quarter data has to be truncated and reloaded but the previous quarter should remain untouched -
say there 2 quarters q1, q2, q3, q4
q1 20160331 , q2 20160630.
need write query load table in such way every time load table, need delete given quarter records , reload them given quarter records.
once move q1 q2 should not touch q1 records , q2 records should truncated , reloaded.
@usedate
variable has current given quarter date quarterkey
has quarter date records loaded.
stg
has current given quarter data. stg
truncate reload.
fact should delete current quarter date , reload it.
if exists (select * sys.objects object_id = object_id(n'[dbo].[fact]') , type in (n'u') , (select count(*) [dbo].[fact]) >0) begin delete [dbo].[fact] (select max(quarterkey) [fact] ) = @usedate end insert [dbo].[fact] select * stg (select max(quarterkey) [stg] ) = @usedate
with query when i’m loading q2, q1 data getting deleted.
it’s minor fix think i’m unable fix it.
can please me.
if @usedate has values 20160331 need extract quarter , delete records fact has same quarter .
delete [dbo].[fact] datepart(quarter,quarterkey) = datepart(quarter,@usedate); insert [dbo].[fact] select * stg datepart(quarter,quarterkey) not in (select datepart(quarter,@usedate);
it more performance efficient if @usedate having extracted quarter data
Comments
Post a Comment