By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,413 Members | 1,594 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,413 IT Pros & Developers. It's quick & easy.

remove duplicated fields depending on time

P: 2
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
May 14 '09 #1
Share this Question
Share on Google+
3 Replies


100+
P: 142
I don't think it is easy to do with siongle query. Better to think about a stored procedure to do it.
May 14 '09 #2

P: 2
any ideas of a stored procedure i could use?
May 15 '09 #3

ck9663
Expert 2.5K+
P: 2,878
Here, try this. I included some display queries...

Expand|Select|Wrap|Line Numbers
  1.  
  2. declare @tblAlarm table (Tool varchar(2), time_occured varchar(5), alarmtext varchar(10))
  3.  
  4. insert into @tblAlarm (Tool, time_occured, alarmtext)
  5. values ('#1', '12:20', 'DOOR OPEN')
  6. insert into @tblAlarm (Tool, time_occured, alarmtext)
  7. values ('#1', '12:21', 'DOOR OPEN')
  8. insert into @tblAlarm (Tool, time_occured, alarmtext)
  9. values ('#1', '12:30', 'DOOR OPEN')
  10. insert into @tblAlarm (Tool, time_occured, alarmtext)
  11. values ('#2', '12:20', 'DOOR OPEN')
  12. insert into @tblAlarm (Tool, time_occured, alarmtext)
  13. values ('#2', '12:24', 'DOOR OPEN')
  14.  
  15. select tool, time_occured, cast(time_occured as datetime), alarmtext from @tblAlarm 
  16.  
  17. select a1.tool, a1.time_occured, a1.alarmtext, 
  18. 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)
  19. from @tblAlarm a1
  20.  
  21.  
  22. select * from
  23. (select a1.tool, a1.time_occured, a1.alarmtext, 
  24. 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)
  25. from @tblAlarm a1) alarms
  26. where prev_alarm is null or datediff(mi,cast(prev_alarm as smalldatetime), cast(time_occured as smalldatetime)) > 5
  27.  
  28.  
Happy Coding!

--- CK
May 15 '09 #4

Post your reply

Sign in to post your reply or Sign up for a free account.