Deleting Data from SQL

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'=====================================================================';