## The Prison Domain Model Challange: Answers

time to read 20 min | 3895 words

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:

SELECT

inmate.FirstName,

inmate.Surname,

command.IssuedAt AS Start,

dbo.CommandEnds(command.CommandType, command.ValidFor, command.IssuedAt) AS [End],

nextCommand.IssuedAt AS StartOfNextCommand,

CASE

WHEN DateDiff(ms,

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:

 FirstName Surname Start End StartOfNextCommand Status 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 NULL Fine 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 NULL Fine

I wish that I had something similar when I needed to do this kind of software when I needed it.