September 2, 2013 at 8:12 am
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.
September 2, 2013 at 9:43 am
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[/url]
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
September 3, 2013 at 7:10 am
BIG THX, IT WORKS!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply