+972 52-548-6969
The Prison Domain Model Challange: Answers
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.
Comments
Comment preview