Hi ALL,
need help

i am one table alarmtest is my test data from which i need result as shown
in 3rd para.
hope you can help.

Select * from alarmtest order by group,stime asc
SELECT FirstTime = MIN(Stime),LastTime = MAX(Etime)FROM alarmtest
GROUP BY gcode

Alarmtest -->

acode gcode stime etime
A01 G01 2004-08-24 11:00:00.000 2004-08-24 11:40:00.000 (get this as
reference for output)
A01 G01 2004-08-24 11:10:00.000 2004-08-24 11:20:00.000 (discard it inside
same time frame)
A01 G01 2004-08-24 11:10:00.000 2004-08-24 11:40:00.000 (need to discard as
falling in same time frame)
A01 G01 2004-08-24 11:20:00.000 2004-08-24 11:30:00.000 (need to discard
this as falling bet same time frame)
A01 G01 2004-08-24 11:30:00.000 2004-08-24 11:50:00.000 (same group but can
take only 10minutes from this)
A01 G01 2004-08-24 12:10:00.000 2004-08-24 12:40:00.000 (same group but
different time slot)
A01 G02 2004-08-24 11:10:00.000 2004-08-24 11:40:00.000 (different group)


Result I Get -->
FirstTime LastTime
2004-08-24 11:10:00.000 2004-08-24 12:40:00.000
2004-08-24 11:10:00.000 2004-08-24 11:40:00.000


Result I want -->
FirstTime LastTime
2004-08-24 11:10:00.000 2004-08-24 11:40:00.000
2004-08-24 12:10:00.000 2004-08-24 12:40:00.000(this record is falling in
diff time slot)
2004-08-24 11:10:00.000 2004-08-24 11:40:00.000

hope you understand what i want ? please advice i need to do it in SQL query
only (procedure,function can be used but no
front end programming)

best regards
jit

MS Sql Server LazyDBA home page