chester c young wrote:
--- Bob Singleton <[EMAIL PROTECTED]> wrote:
Revisiting a Time In Status query I received help on - I'm trying to
narrow down a subset of data I return for analysis.
Given a statusLog as entityId, statusId, timestamp that might look
something like
entityId | statusId | timestamp
--------------------------------------------
001 | HLD | 2007-06-14 11:07:35.93
001 | RDY | 2007-06-15 11:07:35.93
001 | USE | 2007-06-16 11:07:35.93
001 | RDY | 2007-06-17 11:07:35.93
001 | MNT | 2007-06-18 11:07:35.93
I need to pull for a given span of time - say 2007-06-16 00:00:00.01
(let me call it startTime) to 2007-06-17 23:59:59.99 (call it
endTime)
in such a way that rows with a timestamp between startTime and
endTime
AND the latest record prior to or equal to startTime are returned. In
the above simplified example, only the second and third rows would be
returned.
A colleague suggested a temp table, but I'm unsure how to iterate
until
I pass the startTime and then write the _previous_ and all subsequent
rows to a temp table, stopping when I pass the endTime parameter.
Any hints?
Thanks!
Bob Singleton
couldn't you use the simple query:
select * from sometable
where timestamp between
(select max(timestamp) from sometable where timestamp <= minTime)
and maxTime
This works very well unless I have no records where timestamp <=
minTime. If I try
select max(timestamp) from sometable where timestamp <= minTime;
I get
max
-----
(1 row)
And the blank row/line/value/? confuses the between call - I get 0 rows
returned.
(if minTime is later than at least one row's timestamp I will see
something like)
max
------------------------
2007-06-08 17:42:00.18
(1 row)
Any help greatly appreciated!
bs
|
