The script for clearing a cumulative database for a specified period.
See the reference information and discussion at the tracker.
The beginning and end of the period are set by the variables @StartDate and @EndDate.
USE DATABASE_NAME; GO -- Declare the variables to be used. DECLARE @TotalShifts int, @TotalVisits int, @FirstShift int, @LastShift int, @FirstVisit int,@LastVisit int, @StartDate Date, @EndDate Date, @MidserverID int, @MidserverName varchar(30); -- Initialize and set the variables. SET @StartDate='2014-02-11'; SET @EndDate='2015-12-31'; --Print report header PRINT N'====================================================================='; PRINT N'SCRIPT START'; --declare cursor for selecting midservers declare cur CURSOR LOCAL for select SIFR from CASHGROUPS open cur fetch next from cur into @MidserverID while @@FETCH_STATUS = 0 BEGIN --execute main delete --reseting variables SET @TotalShifts=0; SET @TotalVisits=0; SET @FirstShift=0; SET @LastShift=0; SET @FirstVisit=0; SET @LastVisit=0; --Find Midserver name SELECT @MidserverName=NAME FROM CASHGROUPS WHERE SIFR=@MidserverID; PRINT N'Starting script for deleting data of cash server ID: '+RTRIM(CAST(@MidserverID AS nvarchar(30)))+' ('+@MidserverName+')'; PRINT N'Dates from ( '+ RTRIM(CAST(@StartDate AS nvarchar(30)))+' ) to ( '+ RTRIM(CAST(@EndDate AS nvarchar(30)))+' )'; --Select first Shift for StartDate SELECT TOP 1 @FirstShift = SHIFTNUM FROM GLOBALSHIFTS WHERE (MIDSERVER = @MidserverID) AND (SHIFTDATE >= @StartDate); --Select last Shift for EndDate SELECT TOP 1 @LastShift = SHIFTNUM FROM GLOBALSHIFTS WHERE (MIDSERVER = @MidserverID) AND (SHIFTDATE <= @EndDate) ORDER BY SHIFTNUM DESC ; --Print first and last shifts PRINT N'First Shift: ' + RTRIM(CAST(@FirstShift AS nvarchar(30))); PRINT N'Last Shift: ' + RTRIM(CAST(@LastShift AS nvarchar(30))); SELECT @TotalShifts=Count(SHIFTNUM) FROM GLOBALSHIFTS WHERE MIDSERVER=@MidserverID AND SHIFTDATE>=@StartDate AND SHIFTDATE<=@EndDate; PRINT N'Total shifts found: ' + RTRIM(CAST(@TotalShifts AS nvarchar(30))); --Select first visit SIFR from the first Shift SELECT TOP 1 @FirstVisit = SIFR FROM VISITS WHERE MIDSERVER=@MidserverID AND ISTARTCOMMONSHIFT=@FirstShift; --Select last visit SIFR from the last Shift SELECT TOP 1 @LastVisit = SIFR FROM VISITS WHERE MIDSERVER=@MidserverID AND ISTARTCOMMONSHIFT<=@LastShift ORDER by SIFR desc; --Print first and last visits PRINT N'Visits found for midserver '+@MidserverName +':'; PRINT N'First Visit: ' + RTRIM(CAST(@FirstVisit AS nvarchar(30))); PRINT N'Last Visit: ' + RTRIM(CAST(@LastVisit AS nvarchar(30))); SELECT @TotalVisits=Count(SIFR) FROM VISITS WHERE MIDSERVER=@MidserverID AND ISTARTCOMMONSHIFT>=@FirstShift AND ISTARTCOMMONSHIFT<=@LastShift ; PRINT N'Total visits found: ' + RTRIM(CAST(@TotalVisits AS nvarchar(30))); -- Execute Delete only if visits detected in the VISIT range IF (@TotalShifts >0 AND @TotalVisits > 0) BEGIN PRINT N'NOW DELETING DATA....'; DELETE FROM ORDERS WHERE (MIDSERVER = @MidserverID) AND (VISIT>=@FirstVisit) AND (VISIT<=@LastVisit); DELETE FROM OPERATIONLOG WHERE (MIDSERVER = @MidserverID) AND (VISIT>=@FirstVisit) AND (VISIT<=@LastVisit); DELETE FROM PRINTCHECKS WHERE (MIDSERVER = @MidserverID) AND (VISIT>=@FirstVisit) AND (VISIT<=@LastVisit); DELETE FROM SESSIONDISHES WHERE (MIDSERVER = @MidserverID) AND (VISIT>=@FirstVisit) AND (VISIT<=@LastVisit); DELETE FROM ORDERSESSIONS WHERE (MIDSERVER = @MidserverID) AND (VISIT>=@FirstVisit) AND (VISIT<=@LastVisit); DELETE FROM PAYBINDINGS WHERE (MIDSERVER = @MidserverID) AND (VISIT>=@FirstVisit) AND (VISIT<=@LastVisit); DELETE FROM PAYMENTS WHERE (MIDSERVER = @MidserverID) AND (VISIT>=@FirstVisit) AND (VISIT<=@LastVisit); DELETE FROM PAYMENTSEXTRA WHERE (MIDSERVER = @MidserverID) AND (VISIT>=@FirstVisit) AND (VISIT<=@LastVisit); DELETE FROM TAXPARTS WHERE (MIDSERVER = @MidserverID) AND (VISIT>=@FirstVisit) AND (VISIT<=@LastVisit); DELETE FROM KDSDATA WHERE (MIDSERVER = @MidserverID) AND (VISITID>=@FirstVisit) AND (VISITID<=@LastVisit); DELETE FROM DELIVERYDATA WHERE (MIDSERVER = @MidserverID) AND (VISIT>=@FirstVisit) AND (VISIT<=@LastVisit); DELETE FROM CURRLINES WHERE (MIDSERVER = @MidserverID) AND (VISIT>=@FirstVisit) AND (VISIT<=@LastVisit); DELETE FROM DISHDISCOUNTS WHERE (MIDSERVER = @MidserverID) AND (VISIT>=@FirstVisit) AND (VISIT<=@LastVisit); DELETE FROM DISHMODIFIERS WHERE (MIDSERVER = @MidserverID) AND (VISIT>=@FirstVisit) AND (VISIT<=@LastVisit); DELETE FROM DISCPARTS WHERE (MIDSERVER = @MidserverID) AND (VISIT>=@FirstVisit) AND (VISIT<=@LastVisit); DELETE FROM DISHVOIDS WHERE (MIDSERVER = @MidserverID) AND (VISIT>=@FirstVisit) AND (VISIT<=@LastVisit); DELETE FROM VISITS WHERE (MIDSERVER = @MidserverID) AND (SIFR>=@FirstVisit) AND (SIFR<=@LastVisit); DELETE FROM VISITGUESTS WHERE (MIDSERVER = @MidserverID) AND (VISIT>=@FirstVisit) AND (VISIT<=@LastVisit); DELETE FROM INVOICES WHERE (MIDSERVER = @MidserverID) AND (VISIT>=@FirstVisit) AND (VISIT<=@LastVisit); DELETE FROM SHIFTS WHERE MIDSERVER = @MidserverID AND ICOMMONSHIFT>=@FirstShift AND ICOMMONSHIFT <=@LastShift; DELETE FROM GLOBALSHIFTS WHERE MIDSERVER = @MidserverID AND SHIFTDATE>=@StartDate AND SHIFTDATE<=@EndDate; PRINT N'DATA DELETED!!!'; PRINT N'END' PRINT N'====================================================================='; END ELSE PRINT N'Either No shifts founs or No Visits in the selected dates interval - NO DATA deleted!'; PRINT N'---------------------------------------------------------------------'; --now execute next itteration for the next Midserver fetch next from cur into @MidserverID END close cur deallocate cur PRINT N'END OF SCRIPT' PRINT N'=====================================================================';