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:

1. Hold the inmate for a specified number of days, starting from the issued date
2. Hold the inmate for a specified number of hours, starting from the issued hour
3. 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:

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:

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?