Keep most recent entries in a history table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Keep most recent entries in a history table

Author

Topic

tomt00001
Starting Member

5 Posts

Posted - 2004-06-16 : 14:41:23

Hi All,

I have a question, first I'll lay out the SQL schema, then describe the problem I am trying to resolve.

CREATE TABLE tSD_History (
S_iStudentID int NOT NULL,
U_iActionPersonID int NOT NULL,
H_dEvent datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
HT_iTypeID int NULL,
DDDS_iDDocumentID int NULL,
DPDDP_iDPageID int NULL
)
go

INSERT INTO tSD_History (S_iStudentID,U_iActionPersonID,H_sAction) VALUES ('1','1','Action1-01')
INSERT INTO tSD_History (S_iStudentID,U_iActionPersonID,H_sAction) VALUES ('1','1','Action1-02')
INSERT INTO tSD_History (S_iStudentID,U_iActionPersonID,H_sAction) VALUES ('1','1','Action1-03')
INSERT INTO tSD_History (S_iStudentID,U_iActionPersonID,H_sAction) VALUES ('1','1','Action1-04')
INSERT INTO tSD_History (S_iStudentID,U_iActionPersonID,H_sAction) VALUES ('1','1','Action1-05')
INSERT INTO tSD_History (S_iStudentID,U_iActionPersonID,H_sAction) VALUES ('1','1','Action1-06')
INSERT INTO tSD_History (S_iStudentID,U_iActionPersonID,H_sAction) VALUES ('1','1','Action1-07')
INSERT INTO tSD_History (S_iStudentID,U_iActionPersonID,H_sAction) VALUES ('1','1','Action1-08')
INSERT INTO tSD_History (S_iStudentID,U_iActionPersonID,H_sAction) VALUES ('1','1','Action1-09')
INSERT INTO tSD_History (S_iStudentID,U_iActionPersonID,H_sAction) VALUES ('1','1','Action1-10')
INSERT INTO tSD_History (S_iStudentID,U_iActionPersonID,H_sAction) VALUES ('1','1','Action1-11')
INSERT INTO tSD_History (S_iStudentID,U_iActionPersonID,H_sAction) VALUES ('1','1','Action1-12')
INSERT INTO tSD_History (S_iStudentID,U_iActionPersonID,H_sAction) VALUES ('1','1','Action1-13')
INSERT INTO tSD_History (S_iStudentID,U_iActionPersonID,H_sAction) VALUES ('1','1','Action1-14')
INSERT INTO tSD_History (S_iStudentID,U_iActionPersonID,H_sAction) VALUES ('1','1','Action1-15')
INSERT INTO tSD_History (S_iStudentID,U_iActionPersonID,H_sAction) VALUES ('1','1','Action1-16')
INSERT INTO tSD_History (S_iStudentID,U_iActionPersonID,H_sAction) VALUES ('1','1','Action1-17')

This is a history table as the name implies it tracks events that take place on another table. There is no PRIMARY key.

Want I want to do is keep only the 5 most recent events(Timestamp column H_dEvent.)

How do I accomplish this via TSQL? Do I have to have a primary key in order to do this?

Thanks
Tom

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-16 : 14:52:07

Maybe...

Delete tSD_History
from tSD_History
left Join
(Select top 5 * From tSD_History Order By h_dEvent Desc) as A
On tSD_History.h_dEvent = A.h_dEvent
and tSD_History.s_iStudentID = A.s_iStudentID
and tSD_History.U_iActionPersonID = A.U_iActionPersonID
and tSD_History.H_sAction = A.H_sAction
Where A.h_dEvent is null

I know you don't have a primary key, but what are the odds??

Corey

tomt00001
Starting Member

5 Posts

Posted - 2004-06-16 : 15:13:40

Thanks for the reply

With a few modifications that will accomplish what I am trying to do.

Would having a primary key make this easier? I hadn't put a primary key because when I use the data in this table I am going to be selecting based on one of the fields, using TOP and ordering by the date descending.

Tom

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-16 : 15:18:15

Primary keys would make the join simplier and most likely faster. you could just use an identity field...

create table blah
(
id int identity(1,1) not null,
....
)

actually if you did that the last 5 posts would be the 5 greatest ids...

Delete From yourtable Where id < (Select max(id) From yourtable) - 5
or maybe -4... i dunno

Corey

tomt00001
Starting Member

5 Posts

Posted - 2004-06-16 : 15:37:57

Hmm, not a bad idea. The thing I was thinking about was that an identity field would get huge(numberwise) because the numbers would not be reused. Current count is that this table will get 300,000 entries in a couple of months but they will be trimmed constantly.

I want to track history on a particular S_iStudentID so the query will include a S_iStudentID = 'Value' to limit it to work with a particular ID.
Is a identity field pratical in this case?

The plan is to track the events surrounding a particular ID but only keep 5 of them so the table didn't get huge.

Thanks
Tom

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-16 : 15:54:20

well you might be alright with studentId and h_dEvent, how does stuff get inserted into the table??

I use a query to 'reuse' id numbers when the become available. Dependending on how the insert goes you could do that. It would atleast allow you create a pk. You would then go back to the original suggestion though if you are reusing ids

Corey

tomt00001
Starting Member

5 Posts

Posted - 2004-06-16 : 16:13:17

Excellent idea, got an example of how to do this?

Tom

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-16 : 16:37:16

My example assumes you won't store more than 100000 ids at a given time
if you only need 10000 at a time you could reduce the sequence section to 4 instances.

Create table #seq (n int Primary key)
Insert Into #seq Values (0)
Insert Into #seq Values (1)
Insert Into #seq Values (2)
Insert Into #seq Values (3)
Insert Into #seq Values (4)
Insert Into #seq Values (5)
Insert Into #seq Values (6)
Insert Into #seq Values (7)
Insert Into #seq Values (8)
Insert Into #seq Values (9)

create Table #myTable (id int primary key, entryDate datetime default(getdate()), data nvarchar(100))

Insert into #myTable (id, data) Values (1,'blah1')
Insert into #myTable (id, data) Values (2,'blah2')
Insert into #myTable (id, data) Values (3,'blah3')
Insert into #myTable (id, data) Values (4,'blah4')
Insert into #myTable (id, data) Values (5,'blah5')
Insert into #myTable (id, data) Values (6,'blah6')

Select * From #myTable

Delete From #myTable Where id = 3

--Insert Into @myTable (id,data)
Select
id =
(
Select min(nSeq.id)
From
(
Select id = (a.n*10000)+(b.n*1000)+(c.n*100)+(d.n*10)+(e.n)
From #seq as a, #seq as b, #seq as c, #seq as d, #seq as e
) as nSeq
Left Join #myTable as yah
On nSeq.id = yah.id
Where nSeq.id > 0
and yah.id is null
),
data = 'blah7'

Drop Table #seq
Drop Table #myTable

Corey

tomt00001
Starting Member

5 Posts

Posted - 2004-06-16 : 18:19:11

Heres what I came up with, I refined what you posted plus looked at a couple of other posts and borrow liberally from them.

I am using a PK like you suggested and to get the PK for each row inserted I am using a UDF to find a value that is not being used.

To recap for others browsing this topic, I wanted to keep history in a table but only keep 5 entries for each S_iStudentID in the table. The follow schema and code accomplishes this

CREATE TABLE tSD_History (
H_iID int NOT NULL,
S_iStudentID int NOT NULL,
U_iActionPersonID int NOT NULL,
H_dEvent datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
HT_iTypeID int NULL,
DDDS_iDDocumentID int NULL,
DPDDP_iDPageID int NULL
)
go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fSD_History_GetNextID]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fSD_History_GetNextID]
GO
CREATE FUNCTION fSD_History_GetNextID()
RETURNS int
AS
BEGIN
declare @i int

select TOP 1 @i = (v2+(v1-v2-1)) from
(
SELECT x.H_iID v1, (select max(y.H_iID) from tSD_History y where y.H_iID < x.H_iID) as v2
from tSD_History x
) a
where v1 <> v2+1

SELECT @i = ISNULL(@i,(SELECT top 1 H_iID+1 from tSD_History order by H_iID desc))

SELECT @i = ISNULL(@i,1)
RETURN(@i)
END

INSERT INTO tSD_History (H_iID,S_iStudentID,U_iActionPersonID) VALUES (dbo.fSD_History_GetNextID(),'1','1')
INSERT INTO tSD_History (H_iID,S_iStudentID,U_iActionPersonID) VALUES (dbo.fSD_History_GetNextID(),'1','1')
INSERT INTO tSD_History (H_iID,S_iStudentID,U_iActionPersonID) VALUES (dbo.fSD_History_GetNextID(),'1','1')
INSERT INTO tSD_History (H_iID,S_iStudentID,U_iActionPersonID) VALUES (dbo.fSD_History_GetNextID(),'1','1')
INSERT INTO tSD_History (H_iID,S_iStudentID,U_iActionPersonID) VALUES (dbo.fSD_History_GetNextID(),'1','1')
INSERT INTO tSD_History (H_iID,S_iStudentID,U_iActionPersonID) VALUES (dbo.fSD_History_GetNextID(),'1','1')
INSERT INTO tSD_History (H_iID,S_iStudentID,U_iActionPersonID) VALUES (dbo.fSD_History_GetNextID(),'1','1')
INSERT INTO tSD_History (H_iID,S_iStudentID,U_iActionPersonID) VALUES (dbo.fSD_History_GetNextID(),'1','1')
INSERT INTO tSD_History (H_iID,S_iStudentID,U_iActionPersonID) VALUES (dbo.fSD_History_GetNextID(),'1','1')
INSERT INTO tSD_History (H_iID,S_iStudentID,U_iActionPersonID) VALUES (dbo.fSD_History_GetNextID(),'1','1')
INSERT INTO tSD_History (H_iID,S_iStudentID,U_iActionPersonID) VALUES (dbo.fSD_History_GetNextID(),'1','1')
INSERT INTO tSD_History (H_iID,S_iStudentID,U_iActionPersonID) VALUES (dbo.fSD_History_GetNextID(),'1','1')
INSERT INTO tSD_History (H_iID,S_iStudentID,U_iActionPersonID) VALUES (dbo.fSD_History_GetNextID(),'1','1')
INSERT INTO tSD_History (H_iID,S_iStudentID,U_iActionPersonID) VALUES (dbo.fSD_History_GetNextID(),'1','1')
INSERT INTO tSD_History (H_iID,S_iStudentID,U_iActionPersonID) VALUES (dbo.fSD_History_GetNextID(),'1','1')
INSERT INTO tSD_History (H_iID,S_iStudentID,U_iActionPersonID) VALUES (dbo.fSD_History_GetNextID(),'1','1')
INSERT INTO tSD_History (H_iID,S_iStudentID,U_iActionPersonID) VALUES (dbo.fSD_History_GetNextID(),'2','2')
INSERT INTO tSD_History (H_iID,S_iStudentID,U_iActionPersonID) VALUES (dbo.fSD_History_GetNextID(),'2','2')
INSERT INTO tSD_History (H_iID,S_iStudentID,U_iActionPersonID) VALUES (dbo.fSD_History_GetNextID(),'2','2')
INSERT INTO tSD_History (H_iID,S_iStudentID,U_iActionPersonID) VALUES (dbo.fSD_History_GetNextID(),'2','2')

DELETE tSD_History
FROM
tSD_History h
WHERE
S_iStudentID = '1'
AND
H_iID NOT IN
(
SELECT
TOP 5 H_iID
FROM tSD_History
WHERE S_iStudentID = h.S_iStudentID
ORDER BY H_dEvent DESC
)

Note: None of this has been beautified at all its just posted in a working state.

Thanks for all the help
Tom

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-16 : 22:30:00

Any chance you could explain your UDF?? I don't understand what you are doin to get the new value...

you have: (v2+(v1-v2-1))
which is: (v2+(v1~~-v2~~-1)) = v1-1

Doesn't make a whole lot of sense to me...

Corey

Kristen
Test

22859 Posts

Posted - 2004-06-17 : 07:14:25

As an idea perhaps you could UPDATE the oldest row if 5, or more, rows exist, otherwise INSERT a new row

Kristen

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-17 : 08:26:27

i think i like that idea...

yeah Kristen

Corey

Kristen
Test

22859 Posts

Posted - 2004-06-17 : 13:43:06