The Prison Domain Model
Here
is an interesting challenge. Below is a highly simplified database diagram for a
prison. The idea is that the Inmates details are in the Inmates table (duh!),
and the Commands table hold the authorization to hold the inmate in prison.
It
is highly simplified because there are about fifteen different commands, each
with strange and stranger semantics, but we will concentrate on the challenge.
For
the purpose of discussion, there are three types of commands:
- Hold
the inmate for a specified number of days, starting from the issued date
- Hold
the inmate for a specified number of hours, starting from the issued hour
- Hold
the inmate until told otherwise.
Commands
can overlap and (in general, but not always) in this case, the more recent one
wins.
Here
is a sample query to pull all the commands history for all inmates. The
CommandEnds() function calculate when the command is no longer valid.
SELECT
Inmates.Id,
Inmates.FirstName,
Inmates.Surname,
Commands.Id AS [Commands.Id],
CommandsTypes.Description AS [Commands.Description],
Commands.ValidFor as [Commands.ValidFor],
Commands.IssuedAt as [Commands.IssuedAt],
dbo.CommandEnds(Commands.CommandType, Commands.ValidFor, Commands.IssuedAt) as
[Commands.Until]
FROM Inmates
INNER JOIN Commands
ON Inmates.Id =
Commands.InmateId
INNER JOIN CommandsTypes
ON Commands.CommandType = CommandsTypes.Id
The output of the above query is something like
this:
Id |
FirstName |
Surname |
Commands.Id |
Commands.Description |
Commands.ValidFor |
Commands.IssuedAt |
Commands.Until |
1 |
Bad |
Man |
1 |
Hold - Days |
8 |
2/5/2006 6:39 PM |
10/5/2006 6:39 PM |
1 |
Bad |
Man |
2 |
Hold - Hours |
24 |
10/5/2006 6:40 PM |
11/5/2006 6:40 PM |
1 |
Bad |
Man |
3 |
Hold - To Sentence |
NULL |
11/5/2006 6:40 PM |
9/9/9999 12:00 AM |
The
challenge here is to find all the inmates that are held illegally. That is, those
that are held in prison without a valid authorization. This is not just for now,
where it is relatively simple to find using something like this:
SELECT
Id, FirstName, Surname, 'Oh!
Boy! Problem!!!'
FROM
Inmates
WHERE
EXISTS
(
SELECT 1
FROM Commands
WHERE Commands.InmateID = Inmates.Id
AND dbo.CommandEnds(Commands.CommandType, Commands.ValidFor, Commands.IssuedAt) <
getdate()
AND IssuedAt
= (SELECT
MAX(IssuedAt) FROM
Commands WHERE Commands.InmateID = Inmates.Id)
)
I'm
talking about finding all the gaps
in the commands, for instance, the following inmate is held illegally:
Id |
FirstName |
Surname |
Commands.Id |
Commands.Description |
Commands.ValidFor |
Commands.IssuedAt |
Commands.Until |
1 |
Bad |
Man |
1 |
Hold - Days |
8 |
2/5/2006 6:39 PM |
10/5/2006 6:39 PM |
1 |
Bad |
Man |
2 |
Hold - Hours |
24 |
10/5/2006 6:40 PM |
11/5/2006 6:40 PM |
1 |
Bad |
Man |
3 |
Hold - To Sentence |
NULL |
11/5/2006 7:40 PM |
9/9/9999 12:00 AM |
There
is an hour gap between the second and third commands. (This is usually bad, by the way).
Okay,
so far there was a lot of rambling, but nothing concrete. The challenge is to
find all the gaps in T-SQL, without using
cursors. Can you do it?
Comments
Comment preview