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.

Tweet Share Share 0 comments
Tags: