Who Has The Monitor?

  • Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/whohasthemonitor.asp

  • Hi Steve,

    Some clues allow more than one interpretation:

    "The green office was to the left of the white office." - is that directly to the left (adjacent) or somewhere to the left?

    "Sue has the first office." - is that the leftmost or the rightmost office?

    I've made the assumption that the green and left office need not be directly adjacent, and the the first office is the leftmost office. With those assumptions, there are a total of six solutions to the problem. In two, Sue has the Dual 19" LCDs. Andy gets them in three, and the last solution has Brian working the dual 19" LCDs.

    Of course, I didn't work this out for mysself. This is a SQL Server forum, after all!!

    CREATE

    VIEW Carthesian

    AS

    SELECT      *

    FROM       (SELECT 1 UNION ALL

                SELECT 2 UNION ALL

                SELECT 3 UNION ALL

                SELECT 4 UNION ALL

                SELECT 5) AS Offices(Office)

    CROSS JOIN (SELECT 'Brian' UNION ALL

                SELECT 'Steve' UNION ALL

                SELECT 'Andy' UNION ALL

                SELECT 'Sue' UNION ALL

                SELECT 'Kevin') AS Persons(Person)

    CROSS JOIN (SELECT 'red' UNION ALL

                SELECT 'green' UNION ALL

                SELECT 'white' UNION ALL

                SELECT 'yellow' UNION ALL

                SELECT 'blue') AS Colors(Color)

    CROSS JOIN (SELECT 'Prius' UNION ALL

                SELECT 'Accord' UNION ALL

                SELECT 'Caravan' UNION ALL

                SELECT 'Corvette' UNION ALL

                SELECT 'Suburban') AS Cars(Car)

    CROSS JOIN (SELECT 'iced tea' UNION ALL

                SELECT 'coffee' UNION ALL

                SELECT 'Red Bull' UNION ALL

                SELECT 'beer' UNION ALL

                SELECT 'Mountain Dew') AS Drinks(Drink)

    CROSS JOIN (SELECT '17" CRT' UNION ALL

                SELECT '15" CRT' UNION ALL

                SELECT '15" LCD' UNION ALL

                SELECT '17" LCD' UNION ALL

                SELECT 'dual 19" LCDs') AS Monitors(Monitor)

    go

    CREATE VIEW Combinations

    AS

    SELECT     *

    FROM       Carthesian

    WHERE NOT (Person = 'Brian' AND Color <> 'red')       -- Brian had the red office.

    AND   NOT (Person = 'Steve' AND Monitor <> '17" CRT') -- Steve had a 17" CRT on his desk.

    AND   NOT (Person = 'Andy' AND Drink <> 'iced tea')   -- Andy liked to drink iced tea.

    AND   NOT (Color = 'green' AND Drink <> 'coffee')     -- The person with the green office drinks coffee.

    AND   NOT (Car = 'Prius' AND Monitor <> '15" CRT')    -- The person who drove a Prius had a 15" CRT.

    AND   NOT (Office = 3 AND Drink <> 'Red Bull')        -- The person in the middle office drinks Red Bull.

    AND   NOT (Color = 'yellow' AND Car <> 'Accord')      -- The person with the yellow office drove an Accord.

    AND   NOT (Office = 1 AND Person <> 'Sue')            -- Sue has the first office.

    AND   NOT (Car = 'Corvette' AND Drink <> 'beer')      -- The Corvette driver drinks beer.

    AND   NOT (Person = 'Sue' AND Color <> 'blue')        -- Sue has the blue office.

    AND   NOT (Person = 'Kevin' AND Car <> 'Suburban')    -- Kevin drives a Suburban.

    go

    CREATE VIEW Solution

    AS

    SELECT c1.Office AS Office_1, c1.Person AS Person_1, c1.Color AS Color_1, c1.Car AS Car_1, c1.Drink AS Drink_1, c1.Monitor AS Monitor_1,

           c2.Office AS Office_2, c2.Person AS Person_2, c2.Color AS Color_2, c2.Car AS Car_2, c2.Drink AS Drink_2, c2.Monitor AS Monitor_2,

           c3.Office AS Office_3, c3.Person AS Person_3, c3.Color AS Color_3, c3.Car AS Car_3, c3.Drink AS Drink_3, c3.Monitor AS Monitor_3,

           c4.Office AS Office_4, c4.Person AS Person_4, c4.Color AS Color_4, c4.Car AS Car_4, c4.Drink AS Drink_4, c4.Monitor AS Monitor_4,

           c5.Office AS Office_5, c5.Person AS Person_5, c5.Color AS Color_5, c5.Car AS Car_5, c5.Drink AS Drink_5, c5.Monitor AS Monitor_5

    FROM   Combinations AS c1

    JOIN   Combinations AS c2

      ON   c2.Person <> c1.Person

      AND  c2.Color <> c1.Color

      AND  c2.Car <> c1.Car

      AND  c2.Drink <> c1.Drink

      AND  c2.Monitor <> c1.Monitor

    JOIN   Combinations AS c3

      ON   c3.Person <> c1.Person

      AND  c3.Person <> c2.Person

      AND  c3.Color <> c1.Color

      AND  c3.Color <> c2.Color

      AND  c3.Car <> c1.Car

      AND  c3.Car <> c2.Car

      AND  c3.Drink <> c1.Drink

      AND  c3.Drink <> c2.Drink

      AND  c3.Monitor <> c1.Monitor

      AND  c3.Monitor <> c2.Monitor

    JOIN   Combinations AS c4

      ON   c4.Person <> c1.Person

      AND  c4.Person <> c2.Person

      AND  c4.Person <> c3.Person

      AND  c4.Color <> c1.Color

      AND  c4.Color <> c2.Color

      AND  c4.Color <> c3.Color

      AND  c4.Car <> c1.Car

      AND  c4.Car <> c2.Car

      AND  c4.Car <> c3.Car

      AND  c4.Drink <> c1.Drink

      AND  c4.Drink <> c2.Drink

      AND  c4.Drink <> c3.Drink

      AND  c4.Monitor <> c1.Monitor

      AND  c4.Monitor <> c2.Monitor

      AND  c4.Monitor <> c3.Monitor

    JOIN   Combinations AS c5

      ON   c5.Person <> c1.Person

      AND  c5.Person <> c2.Person

      AND  c5.Person <> c3.Person

      AND  c5.Person <> c4.Person

      AND  c5.Color <> c1.Color

      AND  c5.Color <> c2.Color

      AND  c5.Color <> c3.Color

      AND  c5.Color <> c4.Color

      AND  c5.Car <> c1.Car

      AND  c5.Car <> c2.Car

      AND  c5.Car <> c3.Car

      AND  c5.Car <> c4.Car

      AND  c5.Drink <> c1.Drink

      AND  c5.Drink <> c2.Drink

      AND  c5.Drink <> c3.Drink

      AND  c5.Drink <> c4.Drink

      AND  c5.Monitor <> c1.Monitor

      AND  c5.Monitor <> c2.Monitor

      AND  c5.Monitor <> c3.Monitor

      AND  c5.Monitor <> c4.Monitor

    WHERE  c1.Office = 1

    AND    c2.Office = 2

    AND    c3.Office = 3

    AND    c4.Office = 4

    AND    c5.Office = 5

    -- The green office was to the left of the white office.

    AND CASE 'white'

         WHEN c1.Color THEN 'F'

         WHEN c2.Color THEN CASE WHEN 'green' IN (c1.Color) THEN 'T' ELSE 'F' END

         WHEN c3.Color THEN CASE WHEN 'green' IN (c1.Color, c2.Color) THEN 'T' ELSE 'F' END

         WHEN c4.Color THEN CASE WHEN 'green' IN (c1.Color, c2.Color, c3.Color) THEN 'T' ELSE 'F' END

         WHEN c5.Color THEN 'T'

        END = 'T'

    --The person who drives the Caravan has an office next to the person who has the 15" LCD.

    AND CASE 'Caravan'

         WHEN c1.Car THEN CASE WHEN '15" LCD' IN (c2.Monitor) THEN 'T' ELSE 'F' END

         WHEN c2.Car THEN CASE WHEN '15" LCD' IN (c1.Monitor, c3.Monitor) THEN 'T' ELSE 'F' END

         WHEN c3.Car THEN CASE WHEN '15" LCD' IN (c2.Monitor, c4.Monitor) THEN 'T' ELSE 'F' END

         WHEN c4.Car THEN CASE WHEN '15" LCD' IN (c3.Monitor, c5.Monitor) THEN 'T' ELSE 'F' END

         WHEN c5.Car THEN CASE WHEN '15" LCD' IN (c4.Monitor) THEN 'T' ELSE 'F' END

        END = 'T'

    --The person who has the 17" LCD sits next to the person who drives an Accord.

    AND CASE '17" LCD'

         WHEN c1.Monitor THEN CASE WHEN 'Accord' IN (c2.Car) THEN 'T' ELSE 'F' END

         WHEN c2.Monitor THEN CASE WHEN 'Accord' IN (c1.Car, c3.Car) THEN 'T' ELSE 'F' END

         WHEN c3.Monitor THEN CASE WHEN 'Accord' IN (c2.Car, c4.Car) THEN 'T' ELSE 'F' END

         WHEN c4.Monitor THEN CASE WHEN 'Accord' IN (c3.Car, c5.Car) THEN 'T' ELSE 'F' END

         WHEN c5.Monitor THEN CASE WHEN 'Accord' IN (c4.Car) THEN 'T' ELSE 'F' END

        END = 'T'

    --The person who drives a Caravan sits next to the person who drinks Mountain Dew.

    AND CASE 'Caravan'

         WHEN c1.Car THEN CASE WHEN 'Mountain Dew' IN (c2.Drink) THEN 'T' ELSE 'F' END

         WHEN c2.Car THEN CASE WHEN 'Mountain Dew' IN (c1.Drink, c3.Drink) THEN 'T' ELSE 'F' END

         WHEN c3.Car THEN CASE WHEN 'Mountain Dew' IN (c2.Drink, c4.Drink) THEN 'T' ELSE 'F' END

         WHEN c4.Car THEN CASE WHEN 'Mountain Dew' IN (c3.Drink, c5.Drink) THEN 'T' ELSE 'F' END

         WHEN c5.Car THEN CASE WHEN 'Mountain Dew' IN (c4.Drink) THEN 'T' ELSE 'F' END

        END = 'T'

    go

    SELECT * FROM Solution

    go

    DROP VIEW Solution

    DROP VIEW Combinations

    DROP VIEW Carthesian

    go

    (I hope the formatting comes out okay....)


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Okay, so my assumptions were wrong.

    The green office was to the left of the white office." - is that directly to the left (adjacent) or somewhere to the left?

    "Sue has the first office." - is that the leftmost or the rightmost office?

    I've made the assumption that the green and left office need not be directly adjacent, and the the first office is the leftmost office. With those assumptions, there are a total of six solutions to the problem.

    After changing my query to the three other possible combinations of interpretations, I found an interpretation that leaves just one single answer to the question.

    If we assume the the green office is directly adjacent (to the left) if the white office, AND that the first (Sue's) office is to the far right, then my query returns just two rows - and in both rows, Andy is the lucky one who gets to play with the dual 19" LCDs.

    Here's the changed part of the query:

    -- The green office was to the left of the white office.

    AND    CASE 'white'

            WHEN c5.Color THEN 'F'

            WHEN c4.Color THEN CASE WHEN 'green' IN (c5.Color) THEN 'T' ELSE 'F' END

            WHEN c3.Color THEN CASE WHEN 'green' IN (c4.Color) THEN 'T' ELSE 'F' END

            WHEN c2.Color THEN CASE WHEN 'green' IN (c3.Color) THEN 'T' ELSE 'F' END

            WHEN c1.Color THEN CASE WHEN 'green' IN (c2.Color) THEN 'T' ELSE 'F' END

           END = 'T'

    BTW, the execution plan is definitely, ahem, "interesting"


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • It certainly makes a difference depending on whether one assumes the "first office" means rightmost or leftmost.

    I also worked this out using SQL Server (it's supposed to be a logical inference engine, after all!)

    I noticed that the relations I end up with that fulfill the constraints leave open the question of which offices(positionally) certain engineers occupy, and which drinks they like.

    TroyK

  • What a collosal waste of time. Dont you people have jobs or something?

  • I have deduced, only by using a powerful new feature in SQL 2005, Message Queue, that there are 2 solutions (at least) because sue can only drink beer or mountain dew. It is possible to construct 2 solutions based on this logic.

  • I would say it has to be Andy did it in the Yellow office with the spare change in his pocket.

     

    Howevr I am the third gunman on the grassy knoll who just happens to have dual 19s.

  • , LOL, it's a pretty good puzzle.

    I didn't think about the first being right. Should have clarified this a little more, but being an English speaker, tend to go left to right. The blue house is the left most one. Also I do think that I potentially need one more clue since the solution above looks ok and I think there's a place where you need to guess the ordering.

    How about "The red house is in the middle".

  • Bah! With the "The red house is in the middle" (house, here I assume means office!), my solution is now wrong. Unfortunately, my colleagues is now the correct one. Please change to make me more clever than him

  • Hi Steve,

    Fortunately, I had kept my query window open over the day!

    With the clarification that first = leftmost (my first assumption) and the additional hint that the red house (office) is in the middle, I still get three solutions with the green offfice directly left of the white office, and even four solutions with the green office "somewhere to the left" of the white office.

    In both lists, the 19" Dual monitor can be in Sue's, Andy's, or Brian's office.

    (Just run the SQL I included in my first post, with this additional line for the Combinations view:

    AND

    NOT (Office = 3 AND Color <> 'red') -- The red house (office) is in the middle.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Here's what I got for a query plan response...

    Server: Msg 8623, Level 16, State 1, Line 1

    Internal Query Processor Error: The query processor could not produce a query plan.  Contact your primary support provider for more information.

    I came up with Kevin using Visual PaperPen 2005. 

     

  • Andy has the dual monitor...

    PS- I still have a copy of the original puzzle at home somewhere, with the houses and such. My uncle gave it to me when I was about 8 yrs old to keep me quiet for awhile....that was 40+yrs ago...

    wolf

  • Hi SAM,

    I get the same error when I execute on SQL Server 2000. On SQL Server 2005, I do get a response.

    However, in all three or four solutions, Kevin uses the 15" LCD. Either SQL Server 2005 or Visual PaperPen 2005 has a bug.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • I agree with datagod that this is a collosal waste of time...but boy, it sure is fun to waste time on occasion!

    Using the clues provided (including the Red office being in the middle) and defaulting to the Green office being directly left of the White office (rather than just 'somewhere' left of the white office), I have narrowed it down to the following:

    Name

    Sue

    Andy

    Steve

    Brian

     

    Steve

    Kevin

    Andy

    Steve

    Kevin

    Color

    Blue

    Yellow

    Red

    Green

    White

    Car

    Corvette

    Accord

    Prius

    Caravan

    Prius

    Caravan

    Suburban

    Prius

     

    Suburban

    Drink

    Beer

    Iced Tea

    MD

    Red Bull

    Coffee

    Iced Tea

    MD

    Monitor

     

     

    17 LCD

     

    Dual Disp

    17 CRT

     

     

    15 LCD

    Dual Disp

     

    15 CRT

    17 LCD

    15 LCD

    Dual Disp

    17 CRT

    15 CRT

     

    15 LCD

    Dual Disp

    17 CRT

    15 CRT

     

    15 LCD

    Dual Disp

     

    Can someone let me know if I have missed something here?

  • Which of the clues says that Brian has the middle office?  There's a clue which says brian has the Red office, and another which says that the Middle office occupant drinks Red Bull, but no where did I see it tie Brian to the middle office - did I miss something?

    -John

Viewing 15 posts - 1 through 15 (of 50 total)

You must be logged in to reply to this topic. Login to reply