I take it the gcodes would come out like this, had you included them in
select list:
Result I want -->
gcode FirstTime LastTime
G01 2004-08-24 11:10:00.000 2004-08-24 11:40:00.000
G01 2004-08-24 12:10:00.000 2004-08-24 12:40:00.000(this record is
falling in diff time slot)
G02 2004-08-24 11:10:00.000 2004-08-24 11:40:00.000
What is the timeslot grouping rule for the results you want? Do you want
them grouped by the hour as well as by the gcode? If so, what happens to
records that span more than one hour (i.e. 11:50 - 12:10)?
Without this info, impossible to write query
Daniel Morphett
DBA
Brilliant Digital Entertainment
02 9281 9272
-----Original Message-----
From: Singh Jitender
[mailto:mssqldba-ezmlmshield-x92513794.[Email address protected]
Sent: Tuesday, August 24, 2004 3:00 PM
To: LazyDBA Discussion
Subject: Query
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
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
MS Sql Server LazyDBA home page