The 'Last Seven Days' puzzle

Av rating:

Total votes: 1
Total comments: 40

Zipped test file

send to a friend

printer friendly version

The 'Last Seven Days' puzzle

12 March 2008

by Alex Kozak

The best SQL puzzles come from real experiences in the workplace. Here, Alex Kozak describes how he took on a task that looked simple for a while. Later, he realized that he's stumbled over an excellent puzzle for Simple-Talk.

Not long ago, one of our managers ask me for help. He wanted me to provide a report for the latest seven days when each cash register in each store was online, processing sales transactions.

At first glance, the task looked very simple. However, later it turned into quite interesting puzzle.

Sample Table

For demonstration purposes, I created the sample table ‘registersOnline’. That table has only three columns – store, register, onlineDays and about 17000 rows.

In order to try all following examples, you will need to download, unzip and execute the scripts from the zipped files in Create_Load_registersOnline.zip which you can get from the speech-bubble in the title-bar

First Solutions

Since the task looked to me like a ranking problem, my first intention was to use a ranking function, as shown in the Listing 1:

Listing1. Find last seven days, using ranking function

SELECT * FROM (SELECT DENSE_RANK() OVER (PARTITION BY store, register ORDER BY onlineDays DESC) AS num, store, register, onlineDays FROM registersOnline) t1 WHERE t1.num <= 7

However, it has turned out that the customer has SQL Server 2000 and, because of that, I decided to use a correlated sub-query, as demonstrated in the Listing2:

Listing2. Find last seven days, using correlated sub-query-- First variant
SELECT store, register, onlineDays
   FROM registersOnline t1
   WHERE 7 >= (SELECT COUNT(*)
                  FROM registersOnline t2
                  WHERE t1.store = t2.store
                  AND t1.register = t2.register
                  AND t1.onlineDays <= t2.onlineDays)
   ORDER BY store ASC, register ASC, onlineDays DESC

-- Second variant
SELECT store, register, onlineDays
   FROM registersOnline t1
   WHERE 7 >= (SELECT COUNT(*)
            FROM registersOnline t2
            WHERE t1.store = t2.store
            AND t1.register = t2.register
            AND DATEDIFF(dd, t1.onlineDays, t2.onlineDays) >= 0)
   ORDER BY store ASC, register ASC, onlineDays DESC

Results: -only a sample shown

store register onlineDays

----- -------- -------------------

203 1 2008-02-25 00:00:00

203 1 2008-02-22 00:00:00

203 1 2008-02-19 00:00:00

203 1 2008-02-15 00:00:00

203 1 2008-02-14 00:00:00

203 1 2008-02-13 00:00:00

203 1 2008-02-12 00:00:00

. . . . . . . . . . . . . . . . . . .

233 3 2008-02-21 00:00:00

233 3 2008-02-20 00:00:00

233 3 2008-02-16 00:00:00

233 3 2008-02-15 00:00:00

233 3 2008-02-14 00:00:00

233 3 2008-02-13 00:00:00

233 3 2008-02-12 00:00:00

233 4 2008-01-05 00:00:00

358 1 2008-02-26 00:00:00

358 1 2008-02-25 00:00:00

358 1 2008-02-22 00:00:00

358 1 2008-02-21 00:00:00

358 1 2008-02-20 00:00:00

358 1 2008-02-19 00:00:00

358 1 2008-02-18 00:00:00

. . . . . . . . . . . . . . . . . . .

10130 4 2008-02-26 00:00:00

10130 4 2008-02-25 00:00:00

10130 4 2008-02-24 00:00:00

10130 4 2008-02-23 00:00:00

10130 4 2008-02-22 00:00:00

10130 4 2008-02-21 00:00:00

10130 4 2008-02-20 00:00:00

(3080 row(s) affected)

Since the manager needed the answer quickly and because I was busy with a few more tasks, I sent the result as it is.

I promised that as soon as I had finished the other tasks, I would create a query that would show the result in a cross-tab format.

However, an hour later, I got a new request.

Incomplete Groups

You probably noticed that in the result of the query from Listing2, some store – register groups are incomplete. That is, there are some registers within the store that were online less than seven times. For example, register 4 from store 233 was online only on 2008-01-05 00:00:00.

A manager found that fact confusing, and asked me to fill all incomplete groups with some arbitrary date, for instance ‘Jan 01, 1900’. For the group with store 233 and register 4 it would mean that one would have to have one row with the real date 2008-01-05 00:00:00 and six rows with an arbitrary date 1900-01-01 00:00:00.

There are a few possible ways to fill incomplete groups.

First is a pure Transact-SQL solution with a loop statement (see Listing3):

Listing3. Fill incomplete groups using loop statement (Transact-SQL solution)

SELECT store, register, onlineDays INTO #lastSeven FROM registersOnline t1 WHERE 7 >= (SELECT COUNT(*) FROM registersOnline t2 WHERE t1.store = t2.store AND t1.register = t2.register AND t1.onlineDays <= t2.onlineDays); SELECT IDENTITY(INT, 1,1) id, COUNT(*) cnt, store, register INTO #incompleteGroups FROM #lastSeven GROUP BY store, register HAVING COUNT(*) < 7; SET NOCOUNT ON; DECLARE @i AS INT, @store INT, @reg INT, @cnt INT, @j INT; SELECT @i = 1, @cnt = 7; WHILE ((SELECT MAX(id) FROM #incompleteGroups) >=@i) BEGIN SELECT @store = store, @reg = register, @cnt = cnt FROM #incompleteGroups WHERE id = @i; SET @j = 1; WHILE (7 - @cnt) >= @j BEGIN INSERT INTO #lastSeven VALUES(@store, @reg, CAST(0 AS smalldatetime)); SELECT @j = @j + 1; END SELECT @i = @i + 1; END

Another solution would employ an auxiliary table (see Listing4):

Listing4. SQL solution with an auxiliary tableIF EXISTS(SELECT * FROM sysobjects
   WHERE ID = (OBJECT_ID('sequence')) AND xtype = 'U')
DROP TABLE sequence;
CREATE TABLE sequence(num INT NOT NULL PRIMARY KEY);

INSERT INTO sequence
SELECT (a1.id + a0.id + 1) id
   FROM (
        SELECT 0 id UNION ALL
        SELECT 1 UNION ALL
        SELECT 2 UNION ALL
        SELECT 3 UNION ALL
        SELECT 4 UNION ALL
        SELECT 5 UNION ALL
        SELECT 6 UNION ALL
        SELECT 7 UNION ALL
        SELECT 8 UNION ALL
        SELECT 9
) a0
CROSS JOIN (
        SELECT 0 id UNION ALL
        SELECT 10 UNION ALL
        SELECT 20 UNION ALL
        SELECT 30 UNION ALL
        SELECT 40 UNION ALL
        SELECT 50 UNION ALL
        SELECT 60 UNION ALL
        SELECT 70 UNION ALL
        SELECT 80 UNION ALL
        SELECT 90
) a1

SELECT store, register, onlineDays
   FROM registersOnline t1
   WHERE 7 >= (SELECT COUNT(*)
                  FROM registersOnline t2
                  WHERE t1.store = t2.store
                  AND t1.register = t2.register
                  AND t1.onlineDays <= t2.onlineDays)
UNION ALL
SELECT store, register, CAST(0 AS smalldatetime)
   FROM sequence t1 CROSS JOIN
      (SELECT COUNT(*) #days, store, register
         FROM registersOnline
           GROUP BY store, register) t2
   WHERE t1.num <= (SELECT 7 - COUNT(*)
                 FROM registersOnline t3
                 WHERE t2.store = t3.store
                 AND t2.register = t3.register
                 GROUP BY store, register)
ORDER BY store, register, onlineDays DESC

However, there are also the solutions that do not require an auxiliary table.

The Puzzles

The first puzzle

Try to find such a solution. Consider it as a puzzle with the following requirements:

Having table ‘registersOnline’, find the seven most recent days, when each register in each store was online.

If some pair register-store appears online less than seven times, fill that group up to seven rows, where added rows should have an arbitrary date ‘Jan 01, 1900’.

You need to find a single query solution without an auxiliary table, identity columns, ranking functions or constructions like:

SELECT (a1.id + a0.id) id
   FROM (
        SELECT 0 id UNION ALL
        SELECT 1 UNION ALL
        SELECT 2 UNION ALL
        SELECT 3 UNION ALL
        SELECT 4 UNION ALL
        SELECT 5 UNION ALL
        SELECT 6 UNION ALL
        SELECT 7 UNION ALL
        SELECT 8 UNION ALL
        SELECT 9
) a0
CROSS JOIN (
        SELECT 0 id UNION ALL
        SELECT 10 UNION ALL
        SELECT 20 UNION ALL
        SELECT 30 UNION ALL
        SELECT 40 UNION ALL
        SELECT 50 UNION ALL
        SELECT 60 UNION ALL
        SELECT 70 UNION ALL
        SELECT 80 UNION ALL
        SELECT 90
) a1
ORDER BY 1

All you can use is a table ‘registersOnline’.

Your solution should work in any RDBMS, including two latest versions of Microsoft SQL Server – 2005 and 2000.

The Second Puzzle

In addition, if you are asked to find the most recent 100 days, when the registers were online - your query should work without changes in its structure

Once you have done that, Try to fill the missing rows(dates) in each group not with a sample date ‘Jan 01, 1900’, but with a date that equal to max (or to min) date in that group.

The Third Puzzle

Finally, try to find the solution that shows the result for the last 7 days in a cross-tab format.

Once again, it should be a single query that works in SQL Server 2000 and in SQL Server 2005. You are allowed to use table ‘registersOnline’ only.

As always, the best solution will be awarded with a $50 amazon voucher.

I'll post my solutions as comments after a week or so.

This article has been viewed 4692 times.

/*
str rgr OnlineDays
--- --- -----------------------------------------------------------------------------------------
203 1   12 Feb 2008, 13 Feb 2008, 14 Feb 2008, 15 Feb 2008, 19 Feb 2008, 22 Feb 2008, 25 Feb 2008
233 1   09 Jan 2008, 11 Jan 2008, 18 Jan 2008, 30 Jan 2008, 01 Feb 2008, 06 Feb 2008, 13 Feb 2008
233 2   20 Feb 2008, 21 Feb 2008, 22 Feb 2008, 23 Feb 2008, 24 Feb 2008, 25 Feb 2008, 26 Feb 2008
233 3   12 Feb 2008, 13 Feb 2008, 14 Feb 2008, 15 Feb 2008, 16 Feb 2008, 20 Feb 2008, 21 Feb 2008
...etc..

and this is how to get it!
*/
DECLARE @accumulation VARCHAR(7000),
   @register INT,
   @store INT
DECLARE @grouping TABLE
   (
    MyID INT IDENTITY(1, 1),
    store INT,
    register INT,
    onlineDays DATETIME,
    accumulation VARCHAR(7000)
   )
INSERT   INTO @Grouping
         (store, register, onlineDays)
         SELECT   store, register, onlineDays
         FROM     registersonline
         ORDER BY store, register, onlineDays
UPDATE   @grouping
SET      @Accumulation = [accumulation] = CASE WHEN store<>COALESCE(@store,0)
           OR register<>COALESCE(@register,0)
          THEN CONVERT(CHAR(11),onlineDays, 113)
          ELSE @Accumulation+', '+CONVERT(CHAR(11),onlineDays, 113)
          END,
         @Store = store = store,
         @register = register = register
SELECT   store, register, [online-Days] = RIGHT(MAX(accumulation),89)
FROM     @grouping
GROUP BY store, register
ORDER BY store, register

Subject:	I don't understand the puzzle
Posted by:	glenfitch (view profile)
Posted on:	Monday, March 17, 2008 at 7:19 AM
Message:	Please call me Mr Stupid by all means, but I don't understand the puzzle. Your manager wanted you to provide a report for the latest seven days when each cash register in each store was online, processing sales transactions. You give him a list for store 203, cash register 1 that covers thirteen days. 203 1 2008-02-25 00:00:00 203 1 2008-02-22 00:00:00 203 1 2008-02-19 00:00:00 203 1 2008-02-15 00:00:00 203 1 2008-02-14 00:00:00 203 1 2008-02-13 00:00:00 203 1 2008-02-12 00:00:00 If you mean the latest days when that particular register was online, why is it that there is cash register 4 in store 233 that seems to have only ever been operated once. 233 4 2008-01-05 00:00:00 Why would anyone want to have the date ... 1900-01-01 00:00:00 ... inserted into a report? It makes no sense. My best solution would be to tell your manager to feed the data into Excel, and show him how to use the cross-tab function.

Subject:	Puzzle
Posted by:	Phil Factor (view profile)
Posted on:	Monday, March 17, 2008 at 10:23 AM
Message:	Come on Glen! I think that the manager actually just wanted a report like this: /* str rgr OnlineDays --- --- ----------------------------------------------------------------------------------------- 203 1 12 Feb 2008, 13 Feb 2008, 14 Feb 2008, 15 Feb 2008, 19 Feb 2008, 22 Feb 2008, 25 Feb 2008 233 1 09 Jan 2008, 11 Jan 2008, 18 Jan 2008, 30 Jan 2008, 01 Feb 2008, 06 Feb 2008, 13 Feb 2008 233 2 20 Feb 2008, 21 Feb 2008, 22 Feb 2008, 23 Feb 2008, 24 Feb 2008, 25 Feb 2008, 26 Feb 2008 233 3 12 Feb 2008, 13 Feb 2008, 14 Feb 2008, 15 Feb 2008, 16 Feb 2008, 20 Feb 2008, 21 Feb 2008 ...etc.. and this is how to get it! */ DECLARE @accumulation VARCHAR(7000), @register INT, @store INT DECLARE @grouping TABLE ( MyID INT IDENTITY(1, 1), store INT, register INT, onlineDays DATETIME, accumulation VARCHAR(7000) ) INSERT INTO @Grouping (store, register, onlineDays) SELECT store, register, onlineDays FROM registersonline ORDER BY store, register, onlineDays UPDATE @grouping SET @Accumulation = [accumulation] = CASE WHEN store<>COALESCE(@store,0) OR register<>COALESCE(@register,0) THEN CONVERT(CHAR(11),onlineDays, 113) ELSE @Accumulation+', '+CONVERT(CHAR(11),onlineDays, 113) END, @Store = store = store, @register = register = register SELECT store, register, [online-Days] = RIGHT(MAX(accumulation),89) FROM @grouping GROUP BY store, register ORDER BY store, register

Subject:	Re: I don't understand the puzzle
Posted by:	Alex Kozak (view profile)
Posted on:	Monday, March 17, 2008 at 11:18 PM
Message:	Glen, the manager didn’t ask to find the registers, who was online in the last seven calendar days or in any sequence of seven calendar days. The following list 203 1 2008-02-25 00:00:00 203 1 2008-02-22 00:00:00 203 1 2008-02-19 00:00:00 203 1 2008-02-15 00:00:00 203 1 2008-02-14 00:00:00 203 1 2008-02-13 00:00:00 203 1 2008-02-12 00:00:00 , shows exactly what manager wanted to know: the register 1 from store 203 was online on Feb 25, on Feb 22, on Feb 19 and so on. The difference in 13 days between the first and the seventh dates in the list does not mean anything. The difference between two subsequent appearances of the same register can be month, a few months or even longer. Once again, it doesn't mean anything – you need to find the dates, but not the difference. The manager also didn’t ask to find the registers, who was online at least seven times (days). However, you are probably right in regards to register 4 from store 233 that appears on 2008-01-05 00:00:00 only. It would be more precise to say, “Find the latest seven or less days when each cash register in each store was online”. The request to insert the arbitrary date 1900-01-01 00:00:00 instead of missing dates, probably came from the customer. Finally, your suggestion to show manager how to use Excel, would be difficult to implement for a few reasons: - the manaer can be very busy; - he may not have technical skills; - he can be on customer site in another country; - the top managers don't want to know how to do; they want the result.

Subject:	Re: Puzzle
Posted by:	Alex Kozak (view profile)
Posted on:	Tuesday, March 18, 2008 at 12:19 AM
Message:	Phil, you are right about the layout of the cross-tab report. However, your solution doesn’t work. If it works, it would be a valid solution, but in the puzzle, I asked about a single query (one SELECT statement) solution that uses table registersonline only.

Subject:	Re: My solution doesn't work
Posted by:	Phil Factor (view profile)
Posted on:	Tuesday, March 18, 2008 at 4:10 AM
Message:	Hmm. I've just retested my solution here on your table. I ran your script and then ran my solution and it gave every appearance of working fine. I copied and pasted the script I posted into the comment and that worked fine too. In what sense doesn't it work? (I'm using SQL Server 2005). You argue that it doesn't obey your rules but that is another matter. I still think it is a splendid solution.

Subject:	Re: Found the problem
Posted by:	Alex Kozak (view profile)
Posted on:	Tuesday, March 18, 2008 at 2:30 PM
Message:	OK, I found the problem. Your solution works in SQL Server 2005 only. You also have to have the database compatibility level set to 90 (for the lower compatibility level it will not work). I agree, the solution is nice, though it doesn’t follow the rules of the puzzle.

Subject:	Solution
Posted by:	StefanG (view profile)
Posted on:	Wednesday, March 19, 2008 at 9:16 AM
Message:	My solutions all use the technique to add fake rows to the table so all groups have 7 or more rows. Then it is easy to just use the same correlated subquery that was already shown in the puzzle. -- puzzle 1 select store, register, case when fake=0 then onlineDays else '19000101' end as onlinedays from ( select store, register, onlineDays, 0 as fake from registersonline UNION ALL select store, register, fakedate, 1 as fake from ( select store, register from registersonline group by store, register ) t1 cross join ( select distinct top 6 dateadd(year, -10, onlinedays) as fakedate from registersonline ) t2 ) d1 where 7>=( SELECT COUNT(*) FROM ( select store, register, onlineDays, 0 as fake from registersonline UNION ALL select store, register, fakedate, 1 as fake from ( select store, register from registersonline group by store, register ) t1 cross join ( select distinct top 6 dateadd(year, -10, onlinedays) as fakedate from registersonline ) t2 ) d2 WHERE d1.store = d2.store and d1.register=d2.register and d1.onlinedays <= d2.onlinedays ) order by 1,2,3

Subject:	puzzle 2
Posted by:	StefanG (view profile)
Posted on:	Wednesday, March 19, 2008 at 9:18 AM
Message:	------------------------------------------------------------------------------------------------ -- puzzle 2a -- return 100 rows for each group select store, register, case when fake=0 then onlineDays else '19000101' end as onlinedays from ( select store, register, onlineDays, 0 as fake from registersonline UNION ALL select store, register, fakedate, 1 as fake from ( select store, register from registersonline group by store, register ) t1 cross join ( select distinct dateadd(year, -10, onlinedays) as fakedate from registersonline UNION ALL select distinct dateadd(year, -20, onlinedays) as fakedate from registersonline ) t2 ) d1 where 100>=( SELECT COUNT() FROM ( select store, register, onlineDays, 0 as fake from registersonline UNION ALL select store, register, fakedate, 1 as fake from ( select store, register from registersonline group by store, register ) t1 cross join ( select distinct dateadd(year, -10, onlinedays) as fakedate from registersonline UNION ALL select distinct dateadd(year, -20, onlinedays) as fakedate from registersonline ) t2 ) d2 WHERE d1.store = d2.store and d1.register=d2.register and d1.onlinedays <= d2.onlinedays ) order by 1,2,3 ------------------------------------------------------------------------------------------------ -- puzzle 2b -- use max(onlinedays) instead of 1900-01-01 select store , register , case when fake=0 then onlineDays else (select max(onlinedays) from registersonline r1 where r1.store = d1.store and r1.register = d1.register) end as onlinedays from ( select store, register, onlineDays, 0 as fake from registersonline UNION ALL select store, register, fakedate, 1 as fake from ( select store, register from registersonline group by store, register ) t1 cross join ( select distinct top 6 dateadd(year, -10, onlinedays) as fakedate from registersonline ) t2 ) d1 where 7>=( SELECT COUNT() FROM ( select store, register, onlineDays, 0 as fake from registersonline UNION ALL select store, register, fakedate, 1 as fake from ( select store, register from registersonline group by store, register ) t1 cross join ( select distinct top 6 dateadd(year, -10, onlinedays) as fakedate from registersonline ) t2 ) d2 WHERE d1.store = d2.store and d1.register=d2.register and d1.onlinedays <= d2.onlinedays ) order by 1,2,3

Subject:	puzzle3
Posted by:	StefanG (view profile)
Posted on:	Wednesday, March 19, 2008 at 9:18 AM
Message:	------------------------------------------------------------------------------------------------ -- puzzle 3 -- Crosstab select store ,register ,( select case when fake=0 then onlineDays else '19000101' end as onlinedays from ( select store, register, onlineDays, 0 as fake from registersonline UNION ALL select store, register, fakedate, 1 as fake from ( select store, register from registersonline group by store, register ) t1 cross join ( select distinct top 6 dateadd(year, -10, onlinedays) as fakedate from registersonline ) t2 ) d1 where 1=( SELECT COUNT() FROM ( select store, register, onlineDays, 0 as fake from registersonline UNION ALL select store, register, fakedate, 1 as fake from ( select store, register from registersonline group by store, register ) t1 cross join ( select distinct top 6 dateadd(year, -10, onlinedays) as fakedate from registersonline ) t2 ) d2 WHERE d1.store = d2.store and d1.register=d2.register and d1.onlinedays <= d2.onlinedays ) and d1.store=r.store and d1.register=r.register ) day1 ,( select case when fake=0 then onlineDays else '19000101' end as onlinedays from ( select store, register, onlineDays, 0 as fake from registersonline UNION ALL select store, register, fakedate, 1 as fake from ( select store, register from registersonline group by store, register ) t1 cross join ( select distinct top 6 dateadd(year, -10, onlinedays) as fakedate from registersonline ) t2 ) d1 where 2=( SELECT COUNT() FROM ( select store, register, onlineDays, 0 as fake from registersonline UNION ALL select store, register, fakedate, 1 as fake from ( select store, register from registersonline group by store, register ) t1 cross join ( select distinct top 6 dateadd(year, -10, onlinedays) as fakedate from registersonline ) t2 ) d2 WHERE d1.store = d2.store and d1.register=d2.register and d1.onlinedays <= d2.onlinedays ) and d1.store=r.store and d1.register=r.register ) day2 ,( select case when fake=0 then onlineDays else '19000101' end as onlinedays from ( select store, register, onlineDays, 0 as fake from registersonline UNION ALL select store, register, fakedate, 1 as fake from ( select store, register from registersonline group by store, register ) t1 cross join ( select distinct top 6 dateadd(year, -10, onlinedays) as fakedate from registersonline ) t2 ) d1 where 3=( SELECT COUNT() FROM ( select store, register, onlineDays, 0 as fake from registersonline UNION ALL select store, register, fakedate, 1 as fake from ( select store, register from registersonline group by store, register ) t1 cross join ( select distinct top 6 dateadd(year, -10, onlinedays) as fakedate from registersonline ) t2 ) d2 WHERE d1.store = d2.store and d1.register=d2.register and d1.onlinedays <= d2.onlinedays ) and d1.store=r.store and d1.register=r.register ) day3 ,( select case when fake=0 then onlineDays else '19000101' end as onlinedays from ( select store, register, onlineDays, 0 as fake from registersonline UNION ALL select store, register, fakedate, 1 as fake from ( select store, register from registersonline group by store, register ) t1 cross join ( select distinct top 6 dateadd(year, -10, onlinedays) as fakedate from registersonline ) t2 ) d1 where 4=( SELECT COUNT() FROM ( select store, register, onlineDays, 0 as fake from registersonline UNION ALL select store, register, fakedate, 1 as fake from ( select store, register from registersonline group by store, register ) t1 cross join ( select distinct top 6 dateadd(year, -10, onlinedays) as fakedate from registersonline ) t2 ) d2 WHERE d1.store = d2.store and d1.register=d2.register and d1.onlinedays <= d2.onlinedays ) and d1.store=r.store and d1.register=r.register ) day4 ,( select case when fake=0 then onlineDays else '19000101' end as onlinedays from ( select store, register, onlineDays, 0 as fake from registersonline UNION ALL select store, register, fakedate, 1 as fake from ( select store, register from registersonline group by store, register ) t1 cross join ( select distinct top 6 dateadd(year, -10, onlinedays) as fakedate from registersonline ) t2 ) d1 where 5=( SELECT COUNT() FROM ( select store, register, onlineDays, 0 as fake from registersonline UNION ALL select store, register, fakedate, 1 as fake from ( select store, register from registersonline group by store, register ) t1 cross join ( select distinct top 6 dateadd(year, -10, onlinedays) as fakedate from registersonline ) t2 ) d2 WHERE d1.store = d2.store and d1.register=d2.register and d1.onlinedays <= d2.onlinedays ) and d1.store=r.store and d1.register=r.register ) day5 ,( select case when fake=0 then onlineDays else '19000101' end as onlinedays from ( select store, register, onlineDays, 0 as fake from registersonline UNION ALL select store, register, fakedate, 1 as fake from ( select store, register from registersonline group by store, register ) t1 cross join ( select distinct top 6 dateadd(year, -10, onlinedays) as fakedate from registersonline ) t2 ) d1 where 6=( SELECT COUNT() FROM ( select store, register, onlineDays, 0 as fake from registersonline UNION ALL select store, register, fakedate, 1 as fake from ( select store, register from registersonline group by store, register ) t1 cross join ( select distinct top 6 dateadd(year, -10, onlinedays) as fakedate from registersonline ) t2 ) d2 WHERE d1.store = d2.store and d1.register=d2.register and d1.onlinedays <= d2.onlinedays ) and d1.store=r.store and d1.register=r.register ) day6 ,( select case when fake=0 then onlineDays else '19000101' end as onlinedays from ( select store, register, onlineDays, 0 as fake from registersonline UNION ALL select store, register, fakedate, 1 as fake from ( select store, register from registersonline group by store, register ) t1 cross join ( select distinct top 6 dateadd(year, -10, onlinedays) as fakedate from registersonline ) t2 ) d1 where 7=( SELECT COUNT(*) FROM ( select store, register, onlineDays, 0 as fake from registersonline UNION ALL select store, register, fakedate, 1 as fake from ( select store, register from registersonline group by store, register ) t1 cross join ( select distinct top 6 dateadd(year, -10, onlinedays) as fakedate from registersonline ) t2 ) d2 WHERE d1.store = d2.store and d1.register=d2.register and d1.onlinedays <= d2.onlinedays ) and d1.store=r.store and d1.register=r.register ) day7 from registersonline r group by store, register order by store, register

Subject:	cheating with SQL2005
Posted by:	StefanG (view profile)
Posted on:	Wednesday, March 19, 2008 at 10:07 AM
Message:	Of course, using some SQL server 2005 features, the queries becomes much clearer and also much faster. This is obviously not a solution to the puzzle since it does not work on SQL2000, but it is interesting as an illustration to what can be done with SQL2005. IF object_id('v') is not null drop view v GO CREATE VIEW V AS select row_number() over (partition by store, register order by onlinedays desc) as rownum , store , register , case when fake=0 then onlinedays else '19000101' end as onlinedays from ( select store, register, onlineDays, 0 as fake from registersonline UNION ALL select store, register, fakedate, 1 as fake from ( select store, register from registersonline group by store, register ) t1 cross join ( select distinct dateadd(year, -10, onlinedays) as fakedate from registersonline UNION ALL select distinct dateadd(year, -20, onlinedays) as fakedate from registersonline ) t2 ) d1 GO -- puzzle 1 SELECT store,register,onlinedays FROM V where rownum <= 7 order by store, register, rownum -- puzzle 2a SELECT store,register,onlinedays FROM V where rownum <= 100 order by store, register, rownum -- puzzle 3 select * from (select rownum, store,register,onlinedays FROM V) p PIVOT (max(onlinedays) for rownum in ([1],[2],[3],[4],[5],[6],[7])) as pvt order by store, register

Subject:	tentative solution (?)
Posted by:	JeriHatTrick (view profile)
Posted on:	Wednesday, March 19, 2008 at 11:21 AM
Message:	This is my solution to the third puzzle. I didn't have much time to check the correctness of the solution as I wrote it at work, but I think it's correct. SELECT Tbl.store, Tbl.register, Tbl.date_one, Tbl.date_two, Tbl.date_three, Tbl.date_four,Tbl.date_five, Tbl.date_six, Tbl.date_seven FROM ( SELECT A.store, A.register, A.onlineDays date_one, isnull(B.onlineDays,'1900-01-01 00:00:00.000') date_two, isnull(C.onlineDays,'1900-01-01 00:00:00.000') date_three, isnull(D.onlineDays,'1900-01-01 00:00:00.000') date_four, isnull(E.onlineDays,'1900-01-01 00:00:00.000') date_five, isnull(F.onlineDays,'1900-01-01 00:00:00.000') date_six, isnull(G.onlineDays,'1900-01-01 00:00:00.000') date_seven FROM ( SELECT store, register, onlineDays FROM registersOnline t1 WHERE 1 >= (SELECT COUNT() FROM registersOnline t2 WHERE t1.store = t2.store AND t1.register = t2.register AND t1.onlineDays <= t2.onlineDays) ) A LEFT JOIN ( SELECT store, register, onlineDays FROM registersOnline t1 WHERE 2 >= (SELECT COUNT() FROM registersOnline t2 WHERE t1.store = t2.store AND t1.register = t2.register AND t1.onlineDays <= t2.onlineDays) ) B ON A.store = B.store AND A.register = B.register AND A.onlineDays > B.onlineDays LEFT JOIN ( SELECT store, register, onlineDays FROM registersOnline t1 WHERE 3 >= (SELECT COUNT() FROM registersOnline t2 WHERE t1.store = t2.store AND t1.register = t2.register AND t1.onlineDays <= t2.onlineDays) ) C ON A.store = C.store AND A.register = C.register AND B.onlineDays > C.onlineDays LEFT JOIN ( SELECT store, register, onlineDays FROM registersOnline t1 WHERE 4 >= (SELECT COUNT() FROM registersOnline t2 WHERE t1.store = t2.store AND t1.register = t2.register AND t1.onlineDays <= t2.onlineDays) ) D ON A.store = D.store AND A.register = D.register AND C.onlineDays > D.onlineDays LEFT JOIN ( SELECT store, register, onlineDays FROM registersOnline t1 WHERE 5 >= (SELECT COUNT() FROM registersOnline t2 WHERE t1.store = t2.store AND t1.register = t2.register AND t1.onlineDays <= t2.onlineDays) ) E ON A.store = E.store AND A.register = E.register AND D.onlineDays > E.onlineDays LEFT JOIN ( SELECT store, register, onlineDays FROM registersOnline t1 WHERE 6 >= (SELECT COUNT() FROM registersOnline t2 WHERE t1.store = t2.store AND t1.register = t2.register AND t1.onlineDays <= t2.onlineDays) ) F ON A.store = F.store AND A.register = F.register AND E.onlineDays > F.onlineDays LEFT JOIN ( SELECT store, register, onlineDays FROM registersOnline t1 WHERE 7 >= (SELECT COUNT(*) FROM registersOnline t2 WHERE t1.store = t2.store AND t1.register = t2.register AND t1.onlineDays <= t2.onlineDays) ) G ON A.store = G.store AND A.register = G.register AND F.onlineDays > G.onlineDays GROUP BY A.store, A.register, A.onlineDays, B.onlineDays, C.onlineDays, D.onlineDays, E.onlineDays, F.onlineDays, G.onlineDays ) Tbl

Subject:	Solution to puzzles 1,2,3
Posted by:	smcdanie (view profile)
Posted on:	Wednesday, March 19, 2008 at 2:21 PM
Message:	declare @ROWS int set @ROWS=7 /******************************************************************************************************************** select last @ROWS days satisfies first puzzle and 2nd puzzle by only chaning the @ROWS variable *******************************************************************************************************************/ select c.store, c.register, a.rownum, isnull(b.onlineDays, '1/1/1900') onlineDays from ( select distinct store, register from registersOnline ) c inner join ( --create rownum from 1 to 100 select a.rownum + ((b.rownum-1)10) as rownum from (select 1 as rownum union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10) a inner join (select 1 as rownum union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10) b on 1=1 ) a on 1=1 -- cartesian product ON PURPOSE left join ( select ( select count() from registersOnline b where a.register=b.register and a.store=b.store and b.onlineDays <= a.onlineDays ) rownum, from registersOnline a ) b on a.rownum=b.rownum and c.store=b.store and c.register=b.register where a.rownum <= @ROWS order by store, register, rownum /******************************************************************************************************************** create cross-tab *******************************************************************************************************************/ select store, register , max(case when rownum=1 then onlineDays else null end) as day1 , max(case when rownum=2 then onlineDays else null end) as day2 , max(case when rownum=3 then onlineDays else null end) as day3 , max(case when rownum=4 then onlineDays else null end) as day4 , max(case when rownum=5 then onlineDays else null end) as day5 , max(case when rownum=6 then onlineDays else null end) as day6 , max(case when rownum=7 then onlineDays else null end) as day7 from ( select c.store, c.register, a.rownum, isnull(b.onlineDays, '1/1/1900') onlineDays from ( select distinct store, register from registersOnline ) c inner join ( --create rownum from 1 to 100 select a.rownum + ((b.rownum-1)10) as rownum from (select 1 as rownum union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10) a inner join (select 1 as rownum union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10) b on 1=1 ) a on 1=1 -- cartesian product ON PURPOSE left join ( select ( select count() from registersOnline b where a.register=b.register and a.store=b.store and b.onlineDays <= a.onlineDays ) rownum, from registersOnline a ) b on a.rownum=b.rownum and c.store=b.store and c.register=b.register where a.rownum <= @ROWS ) a group by store, register

Subject:	Re: StefanG solutions
Posted by:	Alex Kozak (view profile)
Posted on:	Thursday, March 20, 2008 at 12:14 AM
Message:	Everything looks good. However, the solution for 100 rows in each group (puzzle 2a) is extremely slow. (It was running on my regular laptop with 1 GB of RAM for more than 10 min. and then I cancelled the execution.) The cross-tab solution looks quite heavy as well. It would be not easy to expand that query, if you will be asked for additional days (let’s say twelve instead of seven). Overall, the solutions are valid. Thanks

Subject:	SQL 2005 Puzzle 3
Posted by:	puzsol (view profile)
Posted on:	Thursday, March 20, 2008 at 12:20 AM
Message:	Here is a slightly simpler (no view) version that will work nicely for the 7 dates problem on SQL 2005... I need to think a bit more for SQL 2000, and the 1000 rows... (reminds me of something similar I had to do for a reporting function... get the first five faults listed for a device by client etc... except in my version I had to replace null with a blank string.) select store, register , isnull([1], cast('1900/Jan/01' as datetime)) as Date1 , isnull([2], cast('1900/Jan/01' as datetime)) as Date2 , isnull([3], cast('1900/Jan/01' as datetime)) as Date3 , isnull([4], cast('1900/Jan/01' as datetime)) as Date4 , isnull([5], cast('1900/Jan/01' as datetime)) as Date5 , isnull([6], cast('1900/Jan/01' as datetime)) as Date6 , isnull([7], cast('1900/Jan/01' as datetime)) as Date7 from ( select store, register, onlineDays, ROW_NUMBER() over (partition by store, register order by onlineDays desc) as pos from registersOnline ) src pivot ( max(onlineDays) for pos in ([1], [2], [3], [4], [5], [6], [7]) ) as pvt order by store, register

Subject:	Re: smcdanie solutions
Posted by:	Alex Kozak (view profile)
Posted on:	Thursday, March 20, 2008 at 12:30 AM
Message:	The solutions are very nice. You are almost there. However, I explicitly asked do not use the constructions like: select a.rownum + ((b.rownum-1)*10) as rownum from (select 1 as rownum union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10) a inner join (select 1 as rownum union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10) b on 1=1

Subject:	SQL 2005 Puzzle 1 & 2
Posted by:	puzsol (view profile)
Posted on:	Thursday, March 20, 2008 at 1:17 AM
Message:	Ok, here is a solution for SQL 2005... if you are using SQL 2000 you are on your own... for 7, 100, or however many rows you want, just set the variable to the number you want and fire away: declare @count as int select @count = 100 select ro.store, ro.register, rs.onlineDays from ( select distinct store, register from registersOnline ) as ro cross apply ( select top (@count) onlineDays from registersOnline where store = ro.store and register = ro.register order by onlineDays desc union all select top ( case when (select count(onlineDays) from registersOnline where store = ro.store and register = ro.register) >= @count then 0 else @count - (select count(onlineDays) from registersOnline where store = ro.store and register = ro.register) end ) cast('1900/Jan/01' as datetime) as onlineDays from registersOnline ) as rs order by ro.store, ro.register, rs.onlineDays desc

Subject:	My attempt
Posted by:	Phil Factor (view profile)
Posted on:	Thursday, March 20, 2008 at 3:13 AM
Message:	My version worked in 2005 but not 2000. It worked fine in 'compatibility mode' but tripped over a bug in SQL Server 2000. There is a simple work-around that produces code that works in both versions. DECLARE @accumulation VARCHAR(7000), @register int, @store int DECLARE @grouping TABLE ( MyID INT IDENTITY(1, 1), store int, register int, onlineDays datetime, accumulation VARCHAR(7000) ) insert into @Grouping (store, register, onlineDays) Select store, register, onlineDays from registersonline order by store, register, onlineDays UPDATE @grouping SET @Accumulation = [accumulation] = coalesce(CASE WHEN store<>COALESCE(@store,0) or register<>COALESCE(@register,0) THEN ' '+convert(char(11),onlineDays, 113) ELSE @Accumulation+', '+convert(char(11),onlineDays, 113) END,''), @Store =store, @register = register SELECT store, register, [online-Days] = right(ltrim(MAX(accumulation)),89) FROM @grouping GROUP BY store, register order by store, register Yup. the only real change is to add a... ' '+ ...to the line ... THEN ' '+convert(char(11),onlineDays, 113) ...and you can try taking it out and seeing it fail as a result! I hit upon the workaround by accident. Sorry to cheat so floridly, but I'm amazed by the problems one can solve by doing this sort of update on a table. I still reckon that it is this report that the manager originally wanted.

Subject:	SQL 2000 Puzzle 1 & 2
Posted by:	puzsol (view profile)
Posted on:	Thursday, March 20, 2008 at 3:17 AM
Message:	Ok, so I couldn't help myself... and forgive me the trick to generate a rownumber (rank) for the dates... but hey it works and it's pretty quick 2seconds for 100 dates per register... (I still much prefer the 2005 version... but hey, if you are stuck in the dark ages... hehehe) declare @count as int select @count = 100 SELECT ro.store, ro.register, ro.onlineDays FROM registersOnline as ro WHERE ro.onlineDays IN ( SELECT TOP (@count) ri.onlineDays FROM registersOnline as ri WHERE ri.store = ro.store AND ri.register = ro.register ORDER BY ri.onlineDays Desc ) union all ( select ro.store, ro.register, dd.onlineDays from ( select store, register, (@count - count()) as rows FROM registersOnline group by store, register having count() < @count ) as ro , ( select [rank] = count(*), cast('1900/Jan/01' as datetime) as onlineDays from (select distinct top (@count) store from registersOnline order by store) as ro1 , (select distinct top (@count) store from registersOnline order by store) as ro2 where ro1.store >= ro2.store group by ro1.store ) as dd where dd.[rank] <= ro.rows ) order by ro.store, ro.register, ro.onlineDays desc

Subject:	SQL 2000 Part 2b
Posted by:	puzsol (view profile)
Posted on:	Thursday, March 20, 2008 at 3:23 AM
Message:	Oh yeah, and for part b (min instead of simple date), just replace: dd.onlineDays (in the union select statement) with: (select min(onlineDays) from registersOnline where store = ro.store and register = ro.register) as onlineDays

Subject:	SQL 2000 Part 3
Posted by:	puzsol (view profile)
Posted on:	Thursday, March 20, 2008 at 4:05 AM
Message:	If you can put up with the use of the last date instead of the specific date, then you can get away with this to create the cross tab report (ok so it's a bit simplistic, but it doesn't take too long either) : select ro.store , ro.register , (select min(i.onlineDays) from (select top 1 onlineDays from registersOnline where store = ro.store and register = ro.register order by onlineDays desc) as i) as Date1 , (select min(i.onlineDays) from (select top 2 onlineDays from registersOnline where store = ro.store and register = ro.register order by onlineDays desc) as i) as Date2 , (select min(i.onlineDays) from (select top 3 onlineDays from registersOnline where store = ro.store and register = ro.register order by onlineDays desc) as i) as Date3 , (select min(i.onlineDays) from (select top 4 onlineDays from registersOnline where store = ro.store and register = ro.register order by onlineDays desc) as i) as Date4 , (select min(i.onlineDays) from (select top 5 onlineDays from registersOnline where store = ro.store and register = ro.register order by onlineDays desc) as i) as Date5 , (select min(i.onlineDays) from (select top 6 onlineDays from registersOnline where store = ro.store and register = ro.register order by onlineDays desc) as i) as Date6 , (select min(i.onlineDays) from (select top 7 onlineDays from registersOnline where store = ro.store and register = ro.register order by onlineDays desc) as i) as Date7 from registersOnline ro group by ro.store, ro.register order by ro.store, ro.register

Subject:	SQL 2000 Part 3b
Posted by:	puzsol (view profile)
Posted on:	Thursday, March 20, 2008 at 4:15 AM
Message:	... if you have to have the fixed date then this (bit longer) query does the job: select ro.store , ro.register , case when count() >= 1 then (select min(i.onlineDays) from (select top 1 onlineDays from registersOnline where store = ro.store and register = ro.register order by onlineDays desc) as i) else cast('1900/Jan/01' as datetime) end as Date1 , case when count() >= 2 then (select min(i.onlineDays) from (select top 2 onlineDays from registersOnline where store = ro.store and register = ro.register order by onlineDays desc) as i) else cast('1900/Jan/01' as datetime) end as Date2 , case when count() >= 3 then (select min(i.onlineDays) from (select top 3 onlineDays from registersOnline where store = ro.store and register = ro.register order by onlineDays desc) as i) else cast('1900/Jan/01' as datetime) end as Date3 , case when count() >= 4 then (select min(i.onlineDays) from (select top 4 onlineDays from registersOnline where store = ro.store and register = ro.register order by onlineDays desc) as i) else cast('1900/Jan/01' as datetime) end as Date4 , case when count() >= 5 then (select min(i.onlineDays) from (select top 5 onlineDays from registersOnline where store = ro.store and register = ro.register order by onlineDays desc) as i) else cast('1900/Jan/01' as datetime) end as Date5 , case when count() >= 6 then (select min(i.onlineDays) from (select top 6 onlineDays from registersOnline where store = ro.store and register = ro.register order by onlineDays desc) as i) else cast('1900/Jan/01' as datetime) end as Date6 , case when count(*) >= 7 then (select min(i.onlineDays) from (select top 7 onlineDays from registersOnline where store = ro.store and register = ro.register order by onlineDays desc) as i) else cast('1900/Jan/01' as datetime) end as Date7 from registersOnline ro group by ro.store, ro.register order by ro.store, ro.register

Subject:	Better solution for part 3
Posted by:	StefanG (view profile)
Posted on:	Thursday, March 20, 2008 at 4:19 AM
Message:	Works for all databases Extremely fast Can easily be extended to more days select store , register , MAX(CASE WHEN rank=1 THEN onlinedays ELSE '19000101' END) as day1 , MAX(CASE WHEN rank=2 THEN onlinedays ELSE '19000101' END) as day2 , MAX(CASE WHEN rank=3 THEN onlinedays ELSE '19000101' END) as day3 , MAX(CASE WHEN rank=4 THEN onlinedays ELSE '19000101' END) as day4 , MAX(CASE WHEN rank=5 THEN onlinedays ELSE '19000101' END) as day5 , MAX(CASE WHEN rank=6 THEN onlinedays ELSE '19000101' END) as day6 , MAX(CASE WHEN rank=7 THEN onlinedays ELSE '19000101' END) as day7 from ( select store , register , onlinedays , ( select count(*) from registersonline r2 where r1.store=r2.store and r1.register=r2.register and r2.onlinedays <= r1.onlinedays ) as rank from registersonline r1 ) dt where rank <= 7 group by store, register order by store, register

Subject:	RE: puzsol SQL 2000 Puzzle 1 & 2
Posted by:	StefanG (view profile)
Posted on:	Thursday, March 20, 2008 at 5:03 AM
Message:	Your solution does not work on SQL 2000 The SELECT TOP (@variable) syntax was introduced in SQL2005. In SQL2000 you could only use an integer constant - not a variable to specify the number of rows to return for TOP

Subject:	Re: Me
Posted by:	Phil Factor (view profile)
Posted on:	Thursday, March 20, 2008 at 6:59 AM
Message:	No. PuzSol, I'm enjoying it.

Subject:	My solutions (puzzle 3)
Posted by:	Alex Kozak (view profile)
Posted on:	Tuesday, March 25, 2008 at 4:49 PM
Message:	OK, here are my solutions. I will start from the easiest one, from the cross-tab report. My solution is the same as the last solution of StefanG: SELECT store, register, MAX(CASE WHEN D_RANK = 1 THEN onlineDays ELSE 'Jan 01, 1900' END) I, MAX(CASE WHEN D_RANK = 2 THEN onlineDays ELSE 'Jan 01, 1900' END) II, MAX(CASE WHEN D_RANK = 3 THEN onlineDays ELSE 'Jan 01, 1900' END) III, MAX(CASE WHEN D_RANK = 4 THEN onlineDays ELSE 'Jan 01, 1900' END) IV, MAX(CASE WHEN D_RANK = 5 THEN onlineDays ELSE 'Jan 01, 1900' END) V, MAX(CASE WHEN D_RANK = 6 THEN onlineDays ELSE 'Jan 01, 1900' END) VI, MAX(CASE WHEN D_RANK = 7 THEN onlineDays ELSE 'Jan 01, 1900' END) VII FROM (SELECT store, register, onlineDays, (SELECT COUNT() FROM registersOnline t2 WHERE t1.store = t2.store AND t1.register = t2.register AND t2.onlineDays >= t1.onlineDays) AS D_RANK FROM registersOnline t1) t3 GROUP BY store, register ORDER BY store ASC, register ASC The only difference is that I didn’t use WHERE clause (where rank <= 7), because SELECT list filters data by itself. The variation of that solution would be SELECT store, register, MAX(CASE WHEN D_RANK = 1 THEN Days ELSE '19000101' END) + SPACE(5) + MAX(CASE WHEN D_RANK = 2 THEN Days ELSE '19000101' END) + SPACE(5) + MAX(CASE WHEN D_RANK = 3 THEN Days ELSE '19000101' END) + SPACE(5) + MAX(CASE WHEN D_RANK = 4 THEN Days ELSE '19000101' END) + SPACE(5) + MAX(CASE WHEN D_RANK = 5 THEN Days ELSE '19000101' END) + SPACE(5) + MAX(CASE WHEN D_RANK = 6 THEN Days ELSE '19000101' END) + SPACE(5) + MAX(CASE WHEN D_RANK = 7 THEN Days ELSE '19000101' END) days FROM (SELECT store, register, convert(varchar(20), onlineDays, 107) Days , (SELECT COUNT() FROM registersOnline t2 WHERE t1.store = t2.store AND t1.register = t2.register AND t2.onlineDays >= t1.onlineDays) AS D_RANK FROM registersOnline t1) t3 GROUP BY store, register ORDER BY store ASC, register ASC The main drawback of my solution is that I need to rewrite the query each time, when the number of days will change. The other cross-tab solutions, including SQL Server 2005 solution with PIVOT operator have the same problem and cannot be automated, unless you build query dynamically or use some kind of recursion. From that point of view, the solution of Phil Factor with UPDATE does a nice job, though you need to calculate and cut an accumulated string according to the output format [online-Days] = right(ltrim(MAX(accumulation)),89)

Subject:	My solutions (puzzle1(2))
Posted by:	Alex Kozak (view profile)
Posted on:	Tuesday, March 25, 2008 at 4:53 PM
Message:	The solutions for puzzle 1 & 2 were a little bit more complicated. The trick was to generate the sequence of the numbers, using table registersonline only. My solution is similar to what puzsol did: DECLARE @seed int, @numInGroup int SELECT @seed = 10, @numInGroup = 7 SELECT store, register, onlineDays FROM registersOnline t1 WHERE @numInGroup >= (SELECT COUNT() FROM registersOnline t2 WHERE t1.store = t2.store AND t1.register = t2.register AND t1.onlineDays <= t2.onlineDays) UNION ALL SELECT store, register, CAST(0 AS smalldatetime) FROM (SELECT num = (t1.c1 + @seed (t2.c1)) - @seed FROM (SELECT c1 = COUNT(G2.store) FROM (select distinct store from registersOnline) AS G1 INNER JOIN (select distinct store from registersOnline) AS G2 ON G1.store <= G2.store GROUP BY G2.store HAVING COUNT(G2.store) <= @seed) t1 CROSS JOIN (SELECT c1 = COUNT(G2.store) FROM (select distinct store from registersOnline) AS G1 INNER JOIN (select distinct store from registersOnline) AS G2 ON G1.store <= G2.store GROUP BY G2.store HAVING COUNT(G2.store) <= @seed) t2) t1 CROSS JOIN (SELECT COUNT() #days, store, register FROM registersOnline GROUP BY store, register) t2 WHERE t1.num <= (SELECT @numInGroup - COUNT() FROM registersOnline t3 WHERE t2.store = t3.store and t2.register = t3.register GROUP BY store, register) ORDER BY store, register, onlineDays desc I used two variables: @seed - helps to control generated numbers; the quantity of generated numbers is equal to @seed * @seed. @numInGroup – the number of days that has to be in each store-register group. To produce 7 days in each group, use the parameters SELECT @seed = 10, @numInGroup = 7 To produce 100 days in each group, use the parameters SELECT @seed = 10, @numInGroup = 100 To produce 200 days in each group, use the parameters SELECT @seed = 15, @numInGroup = 200 , and so on. The solution is limited by amount of the numbers that can be generated. Since table registersOnline has 228 distinct stores, the maximum capacity of each group can be 228 * 228 = 51984. Then, running the query with SELECT @seed = 228, @numInGroup = 51984 , you will get 522 * 51984 = 27,135,648 rows (it took me 4 minutes). Finally, you can bypass the limit of 228 * 228, by extending the part of the query that produces the numbers. In that case you can get 228 * 228 * 228 numbers or 228 * 228 * 228 * 228 numbers and so on.

Subject:	My solutions (puzzle 2)
Posted by:	Alex Kozak (view profile)
Posted on:	Tuesday, March 25, 2008 at 4:55 PM
Message:	In order to fill the missing dates in the group with MAX (or MIN) date in that group (instead of ‘Jan 01, 1900’), I slightly changed the previous solution: DECLARE @seed int, @numInGroup int SELECT @seed = 10, @numInGroup = 7 SELECT store, register, onlineDays FROM registersOnline t1 WHERE @numInGroup >= (SELECT COUNT() FROM registersOnline t2 WHERE t1.store = t2.store AND t1.register = t2.register AND t1.onlineDays <= t2.onlineDays) UNION ALL SELECT store, register, MaxDay -- here is the change FROM (SELECT num = (t1.c1 + @seed (t2.c1)) - @seed FROM (SELECT c1 = COUNT(G2.store) FROM (select distinct store from registersOnline) AS G1 INNER JOIN (select distinct store from registersOnline) AS G2 ON G1.store <= G2.store GROUP BY G2.store HAVING COUNT(G2.store) <= @seed) t1 CROSS JOIN (SELECT c1 = COUNT(G2.store) FROM (select distinct store from registersOnline) AS G1 INNER JOIN (select distinct store from registersOnline) AS G2 ON G1.store <= G2.store GROUP BY G2.store HAVING COUNT(G2.store) <= @seed) t2) t1 CROSS JOIN (SELECT MAX(onlineDays) MaxDay, store, register -- here is the change FROM registersOnline GROUP BY store, register) t2 WHERE t1.num <= (SELECT @numInGroup - COUNT(*) FROM registersOnline t3 WHERE t2.store = t3.store and t2.register = t3.register GROUP BY store, register) ORDER BY store, register, onlineDays desc

Subject:	Alex Kozak's Solution 1&2
Posted by:	puzsol (view profile)
Posted on:	Monday, March 31, 2008 at 2:56 AM
Message:	For some reason the query seems faster than my 2000 version... I'll work out why later.... Though, I was wondering why you use WHERE t1.num <= (SELECT @numInGroup - COUNT(*) FROM registersOnline t3 WHERE t2.store = t3.store and t2.register = t3.register GROUP BY store, register) instead of: WHERE t1.num <= (@numInGroup - t2.#days) which seems to produce the same result... Also, I must say, I'm a little disappointed, as you still used a method of generating a row-number for the dummy values... the way the question was written I thought you had found some other method.

Subject:	Re: Alex Kozak's Solution 1&2
Posted by:	Alex Kozak (view profile)
Posted on:	Monday, March 31, 2008 at 1:29 PM
Message:	Puzsol, you are right. I can use (SELECT COUNT() #days, store, register FROM registersOnline GROUP BY store, register) t2 WHERE t1.num <= @numInGroup - #days instead of (SELECT COUNT() #days, store, register FROM registersOnline GROUP BY store, register) t2 WHERE t1.num <= (SELECT @numInGroup - COUNT() FROM registersOnline t3 WHERE t2.store = t3.store and t2.register = t3.register GROUP BY store, register) In that case my query will be faster by 50-100 ms. I also can go farther and change that piece as (SELECT COUNT() #days, store, register FROM registersOnline GROUP BY store, register HAVING COUNT(*) <= @numInGroup) t2 WHERE t1.num <= @numInGroup - #days In that case I will reduce the number of logical reads by another 10000-15000 and will make my query to run faster by another 50-60 ms. Honestly, I had never touched that piece of code after I got it working the first time. Why I wrote it this way, but not that way – I don’t know. In regards to your frustration, I should notice that I had never asked do not build the internal sequences of the numbers. All I asked is to use table registersOnline only. This is similar to another puzzle: CREATE TABLE tbl_numbers(num int) INSERT INTO tbl_numbers value(1) INSERT INTO tbl_numbers value(10000) INSERT INTO tbl_numbers value(777777) Try to generate all missing numbers, using table tbl_numbers only. Thanks for your note.

Subject:	Difference in speed
Posted by:	puzsol (view profile)
Posted on:	Monday, March 31, 2008 at 6:45 PM
Message:	Alex, I found that the main difference in speed between the two versions was the first part of the query... I was using a date in construct, where you used a counting comparison (to get the top @count rows for each register) - the counting is MUCH faster, and looking at the execution plans I can see why: much less rows and easier joins. Other than that, they are fairly much the same - except that you used a square rank function to be able to generate more than 228 rows for each register. Btw, you can also put the @numInGroup - #days in the select part, instead of the where... (@numInGroup - count(*)) as #numReq... where t1.num <= #numReq not sure if it makes a difference to the execution time though.

Subject:	Re: Difference in speed
Posted by:	Alex Kozak (view profile)
Posted on:	Tuesday, April 01, 2008 at 10:48 PM
Message:	Yes, as I mentioned in my comments earlier, our solutions are similar. I agree that performance is one of the main characteristics of the query’s quality. However, for these particular puzzles, I didn’t want to compare the solutions by performance (though I made the comments about some notably slow solutions). I just wanted to see the ideas and results. From that point of view, I should admit that you did a great job, finding your solution. I know, this is interesting, but not easy task to solve the puzzles. Thanks for your performance analysis.

Subject:	The winner
Posted by:	Tony Davis (view profile)
Posted on:	Thursday, April 03, 2008 at 4:56 AM
Message:	Thank you to everyone who contributed. Some excellent work went into these solutions. The author has chosen puzsol as the winner of the $50 Amazon voucher, with very honorable mentions for StefanG and smcdanie and Phil Factor. Cheers, Tony.

Subject:	Missing Date Ranges
Posted by:	Abhishek (view profile)
Posted on:	Friday, May 09, 2008 at 7:46 AM
Message:	Dear Alex, I read your article about finding missing date ranges. Could you please point me to correct solution of a similar problem? My problem has two Columns DateFrom and DateTo (both columns contain date and time values). Problem is to find missing date ranges regards, Abhishek