Deleting Data from SQL
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'=====================================================================';