Solution to September's Puzzle: The Missing Buck
In last month's puzzle, three people arrived at a hotel and asked to share a room. The charge was $30, so each person chipped in $10. Later on, the hotel receptionist discovered that he overcharged the guests by $5. Realizing that he couldn't evenly split $5 between the three guests, he pocketed $2 and gave each guest $1 back. However, the receptionist found it hard to sleep that night because the numbers didn't seem to add up. Each guest eventually paid $9, and 9 x 3 + $2 = $29. Where's the missing buck?
The guests ultimately were supposed to pay $25. After getting the change back, each of them paid $9, for a total of $27. The mistake that the receptionist made was that he added $2 to $27, assuming he'd get $30 instead of subtracting $2 from $27 and expecting $25. To get the original incorrect room charge of $30, you should sum the correct room charge ($25) plus the change that the guests got ($3) plus the amount that the receptionist pocketed ($2).
Similar errors frequently occur in T-SQL code when you apply the incorrect operator (a plus operator instead of a minus operator and vise versa). For example, suppose table T1 has an integer column c1 and you need to return all rows before a gap. In other words, you need to return all c1 values for which a value greater by 1 doesn't exist. A common mistake is to write
SELECT c1 FROM T1 AS A WHERE NOT EXISTS(SELECT * FROM T1 AS B WHERE A.c1 = B.c1 + 1)
Instead of getting all the values before a gap, you'll get all the values after a gap. The correct query uses a minus operator, not a plus operator:
SELECT c1 FROM T1 AS A WHERE NOT EXISTS(SELECT * FROM T1 AS B WHERE A.c1 = B.c1 — 1)
The correct way to phrase a relationship where a c1 value in B is greater than a c1 value in A by 1 is
A.c1 = B.c1 — 1
If B1.c1 – 1 is equal to A.c1, then B.c1 is greater than A.c1 by 1 and not the other way around.
October's Puzzle: Alternating Lamp States
One hundred lamps are arranged in a row (call them lamp 1, lamp 2, lamp 3, and so on). All lamps are originally turned off. Each lamp has a switch that alternates its state (on/off). One hundred people (call them person 1, person 2, person 3, and so on) get the following mission: Go to each nth lamp and alternate its state, where n is the person's number. So person 1 alternates the state of lamps 1, 2, 3, and so on. Person 2 alternates the states of lamps 2, 4, 6, and so on. What's the state of the lamps after all 100 people finished their missions?
