Think this will do it
select * from alarmtest at1
left join (
SELECT a.*
FROM alarmtest a
inner join alarmtest x
on (a.gcode = x.gcode) and (a.stime > x.stime) and (a.stime < x.etime)
and (a.stime != x.stime)
) at2 on at1.gcode = at2.gcode
and at1.stime = at2.stime
and at1.etime = at2.etime
where at2.gcode is null
Daniel Morphett
DBA
Brilliant Digital Entertainment
02 9281 9272
-----Original Message-----
From: Thomas Anthony
[mailto:mssqldba-ezmlmshield-x82731597.[Email address protected]
Sent: Tuesday, August 24, 2004 4:06 PM
To: LazyDBA Discussion
Subject: RE: Query
No, he wants the something akin to the classic Hotel Reservation
problem. If two events overlap, then they are within the same event and
he wants the total duration between the two.
The signal of a new event is when the start time for a new alert is
outside the range of all priors within the group code.
That's fairly straight forward; however, I am hitting upon how to get
the first interval when none have existed prior to it: the seed, if you
will.
Sincerely,
Anthony Thomas, MCDBA, MCSA
-----Original Message-----
From: Daniel Morphett
[mailto:mssqldba-ezmlmshield-x46357662.[Email address protected]
Sent: Tuesday, August 24, 2004 12:55 AM
To: LazyDBA Discussion
Subject: RE: Query
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]
---------------------------------------------------------------------
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]
***********************************************************************
NOTICE: This electronic mail message and any attached files are
confidential. The information is exclusively for the use of the
individual or entity intended as the recipient. If you are not the
intended recipient, any use, copying, printing, reviewing, retention,
disclosure, distribution or forwarding of the message or any attached
file is not authorized and is strictly prohibited. If you have received
this electronic mail message in error, please advise the sender by reply
electronic mail immediately and permanently delete the original
transmission, any attachments and any copies of this message from your
computer system.
***********************************************************************
---------------------------------------------------------------------
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