SQL to calculate days to next status change
I would like to find out how long a particular customer order has been on hold in SQL.
I would like to find out how long a particular customer order has been on hold. The current system allows the status of an order to be placed on hold which gives me the beginning date via the history timestamp. Unfortunately, the only action I can take next is to change the status to something else such as In Progress or Submitted. Since there is no record or timestamp of when an order was removed from the hold status, I have no end date which to calcuate the days on hold. I only have the date of the next status change, which could be any one of three. Is there a way to determine the days on hold without the specific end date?
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.
