Get records which met SELECT condition over time and range them by period of time when condition was met
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 _History periodically.
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 &lt; (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
      ,record_id &lt;datatype&gt;
      ,status &lt;datatype&gt;
      ,first_seen datetime
      ,times_seen INT DEFAULT 0
     );


where 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 (@CurTime);
Selects all records currently in a status you're interested in into #StatusDuration; set first_seen to @CurTime;
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 record_id and status, updating the temp table's first_seen and times_seen with the values from StatusDuration_Active.
TRUNCATE TABLE StatusDuration_Active
Insert all records in #StatusDuration into StatusDuration_Active; set last_seen to @CurTime, and times_seen to 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.