remove duplicated fields depending on time

[parabolam]

hello,

i am new enough to sql and looking for help with the following..

I have a table with duplicate record information in some fields which i dont want to show. I pull alarm data from a database and it gives me all the alarms that occur with the following columns..


Tool time_occured alarmtext


When I run the query say it gives me say 5 alarms….


Tool time_occured alarmtext

#1 12:20 DOOR OPEN
#1 12:21 DOOR OPEN
#1 12:30 DOOR OPEN
#2 12:20 DOOR OPEN
#2 12:24 DOOR OPEN

What I need is the table to show the following…

First i want to compare the tool number and if this is the same.. then i want to compare the alarmtext and if this is the same... then i want to compare the times... now if the times are within 5 mins of each other i only want to show the first time occured..

this is what i want the table to show

Tool time_occured alarmtext

#1 12:20 DOOR OPEN
#1 12:30 DOOR OPEN
#2 12:20 DOOR OPEN

thanks

[balame2004]

I don't think it is easy to do with siongle query. Better to think about a stored procedure to do it.

[parabolam]

any ideas of a stored procedure i could use?

[ck9663]

Here, try this. I included some display queries...

 
declare @tblAlarm table (Tool varchar(2), time_occured varchar(5), alarmtext varchar(10))
 
insert into @tblAlarm (Tool, time_occured, alarmtext)
values ('#1', '12:20', 'DOOR OPEN')
insert into @tblAlarm (Tool, time_occured, alarmtext)
values ('#1', '12:21', 'DOOR OPEN')
insert into @tblAlarm (Tool, time_occured, alarmtext)
values ('#1', '12:30', 'DOOR OPEN')
insert into @tblAlarm (Tool, time_occured, alarmtext)
values ('#2', '12:20', 'DOOR OPEN')
insert into @tblAlarm (Tool, time_occured, alarmtext)
values ('#2', '12:24', 'DOOR OPEN')
 
select tool, time_occured, cast(time_occured as datetime), alarmtext from @tblAlarm 
 
select a1.tool, a1.time_occured, a1.alarmtext, 
prev_alarm = (select top 1 a2.time_occured from @tblAlarm a2 where a1.tool = a2.tool and cast(a1.time_occured as smalldatetime) > cast(a2.time_occured as smalldatetime) order by cast(a2.time_occured as smalldatetime) desc)
from @tblAlarm a1
 
 
select * from
(select a1.tool, a1.time_occured, a1.alarmtext, 
prev_alarm = (select top 1 a2.time_occured from @tblAlarm a2 where a1.tool = a2.tool and cast(a1.time_occured as smalldatetime) > cast(a2.time_occured as smalldatetime) order by cast(a2.time_occured as smalldatetime) desc)
from @tblAlarm a1) alarms
where prev_alarm is null or datediff(mi,cast(prev_alarm as smalldatetime), cast(time_occured as smalldatetime)) > 5
 
 

Happy Coding!

--- CK