January 23, 2007 at 1:56 am
I need to remove triplets from a table. The table can contain triplets but
not always. A triplet is duplicates within a 15 second span.
Tips on achieving this very much appreciated.
Script:
Create table:
CREATE TABLE dbo.Statistics2
( UserId integer NOT NULL,
EventDate datetime NOT NULL,
FirstName varchar (80) NOT NULL,
FamilyName varchar (60) NOT NULL)
GO
Insert rows:
insert into dbo.Statistics2 VALUES (4807, '2007-01-17 14:06:38.037','Tor',
'Gunn')
insert into dbo.Statistics2 VALUES (4807, '2007-01-17 14:06:49.333','Tor',
'Gunn')
insert into dbo.Statistics2 VALUES (4807, '2007-01-17 14:06:50.600','Tor',
'Gunn')
insert into dbo.Statistics2 VALUES (4807, '2007-01-17 14:08:35.910','Tor',
'Gunn')
insert into dbo.Statistics2 VALUES (4807, '2007-01-17 14:09:07.643','Tor',
'Gunn')
insert into dbo.Statistics2 VALUES (4807, '2007-01-17 14:09:13.973','Tor',
'Gunn')
insert into dbo.Statistics2 VALUES (4807, '2007-01-17 14:09:15.190','Tor',
'Gunn')
insert into dbo.Statistics2 VALUES (5564, '2007-01-17
14:00:13.757','Wake','Kun')
insert into dbo.Statistics2 VALUES (5564, '2007-01-17
14:05:16.647','Wake','Kun')
insert into dbo.Statistics2 VALUES (5565, '2007-01-17 14:00:14.240','Ben',
'Aske')
insert into dbo.Statistics2 VALUES (5565, '2007-01-17 14:00:17.570','Ben',
'Aske')
insert into dbo.Statistics2 VALUES (5565, '2007-01-17 14:00:18.710','Ben',
'Aske')
After removal these should be left:
VALUES (4807, '2007-01-17 14:06:38.037','Tor', 'Gunn')
VALUES (4807, '2007-01-17 14:08:35.910','Tor', 'Gunn')
VALUES (4807, '2007-01-17 14:09:07.643','Tor', 'Gunn')
VALUES (5564, '2007-01-17 14:00:13.757','Wake','Kun')
VALUES (5564, '2007-01-17 14:05:16.647','Wake','Kun')
VALUES (5565, '2007-01-17 14:00:14.240','Ben', 'Aske')
/m
January 23, 2007 at 3:39 am
It's easier not to let them in.
Set up a trigger which will test if inserted line(s) are allowed to be in the table and remove those which are not.
_____________
Code for TallyGenerator
January 23, 2007 at 5:09 am
Michael
I'm assuming that for any given UserId, FirstName And FamilyName will always be the same. If that's the case, your database isn't properly normalised and you should create a separate table called Users, referenced by the UserId column of Statistics2.
Anyway, try this. Performance will probably be horrible if your table is large - let's hope you have an index on the EventDate column to mitigate this. You may get unexpected results if you have identical value of EventDate for different users - you should test for this before your run the query.
delete
Statistics2
from Statistics2 s2 join
(select s.UserId, s.EventDate
from statistics2 s join statistics2 t
on s.EventDate < dateadd(s, 15, t.EventDate)
and s.EventDate > t.EventDate
and s.UserId = t.UserId) t2
on s2.UserId = t2.UserId
and s2.EventDate = t2.EventDate
Do this once, and then set up the trigger as Sergiy suggests.
John
January 23, 2007 at 5:31 am
Thanks.
Yes, tomorrow I will stop duplicates, they were due to a front end programmer error.
The code is a simplified version from my original table, thats why it looks as it looks.
/m
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply