Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Duplicate Rows in certain time Expand / Collapse
Author
Message
Posted Monday, September 2, 2013 8:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 6, 2013 5:23 AM
Points: 2, Visits: 4
Hello,

i had this problem allready in a normal SQL Server Version and solve it with CTEs or Cursors.

Now I have to Use Compact SQL .NET 3.5 and i have no idea how to solve it:

If have
Attribute 1
Attribute 2
Attribute x
Timestamp y

If a row have all attributes in common an a timestamp with less then 60 seconds differenze then only the first record should be taken and the other ones delete.
The Table has now Key!

For Example
A, B, C, 02.09.2013 16:06:30
A, B, C, 02.09.2013 16:06:35
A, B, C, 02.09.2013 16:06:30
A, B, X, 02.09.2013 16:06:30
A, B, C, 02.09.2013 16:06:20
A, B, Y, 02.09.2013 16:06:30

Output should be:
A, B, X, 02.09.2013 16:06:30
A, B, C, 02.09.2013 16:06:20
A, B, Y, 02.09.2013 16:06:30

Have someone an idea?

Thx for helping.
Post #1490632
Posted Monday, September 2, 2013 9:43 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, October 13, 2014 4:54 AM
Points: 708, Visits: 3,290
My attempt:

create table test (Attribute1 char(1), attribute2 char(1), attribute3 char(1), thetimestamp datetime)

insert into test(Attribute1, attribute2, attribute3, thetimestamp)
select 'A', 'B', 'C', '2013-09-02 16:06:30' union all
select 'A', 'B', 'C', '2013-09-02 16:06:35' union all
select 'A', 'B', 'C', '2013-09-02 16:06:30' union all
select 'A', 'B', 'X', '2013-09-02 16:06:30' union all
select 'A', 'B', 'C', '2013-09-02 16:06:20' union all
select 'A', 'B', 'Y', '2013-09-02 16:06:30' union all
select 'A', 'B', 'C', '2013-09-02 16:10:00'

select c.Attribute1, c.attribute2, c.attribute3, c.thetimestamp
from test as a
cross apply (select b.Attribute1, b.attribute2, b.attribute3, MIN(thetimestamp) as thetimestamp
from test as b
where a.Attribute1 = b.Attribute1
and a.attribute2 = b.attribute2
and a.attribute3 = b.attribute3
and datediff(ss, b.thetimestamp, a.thetimestamp ) <= 60
group by b.Attribute1, b.attribute2, b.attribute3) as c
group by c.Attribute1, c.attribute2, c.attribute3, c.thetimestamp


select * from test

drop table test



---------------------------------------------------------


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Post #1490658
Posted Tuesday, September 3, 2013 7:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 6, 2013 5:23 AM
Points: 2, Visits: 4
BIG THX, IT WORKS!
Post #1490940
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse