Steffen Mangold wrote:
> this won't work
It works for what you've asked. Perhaps you should not have kept your
actual requirements a secret.
> if the table look like this:
>
> ID | TimeStamp | Status
> ----------------------------------------------------
> 0 | 2012-07-24 22:23:00 | status1
> 1 | 2012-07-24 22:23:05 | status1
> 2 | 2012-07-24 22:23:10 | status2
> 3 | 2012-07-24 22:23:16 | status2
> 4 | 2012-07-24 22:23:21 | status1
> 5 | 2012-07-24 22:23:26 | status1
> 6 | 2012-07-24 22:23:32 | status2
> 7 | 2012-07-24 22:23:37 | status3
> 8 | 2012-07-24 22:23:42 | status3
> 9 | 2012-07-24 22:23:47 | status3
>
> Than you get:
> ID | Begin | End | Status
>
> -------------------------------------------------------------------------------
> 0 | 2012-07-24 22:23:00 | 2012-07-24 22:23:26 | status1
> 1 | 2012-07-24 22:23:10 | 2012-07-24 22:23:32 | status2
> 2 | 2012-07-24 22:23:37 | 2012-07-24 22:23:47 | status3
>
> But it should be:
> ID | Begin | End | Status
>
> -------------------------------------------------------------------------------
> 0 | 2012-07-24 22:23:00 | 2012-07-24 22:23:05 | status1
> 0 | 2012-07-24 22:23:10 | 2012-07-24 22:23:16 | status2
> 1 | 2012-07-24 22:23:21 | 2012-07-24 22:23:26 | status1
> 2 | 2012-07-24 22:23:37 | 2012-07-24 22:23:47 | status3
So you want to group only consecutive events with identical statuses,
where "consecutive" is defined on the ordering of the timestamps?
> You are right the ID column is not relevant. You can ignore them.
Then why do you write them? (And why are there duplicates in your last
example?)
Well, try this:
SELECT TimeStamp AS Begin,
(SELECT MAX(TimeStamp)
FROM Data AS same
WHERE same.Status = ou.Status
AND same.TimeStamp >= ou.TimeStamp
AND same.TimeStamp < (COALESCE((SELECT MIN(TimeStamp)
FROM Data AS next
WHERE next.TimeStamp > ou.TimeStamp
AND next.Status <> ou.Status),
'9999'))
) AS End,
Status
FROM Data AS ou
WHERE Status IS NOT (SELECT Status
FROM (SELECT Status,
MAX(prev.TimeStamp)
FROM Data AS prev
WHERE prev.TimeStamp < ou.TimeStamp))
(And it might be easier and faster to just query the events ordered by
timestamp, and aggregate statuses by hand in your code.)
Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
