Chris Bilson give a nearly complete answer of how to find the valid / invalid commands for an inmate in the comments of the post. It is a very elegant solution, in my opinion. I took upon myself to improve his solution a bit. The result is this query:
command.IssuedAt AS Start,
dbo.CommandEnds(command.CommandType, command.ValidFor, command.IssuedAt) AS [End],
nextCommand.IssuedAt AS StartOfNextCommand,
dbo.CommandEnds(command.CommandType, command.ValidFor, command.IssuedAt) ,
nextCommand.IssuedAt) > 0 THEN 'Gap In Commands!'
WHEN getdate() > dbo.CommandEnds(command.CommandType, command.ValidFor, command.IssuedAt)
AND nextCommand.Id is null THEN 'Command ended!'
ELSE 'Fine' END AS Status
FROM dbo.Commands command INNER JOIN dbo.Inmates inmate
ON command.InmateID = inmate.ID
LEFT OUTER JOIN dbo.Commands nextCommand
ON nextCommand.InmateID = inmate.Id
AND nextCommand.IssuedAt = (SELECT MIN(IssuedAt)
FROM dbo.Commands c3
WHERE c3.InmateId = inmate.Id
AND c3.IssuedAt > command.IssuedAt )
ORDER BY inmate.Id, Start, [End]
The nice part about this solution that how the query match a command with the (logical) next command. Here is the result of the query:
|Bad||Man||2/5/2006 6:39 PM||10/5/2006 6:39 PM||10/5/2006 6:40 PM||Gap In Commands!|
|Bad||Man||10/5/2006 6:40 PM||11/5/2006 6:40 PM|| |
|Nasty||Guy||3/5/2006 12:00 AM||8/5/2006 12:00 AM||7/5/2006 5:00 PM||Fine|
|Nasty||Guy||7/5/2006 5:00 PM||9/5/2006 5:00 PM|| |
I wish that I had something similar when I needed to do this kind of software when I needed it.