EXPERT RESPONSE
To calculate the days on hold, all you really need is the
date it went on hold, and the date of the first status change
that occurred afterwards. Let's say that the timestamp column
is an INTEGER datatype and contains UNIX timestamp values.
(DATETIME is the more common datatype, but the exact SQL syntax
involving date calculations depends on whichever database
system you're using, and the important part of this problem
is the correlated subquery, not the specifics of the date subtraction.)
select h.orderno
, h.status_timestamp
, x.status
, x.status_timestamp
, ( x.status_timestamp
- h.status_timestamp ) /86400
as days_on_hold
from yourtable as h
inner
join yourtable as x
on x.orderno = h.orderno
and x.status_timestamp =
( select min(status_timestamp)
from yourtable
where orderno = h.orderno
and status_timestamp > h.status_timestamp )
where h.status = 'On Hold'
The subquery chooses the first row that comes after
the On Hold row for each order that has an On Hold status.
Two things to note. First, no check is made on the
x.status value, so it basically calculates the days to whatever the
next transaction is, even if it's not a status change. Add a WHERE condition
to both the ON clause of the join and the subquery if x.status must be
one of the three statuses you mentioned.
Secondly, this query does not return orders
which are still on hold, i.e. the subquery finds
no subsequent row. To get those orders, change it to a
LEFT OUTER JOIN and use COALESCE on x.status_timestamp in the SELECT to
substitute the current timestamp value, so that the days_on_hold
value is calculated up to the current date and time.
|
