Help: Purge Table Stored Procedure

I have to create a stored procedure to purge "x" # of records from a
table. I have two tables (script below):

Schedule
ScheduleHistory

I need to purge records out of ScheduleHistory. The problem is that
the # of records that needs to be "kept" is dynamic, and stored in the
Schedule table. So all records in ScheduleHistory should be purged,
except for the most recent "x" number of records.

For instance, for each Schedule row, you can specify how many records
to "keep" at all times. I want to keep the most recent "x" # of
ScheduleHistory rows for each Schedule.

In the script below, I have two schedules ("test1" and "test2"). One
of them specifies that the most recent "5" records in ScheduleHistory
should be kept, and the other specifies that the most recent "3"
records in ScheduleHistory should be kept.

So, I need a stored procedure that can clean up this table on demand.
I can't figure this one out...it's over my head.

Dan

------
SCRIPT BELOW
------

CREATE TABLE [dbo].[Schedule] (
[ScheduleID] [int] IDENTITY (1, 1) NOT NULL ,
[TypeFullName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[TimeLapse] [int] NOT NULL ,
[TimeLapseMeasurement] [varchar] (2) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RetryTimeLapse] [int] NOT NULL ,
[RetryTimeLapseMeasurement] [varchar] (2) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RetainHistoryNum] [int] NOT NULL ,
[ObjectDependencies] [varchar] (300) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ScheduleHistory] (
[ScheduleHistoryID] [int] IDENTITY (1, 1) NOT NULL ,
[ScheduleID] [int] NOT NULL ,
[StartDate] [datetime] NOT NULL ,
[EndDate] [datetime] NULL ,
[Succeeded] [bit] NULL ,
[LogNotes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NextStart] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

INSERT INTO Schedule VALUES ('Test1', 1, 'd', 1, 'm', 5, '')
INSERT INTO Schedule VALUES ('Test2', 1, 'd', 1, 'm', 3, '')
INSERT INTO ScheduleHistory VALUES (1,'1/1/2000 1:00 PM', '1/1/2000
1:01 PM', 1, '', '1/2/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/1/2000 1:00 PM', '1/1/2000
1:01 PM', 1, '', '1/2/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/2/2000 1:00 PM', '1/2/2000
1:01 PM', 1, '', '1/3/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/2/2000 1:00 PM', '1/2/2000
1:01 PM', 1, '', '1/3/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/3/2000 1:00 PM', '1/3/2000
1:01 PM', 1, '', '1/4/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/3/2000 1:00 PM', '1/3/2000
1:01 PM', 1, '', '1/4/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/4/2000 1:00 PM', '1/4/2000
1:01 PM', 1, '', '1/5/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/4/2000 1:00 PM', '1/4/2000
1:01 PM', 1, '', '1/5/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/5/2000 1:00 PM', '1/5/2000
1:01 PM', 1, '', '1/6/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/5/2000 1:00 PM', '1/5/2000
1:01 PM', 1, '', '1/6/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/6/2000 1:00 PM', '1/6/2000
1:01 PM', 1, '', '1/7/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/6/2000 1:00 PM', '1/6/2000
1:01 PM', 1, '', '1/7/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/7/2000 1:00 PM', '1/7/2000
1:01 PM', 1, '', '1/8/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/7/2000 1:00 PM', '1/7/2000
1:01 PM', 1, '', '1/8/2000 1:00 PM')Assuming you want the keep the most recent rows based on StartDate, one
method:

DELETE FROM ScheduleHistory
FROM Schedule s
WHERE
(
SELECT COUNT(*)
FROM ScheduleHistory sh
WHERE
sh.ScheduleID = ScheduleHistory.ScheduleID AND
sh.ScheduleID = s.ScheduleID AND
sh.StartDate >= ScheduleHistory.StartDate
) > s.RetainHistoryNum

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Dan Caron" <dancaron-listserv0687@.mailblocks.com> wrote in message
news:4d6e6a6c.0404231047.60cd40d0@.posting.google.c om...
> I have to create a stored procedure to purge "x" # of records from a
> table. I have two tables (script below):
> Schedule
> ScheduleHistory
> I need to purge records out of ScheduleHistory. The problem is that
> the # of records that needs to be "kept" is dynamic, and stored in the
> Schedule table. So all records in ScheduleHistory should be purged,
> except for the most recent "x" number of records.
> For instance, for each Schedule row, you can specify how many records
> to "keep" at all times. I want to keep the most recent "x" # of
> ScheduleHistory rows for each Schedule.
> In the script below, I have two schedules ("test1" and "test2"). One
> of them specifies that the most recent "5" records in ScheduleHistory
> should be kept, and the other specifies that the most recent "3"
> records in ScheduleHistory should be kept.
> So, I need a stored procedure that can clean up this table on demand.
> I can't figure this one out...it's over my head.
> Dan
> ------
> SCRIPT BELOW
> ------
>
> CREATE TABLE [dbo].[Schedule] (
> [ScheduleID] [int] IDENTITY (1, 1) NOT NULL ,
> [TypeFullName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [TimeLapse] [int] NOT NULL ,
> [TimeLapseMeasurement] [varchar] (2) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [RetryTimeLapse] [int] NOT NULL ,
> [RetryTimeLapseMeasurement] [varchar] (2) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [RetainHistoryNum] [int] NOT NULL ,
> [ObjectDependencies] [varchar] (300) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[ScheduleHistory] (
> [ScheduleHistoryID] [int] IDENTITY (1, 1) NOT NULL ,
> [ScheduleID] [int] NOT NULL ,
> [StartDate] [datetime] NOT NULL ,
> [EndDate] [datetime] NULL ,
> [Succeeded] [bit] NULL ,
> [LogNotes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [NextStart] [datetime] NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> INSERT INTO Schedule VALUES ('Test1', 1, 'd', 1, 'm', 5, '')
> INSERT INTO Schedule VALUES ('Test2', 1, 'd', 1, 'm', 3, '')
> INSERT INTO ScheduleHistory VALUES (1,'1/1/2000 1:00 PM', '1/1/2000
> 1:01 PM', 1, '', '1/2/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/1/2000 1:00 PM', '1/1/2000
> 1:01 PM', 1, '', '1/2/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/2/2000 1:00 PM', '1/2/2000
> 1:01 PM', 1, '', '1/3/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/2/2000 1:00 PM', '1/2/2000
> 1:01 PM', 1, '', '1/3/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/3/2000 1:00 PM', '1/3/2000
> 1:01 PM', 1, '', '1/4/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/3/2000 1:00 PM', '1/3/2000
> 1:01 PM', 1, '', '1/4/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/4/2000 1:00 PM', '1/4/2000
> 1:01 PM', 1, '', '1/5/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/4/2000 1:00 PM', '1/4/2000
> 1:01 PM', 1, '', '1/5/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/5/2000 1:00 PM', '1/5/2000
> 1:01 PM', 1, '', '1/6/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/5/2000 1:00 PM', '1/5/2000
> 1:01 PM', 1, '', '1/6/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/6/2000 1:00 PM', '1/6/2000
> 1:01 PM', 1, '', '1/7/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/6/2000 1:00 PM', '1/6/2000
> 1:01 PM', 1, '', '1/7/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/7/2000 1:00 PM', '1/7/2000
> 1:01 PM', 1, '', '1/8/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/7/2000 1:00 PM', '1/7/2000
> 1:01 PM', 1, '', '1/8/2000 1:00 PM')|||That is exactly what I needed. Thank you very much Dan.

-Dan

"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message news:<MLvic.10091$e4.6777@.newsread2.news.pas.earthlink.n et>...
> Assuming you want the keep the most recent rows based on StartDate, one
> method:
> DELETE FROM ScheduleHistory
> FROM Schedule s
> WHERE
> (
> SELECT COUNT(*)
> FROM ScheduleHistory sh
> WHERE
> sh.ScheduleID = ScheduleHistory.ScheduleID AND
> sh.ScheduleID = s.ScheduleID AND
> sh.StartDate >= ScheduleHistory.StartDate
> ) > s.RetainHistoryNum
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dan Caron" <dancaron-listserv0687@.mailblocks.com> wrote in message
> news:4d6e6a6c.0404231047.60cd40d0@.posting.google.c om...
> > I have to create a stored procedure to purge "x" # of records from a
> > table. I have two tables (script below):
> > Schedule
> > ScheduleHistory
> > I need to purge records out of ScheduleHistory. The problem is that
> > the # of records that needs to be "kept" is dynamic, and stored in the
> > Schedule table. So all records in ScheduleHistory should be purged,
> > except for the most recent "x" number of records.
> > For instance, for each Schedule row, you can specify how many records
> > to "keep" at all times. I want to keep the most recent "x" # of
> > ScheduleHistory rows for each Schedule.
> > In the script below, I have two schedules ("test1" and "test2"). One
> > of them specifies that the most recent "5" records in ScheduleHistory
> > should be kept, and the other specifies that the most recent "3"
> > records in ScheduleHistory should be kept.
> > So, I need a stored procedure that can clean up this table on demand.
> > I can't figure this one out...it's over my head.
> > Dan
> > ------
> > SCRIPT BELOW
> > ------
> > CREATE TABLE [dbo].[Schedule] (
> > [ScheduleID] [int] IDENTITY (1, 1) NOT NULL ,
> > [TypeFullName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
> > NOT NULL ,
> > [TimeLapse] [int] NOT NULL ,
> > [TimeLapseMeasurement] [varchar] (2) COLLATE
> > SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [RetryTimeLapse] [int] NOT NULL ,
> > [RetryTimeLapseMeasurement] [varchar] (2) COLLATE
> > SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [RetainHistoryNum] [int] NOT NULL ,
> > [ObjectDependencies] [varchar] (300) COLLATE
> > SQL_Latin1_General_CP1_CI_AS NOT NULL
> > ) ON [PRIMARY]
> > GO
> > CREATE TABLE [dbo].[ScheduleHistory] (
> > [ScheduleHistoryID] [int] IDENTITY (1, 1) NOT NULL ,
> > [ScheduleID] [int] NOT NULL ,
> > [StartDate] [datetime] NOT NULL ,
> > [EndDate] [datetime] NULL ,
> > [Succeeded] [bit] NULL ,
> > [LogNotes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [NextStart] [datetime] NULL
> > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> > GO
> > INSERT INTO Schedule VALUES ('Test1', 1, 'd', 1, 'm', 5, '')
> > INSERT INTO Schedule VALUES ('Test2', 1, 'd', 1, 'm', 3, '')
> > INSERT INTO ScheduleHistory VALUES (1,'1/1/2000 1:00 PM', '1/1/2000
> > 1:01 PM', 1, '', '1/2/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/1/2000 1:00 PM', '1/1/2000
> > 1:01 PM', 1, '', '1/2/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/2/2000 1:00 PM', '1/2/2000
> > 1:01 PM', 1, '', '1/3/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/2/2000 1:00 PM', '1/2/2000
> > 1:01 PM', 1, '', '1/3/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/3/2000 1:00 PM', '1/3/2000
> > 1:01 PM', 1, '', '1/4/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/3/2000 1:00 PM', '1/3/2000
> > 1:01 PM', 1, '', '1/4/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/4/2000 1:00 PM', '1/4/2000
> > 1:01 PM', 1, '', '1/5/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/4/2000 1:00 PM', '1/4/2000
> > 1:01 PM', 1, '', '1/5/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/5/2000 1:00 PM', '1/5/2000
> > 1:01 PM', 1, '', '1/6/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/5/2000 1:00 PM', '1/5/2000
> > 1:01 PM', 1, '', '1/6/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/6/2000 1:00 PM', '1/6/2000
> > 1:01 PM', 1, '', '1/7/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/6/2000 1:00 PM', '1/6/2000
> > 1:01 PM', 1, '', '1/7/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/7/2000 1:00 PM', '1/7/2000
> > 1:01 PM', 1, '', '1/8/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/7/2000 1:00 PM', '1/7/2000
> > 1:01 PM', 1, '', '1/8/2000 1:00 PM')|||That is exactly what I needed. Thank you very much Dan.

-Dan

"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message news:<MLvic.10091$e4.6777@.newsread2.news.pas.earthlink.n et>...
> Assuming you want the keep the most recent rows based on StartDate, one
> method:
> DELETE FROM ScheduleHistory
> FROM Schedule s
> WHERE
> (
> SELECT COUNT(*)
> FROM ScheduleHistory sh
> WHERE
> sh.ScheduleID = ScheduleHistory.ScheduleID AND
> sh.ScheduleID = s.ScheduleID AND
> sh.StartDate >= ScheduleHistory.StartDate
> ) > s.RetainHistoryNum
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dan Caron" <dancaron-listserv0687@.mailblocks.com> wrote in message
> news:4d6e6a6c.0404231047.60cd40d0@.posting.google.c om...
> > I have to create a stored procedure to purge "x" # of records from a
> > table. I have two tables (script below):
> > Schedule
> > ScheduleHistory
> > I need to purge records out of ScheduleHistory. The problem is that
> > the # of records that needs to be "kept" is dynamic, and stored in the
> > Schedule table. So all records in ScheduleHistory should be purged,
> > except for the most recent "x" number of records.
> > For instance, for each Schedule row, you can specify how many records
> > to "keep" at all times. I want to keep the most recent "x" # of
> > ScheduleHistory rows for each Schedule.
> > In the script below, I have two schedules ("test1" and "test2"). One
> > of them specifies that the most recent "5" records in ScheduleHistory
> > should be kept, and the other specifies that the most recent "3"
> > records in ScheduleHistory should be kept.
> > So, I need a stored procedure that can clean up this table on demand.
> > I can't figure this one out...it's over my head.
> > Dan
> > ------
> > SCRIPT BELOW
> > ------
> > CREATE TABLE [dbo].[Schedule] (
> > [ScheduleID] [int] IDENTITY (1, 1) NOT NULL ,
> > [TypeFullName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
> > NOT NULL ,
> > [TimeLapse] [int] NOT NULL ,
> > [TimeLapseMeasurement] [varchar] (2) COLLATE
> > SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [RetryTimeLapse] [int] NOT NULL ,
> > [RetryTimeLapseMeasurement] [varchar] (2) COLLATE
> > SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [RetainHistoryNum] [int] NOT NULL ,
> > [ObjectDependencies] [varchar] (300) COLLATE
> > SQL_Latin1_General_CP1_CI_AS NOT NULL
> > ) ON [PRIMARY]
> > GO
> > CREATE TABLE [dbo].[ScheduleHistory] (
> > [ScheduleHistoryID] [int] IDENTITY (1, 1) NOT NULL ,
> > [ScheduleID] [int] NOT NULL ,
> > [StartDate] [datetime] NOT NULL ,
> > [EndDate] [datetime] NULL ,
> > [Succeeded] [bit] NULL ,
> > [LogNotes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [NextStart] [datetime] NULL
> > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> > GO
> > INSERT INTO Schedule VALUES ('Test1', 1, 'd', 1, 'm', 5, '')
> > INSERT INTO Schedule VALUES ('Test2', 1, 'd', 1, 'm', 3, '')
> > INSERT INTO ScheduleHistory VALUES (1,'1/1/2000 1:00 PM', '1/1/2000
> > 1:01 PM', 1, '', '1/2/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/1/2000 1:00 PM', '1/1/2000
> > 1:01 PM', 1, '', '1/2/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/2/2000 1:00 PM', '1/2/2000
> > 1:01 PM', 1, '', '1/3/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/2/2000 1:00 PM', '1/2/2000
> > 1:01 PM', 1, '', '1/3/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/3/2000 1:00 PM', '1/3/2000
> > 1:01 PM', 1, '', '1/4/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/3/2000 1:00 PM', '1/3/2000
> > 1:01 PM', 1, '', '1/4/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/4/2000 1:00 PM', '1/4/2000
> > 1:01 PM', 1, '', '1/5/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/4/2000 1:00 PM', '1/4/2000
> > 1:01 PM', 1, '', '1/5/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/5/2000 1:00 PM', '1/5/2000
> > 1:01 PM', 1, '', '1/6/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/5/2000 1:00 PM', '1/5/2000
> > 1:01 PM', 1, '', '1/6/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/6/2000 1:00 PM', '1/6/2000
> > 1:01 PM', 1, '', '1/7/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/6/2000 1:00 PM', '1/6/2000
> > 1:01 PM', 1, '', '1/7/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/7/2000 1:00 PM', '1/7/2000
> > 1:01 PM', 1, '', '1/8/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/7/2000 1:00 PM', '1/7/2000
> > 1:01 PM', 1, '', '1/8/2000 1:00 PM')