PURGING DATA

• Deny_Christian

  SSC Eights!

  Points: 914

  More actions

  November 3, 2017 at 2:50 am

  #1966530

  MMartin1 - Friday, November 3, 2017 2:02 AM

  I definitely think there is a language barrier here as I am still hung up on 

  I have an assignment, to Purging data, with conditions:

  I am not sure if from the date dimension table OR a date column in a another table. He may be trying to just select these dates of interest into some temporary table and delete the rows in another table that join to the temporary table. But again, just a educated guess. 

  hi All,
  I have finished making the stored procedure.

  This is the Target:
  

  
CREATE PROCEDURE SP_PURGING
AS

  DECLARE
@maxid int,
@flag int,        ----ID PARAMETER
@SQLSTATE varchar (8000),
@DB_NAME VARCHAR(50), --DATABASE_NAME
@SCHEMA_NAME VARCHAR (50), --SCHEMA_NAME
@TABLE_NAME varchar (50), --TABLE_NAME
@COLUMN VARCHAR(50), --COLUMN_NAME
@TIME DATETIME,
@RETENTION INT
SET @TIME = (SELECT VALUE FROM CTLFW_APP..Ptbc_Dwh_Param_Config WHERE COMPONENT = 'BUSINESS_dATE')
set @maxid = (select max(id) from CTLFW_APP.dbo.Ptbc_Dwh_Retention_Process WHERE ACTIVE_YN='Y')
SET @FLAG = '1'

  WHILE @FLAG <= @MAXID
BEGIN
    SET @DB_NAME = (select TARGET_DB_NAME from CTLFW_APP.dbo.Ptbc_Dwh_Retention_Process where id = @flag AND ACTIVE_YN='Y')
    SET @SCHEMA_NAME = (select [TARGET_SCHEMA_NAME] from CTLFW_APP.dbo.Ptbc_Dwh_Retention_Process where id = @flag AND ACTIVE_YN='Y')
    SET @TABLE_NAME = (select TARGET_TABLE_NAME from CTLFW_APP.dbo.Ptbc_Dwh_Retention_Process where id = @flag AND ACTIVE_YN='Y')
    SET @COLUMN = 'DATEKEY' --(select COLUMN_NAME from CTLFW_APP.dbo.Ptbc_Dwh_Retention_Process where id = @flag)
    SET @RETENTION = (select Retention_In_Month from CTLFW_APP.dbo.Ptbc_Dwh_Retention_Process where id = @flag AND ACTIVE_YN='Y')

  SET @SQLSTATE = 'select 1 while @@ROWCOUNT > 0 BEGIN delete top (5000) A from ' +@DB_NAME+ +'.'+ +@SCHEMA_NAME+ +'.'+ +@TABLE_NAME+
' A
INNER JOIN DWIM_sRCE_IMG..DB_DATE B ON A.'+@COLUMN+' = B.'+@COLUMN+' WHERE A.' +@COLUMN+ ' < ''' +cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@TIME)-@RETENTION,0))as varchar)+'''
AND A.'+@COLUMN+' NOT IN (''' +cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@TIME)-@RETENTION,0))as varchar)+''') and a.iseombusiness = 0 END'

  PRINT ''
print '--============= PURGING DATA '+CAST(@RETENTION AS VARCHAR)+ ' MONTHS ================--'
PRINT ''
print @SQLSTATE

  set @flag = @flag + 1

  END

  --The Result:

  --============= PURGING DATA 3 MONTHS ================--

  select 1 while @@ROWCOUNT > 0 BEGIN delete top (5000) A from DWIM_SRCE_IMG.dbo.DB_DATEDummy A
INNER JOIN DWIM_sRCE_IMG..DB_DATE B ON A.DATEKEY = B.DATEKEY WHERE A.DATEKEY < 'Mar 31 2017 11:59PM'
AND A.DATEKEY NOT IN ('Mar 31 2017 11:59PM') and a.iseombusiness = 0 END

  --============= PURGING DATA 6 MONTHS ================--

  select 1 while @@ROWCOUNT > 0 BEGIN delete top (5000) A
--SELECT *
from DWIM_SRCE_IMG.dbo.DB_DATEDummy1 A
INNER JOIN DWIM_sRCE_IMG..DB_DATE B ON A.DATEKEY = B.DATEKEY WHERE A.DATEKEY < 'Dec 31 2016 11:59PM'
AND A.DATEKEY NOT IN ('Dec 31 2016 11:59PM') and a.iseombusiness = 0 END

  --============= PURGING DATA 12 MONTHS ================--

  select 1 while @@ROWCOUNT > 0 BEGIN delete top (5000) A from DWIM_SRCE_IMG.dbo.DB_DATEDummy2 A
INNER JOIN DWIM_sRCE_IMG..DB_DATE B ON A.DATEKEY = B.DATEKEY WHERE A.DATEKEY < 'Jun 30 2016 11:59PM'
AND A.DATEKEY NOT IN ('Jun 30 2016 11:59PM') and a.iseombusiness = 0 END

• MMartin1

  One Orange Chip

  Points: 27795

  More actions

  November 3, 2017 at 3:43 pm

  #1966639

  Can you see my previous post and let me know if this is what you are trying to do? It appears you are, and from what I see you are doing things in a very inefficient way since the code is prone to error and not readibly reusable.

  ----------------------------------------------------

• MMartin1

  One Orange Chip

  Points: 27795

  More actions

  November 3, 2017 at 4:00 pm

  #1966640

  In my dimDate table I like to have a DATE type to represent the weekStart, monthStart, quarterStart, etc...

  With this design in place I can easily handle such a request with the following serving as the base of my dates of interest >

  select    monthStart, max(dateKey) as lastDateOfMonth
from    dimdate
where    datekey >= cast(dateadd(m, -6, dateadd(d, -day(getdate())+1, getdate())) as date)
        and dateKey < dateadd(d, -day(getdate()), getdate())--cast(getdate() as date)
group by monthStart

  union all

  Select    cast(dateadd(d, -day(getdate())+1, getdate()) as date) as monthStart ,
        datekey
from    dimdate
where    datekey >=dateadd(d, -day(getdate()), getdate())
        and datekey< getdate()
        
order by monthStart        

  My output quick and dirty comes out as : 

  monthStart................................                                  lastDateOfMonth
  2017-05-01 00:00:00.0000000    2017-05-31
  2017-06-01 00:00:00.0000000    2017-06-30
  2017-07-01 00:00:00.0000000    2017-07-31
  2017-08-01 00:00:00.0000000    2017-08-31
  2017-09-01 00:00:00.0000000    2017-09-30
  2017-10-01 00:00:00.0000000    2017-10-31
  2017-11-01 00:00:00.0000000    2017-11-01
  2017-11-01 00:00:00.0000000    2017-11-02
  2017-11-01 00:00:00.0000000    2017-11-03

  ----------------------------------------------------