I am using the following stored procedure to move records in time_elapsed_record_Archive table from the time_elapsed_record table.
ALTER procedure USP_time_elapsed_record_Archive
AS
BEGIN
DECLARE @intialDate datetime = getdate();
DECLARE @startDate datetime = NULL;
DECLARE @endDate datetime = NULL;
DECLARE @count int = 0;
DECLARE @rowsEffected nvarchar(10);
select top 1 @startDate = reportdate from time_elapsed_record order by reportdate
IF(@startDate < DATEADD(YEAR, -2, DATEADD(YY, DATEDIFF(YY,0,@intialDate), 0)))
BEGIN
SELECT @endDate = DATEADD(month, 6, @startDate);
IF(@endDate >= DATEADD(YEAR, -2, DATEADD(YY, DATEDIFF(YY,0,@intialDate), 0)))
BEGIN
SELECT @endDate = DATEADD(YEAR, -2, DATEADD(YY, DATEDIFF(YY,0,@intialDate), -1))
END
END
ELSE
BEGIN
SET @startDate = null;
END
WHILE @startDate <> @endDate
BEGIN
BEGIN TRY
IF(@count>0)
BEGIN
SET @startDate = @endDate;
IF(@startDate < DATEADD(YEAR, -2, DATEADD(YY, DATEDIFF(YY,0,@intialDate), 0)))
BEGIN
SELECT @endDate = DATEADD(month, 6, @startDate);
IF(@endDate >= DATEADD(YEAR, -2, DATEADD(YY, DATEDIFF(YY,0,@intialDate), 0)))
BEGIN
SELECT @endDate = DATEADD(YEAR, -2, DATEADD(YY, DATEDIFF(YY,0,@intialDate), -1))
END
END
ELSE
BEGIN
SET @startDate = null;
END
END
PRINT CONVERT(nvarchar(10), @startDate, 103)+' start count-'+CONVERT(nvarchar(10), @count);
PRINT CONVERT(nvarchar(10), @endDate, 103)+' end count-'+CONVERT(nvarchar(10), @count);
INSERT INTO time_elapsed_record_Archive(
[employeeid],
[reportdate],
[reportid],
[jobcode],
[paycode],
[project],
[activity],
[resource],
[resourcecategory],
[hours],
[status_code],
[deletedind],
[datecreated],
[datemodified],
[oprid],
[msg_id],
[jobrecord],
[rate],
[currency],
[abpayroll],
[ismasterjobcode],
[isUdcProject],
[TimeDrp_personid],
[dte_Archived])
SELECT [employeeid],
[reportdate],
[reportid],
[jobcode],
[paycode],
[project],
[activity],
[resource],
[resourcecategory],
[hours],
[status_code],
[deletedind],
[datecreated],
[datemodified],
[oprid],
[msg_id],
[jobrecord],
[rate],
[currency],
[abpayroll],
[ismasterjobcode],
[isUdcProject],
[TimeDrp_personid],getdate()
FROM time_elapsed_record
WHERE (reportdate >= @startdate AND reportdate <= @enddate) AND
not exists(select reportid
from time_elapsed_record_Archive
where reportid=time_elapsed_record.reportid AND
employeeid = time_elapsed_record.employeeid AND
reportdate = time_elapsed_record.reportdate AND
paycode = time_elapsed_record.paycode AND
employeeid = time_elapsed_record.employeeid AND
project = time_elapsed_record.project)
DELETE FROM time_elapsed_record
WHERE (reportdate >= @startdate AND reportdate <= @enddate)
END TRY
BEGIN CATCH
INSERT INTO dbo.log4net --INSERTING ErrorInfo INTO LOG TABLE
SELECT
'Time Elapsed archive package',
getdate(),
1,
'ERROR',
'USP_time_elapsed_record_Archive',
ERROR_MESSAGE(),
(CONVERT(nvarchar(max), ERROR_NUMBER()) + 'ErrorNumber'+
CONVERT(nvarchar(max),ERROR_SEVERITY()) + 'ErrorSeverity'+
CONVERT(nvarchar(max),ERROR_STATE()) + 'ErrorState'+
ERROR_PROCEDURE() + 'ErrorProcedure'+
CONVERT(nvarchar(max),ERROR_LINE()) + 'ErrorLine'+
ERROR_MESSAGE() + 'ErrorMessage'),
'USP_time_elapsed_record_Archive',
'SQLSERVER'
END CATCH
SET @count = @count+1;
END
END
In this SP, I am moving records which are older than 2 years in the archive table. Everything works fine.
Table structures:
CREATE TABLE [dbo].[time_elapsed_record_Archive](
[archived_id] [int] IDENTITY(1,1) NOT NULL,
[employeeid] [nvarchar](30) NOT NULL,
[reportdate] [datetime] NOT NULL,
[reportid] [nvarchar](30) NOT NULL,
[jobcode] [nvarchar](30) NOT NULL,
[paycode] [nvarchar](30) NOT NULL,
[project] [nvarchar](30) NOT NULL,
[activity] [nvarchar](30) NOT NULL,
[resource] [nvarchar](30) NOT NULL,
[resourcecategory] [nvarchar](30) NOT NULL,
[hours] [float] NULL,
[status_code] [int] NULL,
[deletedind] [bit] NOT NULL,
[datecreated] [datetime] NULL,
[datemodified] [datetime] NULL,
[oprid] [nvarchar](30) NULL,
[msg_id] [int] NULL,
[jobrecord] [int] NULL,
[rate] [decimal](18, 5) NULL,
[currency] [nvarchar](3) NULL,
[abpayroll] [bit] NULL,
[ismasterjobcode] [bit] NULL,
[isUdcProject] [bit] NOT NULL,
[TimeDrp_personid] [int] NULL,
[dte_Archived] [datetime] NOT NULL,
CONSTRAINT [PK_time_elapsed_record_Archive] PRIMARY KEY CLUSTERED
(
[archived_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[time_elapsed_record](
[employeeid] [nvarchar](30) NOT NULL,
[reportdate] [datetime] NOT NULL,
[reportid] [nvarchar](30) NOT NULL,
[jobcode] [nvarchar](30) NOT NULL,
[paycode] [nvarchar](30) NOT NULL,
[project] [nvarchar](30) NOT NULL,
[activity] [nvarchar](30) NOT NULL,
[resource] [nvarchar](30) NOT NULL,
[resourcecategory] [nvarchar](30) NOT NULL,
[hours] [float] NULL,
[status_code] [int] NULL,
[deletedind] [bit] NOT NULL,
[datecreated] [datetime] NULL,
[datemodified] [datetime] NULL,
[oprid] [nvarchar](30) NULL,
[msg_id] [int] NULL,
[jobrecord] [int] NULL,
[rate] [decimal](18, 5) NULL,
[currency] [nvarchar](3) NULL,
[abpayroll] [bit] NULL,
[ismasterjobcode] [bit] NULL,
[isUdcProject] [bit] NOT NULL,
[TimeDrp_personid] [int] NULL
) ON [PRIMARY]
GO
PROBLEM: In while loop the delete query is executing twice for each iteration.
Could someone please assist me, how I can resolve this?
