WHERE [Column] Has Not Changed in N Time

Question

So, I've got a view that is admittedly not well-indexed and there's not much I can do about it.

The view has data that looks a bit like the one in this question, but my problem is essentially the opposite of theirs and I'm not sure their solution will work here, though a similar TVF or CTE is probably in the forecast.

My data looks like this at the moment:

CustomPollerAssignmentId     DateTime                Status
[Some Id B]                    2013-11-18 08:54:00   IDLE
[Some Id A]                    2013-11-18 08:54:00   DORMANT
[Some Id B]                    2013-11-18 08:53:00   IDLE
[Some Id A]                    2013-11-18 08:53:00   NOMINAL

Unlike the other question, I need to see that the status hasn't changed. The view comprises three separate tables. One with minute statistics, one with fifteen minute statistics (for between three and six months ago), and one for hourly statistics (for up to a year ago).

The goal here is to check which modems have been idle for at least the last 10 minutes. We've got about 1200 active modems, so this could be up to 12000 rows, which is why I'd prefer not to do it with C#, but I'm still kind of new to SQL and set-based thinking. I'm currently working with an instance of SQL Server 2012, but it's very new here and I'm not really experienced with the newer windowed functions since we were on 2008R2 until about a month ago.

To be honest, I'm not even sure where to begin here because my OOP background wants me to just grab the TOP 10 statuses for each and loop through. If all 10 == idle || dormant, add to the result set, but I know there's got to be a better way to do it in SQL. Can someone point me in the right direction?

EDIT

To try to clarify a bit:

1. I'm using T-SQL.

2. This isn't as simple as a WHERE NOT EXISTS clause.

3. Regardless of whether or not the status has changed, there should be an entry for the remote's status unless it has been deactivated. This means that it could have (idle, idle, idle, idle, idle, nominal, idle, idle, idle, idle) statuses for the last 10 minutes and that example is a case I would not want to include. The result set should include ONLY those remotes which have had statuses which are only idle or dormant for the last 10 minutes. If the last status is more than three months ago, it will only have one status for a fifteen minute interval.

Answer 1

If i understand correctly what you're asking:

SELECT [theView].CustomPollerAssignmentId
FROM
[theView]
LEFT JOIN
(
  SELECT CustomPollerAssignmentId, MAX([DateTime]) AS LastTime
  FROM [theView]
  WHERE [Status] <> 'IDLE' AND [DateTime] <= @Now
  GROUP BY CustomPollerAssignmentId
) AS NotIdleStatus ON
  [theView].CustomPollerAssignmentId = NotIdleStatus.CustomPollerAssignmentId
WHERE
  [theView].[DateTime] <= @Now AND
  [theView].[Status] = 'IDLE' AND
  (
    [theView].[DateTime] > NotIdleStatus.LastTime OR
    NotIdleStatus.LastTime IS NULL
  )
GROUP BY [theView].CustomPollerAssignmentId
  HAVING MIN([theView].[DateTime]) <= DATEADD(MINUTE, -10, @Now)    

The concept here is to

1. For each ID, select the latest time for non-idle status up till the current time.
2. For each ID, join idle status against the latest non-idle time.
3. Group the status by ID.
4. Filter for those that have been idle before 10 minutes ago.
5. Also filter for those that are either idle later than its latest non-idle status or does not have non-idle status.

The following code selects the last non-idle time for each of IDs:

SELECT CustomPollerAssignmentId, MAX([DateTime]) AS LastTime
FROM [theView]
WHERE [Status] <> 'IDLE' AND [DateTime] <= @Now
GROUP BY CustomPollerAssignmentId

We are using LEFT JOIN so that any status that is IDLE but never in any other status is still captured. The ON clause joins them by ID.

The following code selects those that are idle and is before the current time:

WHERE
  [theView].[DateTime] <= @Now AND
  [theView].[Status] = 'IDLE' AND ...

The following code groups the records by ID and selects the ID that has the earliest time earlier than 10 minutes before the current time:

GROUP BY [theView].CustomPollerAssignmentId
  HAVING MIN([theView].[DateTime]) <= DATEADD(MINUTE, -10, @Now)    

Also, you will need to pass in the @Now value, which would be the current time that you want to check.

Answer 2

SELECT v1.*
FROM (
    SELECT *
    FROM vdata
    WHERE OnlineStatus IN ('IDLE', 'DORMANT')
) v1
WHERE NOT EXISTS (
    SELECT 1
    FROM vdata v2
    WHERE v2.ModemId = v1.ModemId
    AND v2.MinuteMarker > v1.MinuteMarker 
    AND v2.MinuteMarker <= DATEADD(MINUTE, 10, v1.MinuteMarker )
    AND v2.OnlineStatus NOT IN ('IDLE', 'DORMANT')
)