No, from what you describe, I think that you'll wind up having to track these using some secondary tables.
It sounds like you want to know how long record X stayed in a given status, whether that's the latest status or not. From your description, I'm not sure if records can wind up in a given status multiple times, or if hitting any status is a one-time event. That would impact how this needs to be set up.
It also sounds like there are statuses that matter, and others that do not matter. Hopefully, if there are a large number of records, most records should wind up in statuses that you're not interested in.
So, you'd want to do something like the following:
- Create a table to hold current status info (let's call that
StatusDuration_Active), and a parallel table to keep historical info (
StatusDuration_History). Let's assume it will have at least the following fields:
- a unique ID for each of the above tables (and I'd make this an IDENTITY column). The
_History ID will be unconnected to the
_Active ID (that is to say, when a record moves to
_History, it will get a new ID). Unique IDs are useful in case you need to delete records, among other things - and you will want to delete records from
record_id - this will hold the primary key value from the table you're tracking. It can be made a foreign key, but might be useful without being one. Presumably, you'd want to know about a long duration in a given status, even if the record was deleted afterwards.
status - the status value from the table you're tracking.
first_seen - datetime (or datetime2) column holding the time when the records was seen in this status, when it hadn't been in this status at the previous check.
last_seen - same datatype as previous; the last check when the record was still in this status.
times_seen - integer value showing the number of consecutive times your process saw this record at this status. Useful for a cross-check against the first and last times, in case your process that checks these goes offline at some point (accidentally or on purpose); you could add code to remove or warn if
times_seen < (DATEDIFF(minute, first_seen, last_seen) - 10), for instance.
Create a procedure that:
Create a temp table as follows:
CREATE TABLE #StatusDuration
( tmp_id INT IDENTITY(1,1) NOT NULL
,times_seen INT DEFAULT 0
record_id will hold the primary key of the table you're tracking, and
status will hold the current status from that table.
- Selects the current time into a variable (
- Selects all records currently in a status you're interested in into
- Copies all records from
StatusDuration_Active where the record ID and status are not in the temp table into
StatusDuration_History; these rows should be copied exactly as is;
- Updates all records in
#StatusDuration that are in
StatusDuration_Active with the same
status, updating the temp table's
times_seen with the values from
TRUNCATE TABLE StatusDuration_Active
- Insert all records in
times_seen + 1
- Set up a job to run the procedure every minute.
Depending on the number of records returned, you could delete records from
StatusDuration_Active that got moved to
_History, update records that are already present, and insert new records, instead of the
TRUNCATE TABLE method. However, if there are a large number of records to be deleted, this might be the fastest method to use.
You'll need to confirm that all this runs within a minute (or schedule it to run less frequently). I'd expect it to be OK, but I'm not sure how many records you'll have.
Note that the
TRUNCATE TABLE on
StatusDuration_Active will reset the
IDENTITY value back to 1 (or whatever you started with) - one of the reasons the IDs shouldn't track over). An ID column on
#StatusDuration may actually be unnecessary; I include them wherever possible, because sometimes they are necessary.
As noted, you'll want to clear the oldest rows (optionally keeping the most interesting ones, cases where the statuses remained at a particular duration for longer than normal) from
StatusDuration_History, or it will eat up a lot of space, and will take longer and longer to query (and, depending on how you index it, possibly longer and longer to insert into, which may be more of a concern).
When reporting, you may want to use the
WITH (NOLOCK) table hint, as both tables will be updated once a minute, and you don't want to interfere with that. If your queries run in a few seconds, and your procedure does as well, then no major worries. If you do use the table hint, note that it'll be possible to hit a row that's in
_Active, and to hit the same row because it's been copied to
_History. I'd keep the possibility in mind; you could use
UNION instead of
UNION ALL to combine the data from the two tables to work around this, but I'd not worry about it unless you see it happening and notably affecting your results.