extrobe
1/31/2017 - 2:16 PM

Archiving the ADDDR tabe

Archiving the ADDDR tabe

Archiving the ADDDR Table 
--Declare variables to be used 
DECLARE @rundate date, @startdate date, @batch smallint, @TPWorkDay tinyint, @IsBusDay nChar(1), @msgdate nvarchar(30) 
 
 
SET @batch = 5 --User Input - how many to process 
 
 
--Get the first non-work day 4 date (we want to keep WD4 values) 
SET @rundate = (SELECT MIN(FileDate) FROM ADDDR WHERE filedate NOT IN (SELECT calendardate FROM dbo.Calendar c WHERE c.TPWorkDay = 4 AND c.IsBusinessDay = 'Y')) 
SET @startdate = @rundate 
 
 
--loop form the start date through to the size of the batch 
WHILE @rundate < dateadd(dd,@batch,@startdate) 
 
 
BEGIN 
--PRINT @rundate 
SET @msgdate = @rundate 
RAISERROR ('Processing %s', 0, 1,@msgdate ) WITH NOWAIT 
SET @tpworkday = (SELECT tpworkday FROM dbo.Calendar c WHERE c.CalendarDate = @rundate) 
SET @IsBusDay = (SELECT IsBusinessDay FROM dbo.Calendar c WHERE c.CalendarDate = @rundate) 
 
 
IF @tpworkday <> 4 AND @IsBusDay = 'Y' --we want to skip WD4 values 
BEGIN 
INSERT INTO dbo.ADDDR_Archive 
SELECT * FROM dbo.ADDDR a WHERE a.FileDate = @rundate  
DELETE FROM dbo.ADDDR WHERE dbo.ADDDR.FileDate = @rundate  
--else 
--RAISERROR ('%s was Skipped due to WD4', 0, 1,@msgdate ) WITH NOWAIT 
END 
set @rundate = DATEADD( dd,1,@rundate ) 
END