create table #Numbers(DateEntered datetime, Number int)
go

insert #Numbers values('07/01/00 12:00:00 PM', 1)
insert #Numbers values('07/01/00 12:01:00 PM', 2)
insert #Numbers values('07/01/00 12:02:00 PM', 3)
insert #Numbers values('07/02/00 12:00:00 PM', 998)
insert #Numbers values('07/02/00 12:01:00 PM', 999)
insert #Numbers values('07/02/00 12:02:00 PM', 1)
insert #Numbers values('07/03/00 12:00:00 PM', 998)
insert #Numbers values('07/03/00 12:01:00 PM', 1)
insert #Numbers values('07/03/00 12:02:00 PM', 999)
insert #Numbers values('07/03/00 12:03:00 PM', 2)
insert #Numbers values('07/03/00 12:04:00 PM', 3)
go

/*
The nature of this data is as follows:

>> A Number will never be duplicated on the same *day*.
>> The Number values are sequenced 1-999, wrapping back to 1 after 999
>> (1, 2, 3, 4, ..., 997, 998, 999, 1, 2, 3, ...)
>> There may be gaps in the data.
>> The data in the Numbers table can be *entered* out of order as in the
>> 07/03/00 examples
>> If 999 occurs in the same *day* as 1, everything prior to 999 must be
>> ordered *before* 1 and greater.  
>> The numbers will always be within a 500-number delta 
*/

-- To understand the query, you can analyze the output of
-- each derived table separately.
SELECT nn.DateEntered , nn.Number
FROM (
SELECT n.DateEntered , n.Number , 
       -- If 1 & 999 occur on same day And
       -- If ( Number < 999 & DateEntered is prior to Number = 1 ) Or
       -- ( Number = 999 ) then use DateEntered of Number = 1 itself
       CASE WHEN ( n.SameDay = 1 ) And
                 ( ( n.Number < 999 And n.DateEntered < n.OneDateEntered ) Or
                   ( n.Number = 999 ) )
                THEN n.OneDateEntered
        END AS NewDateEntered ,
        -- If 1 & 999 occur on same day, offset 1 to 1000 for sorting
        CASE WHEN SameDay = 1 And n.Number = 1
                THEN 1000
        END AS NewNumber
FROM  (
SELECT n1.DateEntered , n1.Number ,
       -- Mark rows where 1 & 999 occur on the same day
       ( CASE WHEN ( SELECT COUNT( * ) FROM #Numbers n2
                      WHERE n2.Number in ( 999 , 1 ) And
                            CONVERT( varchar , n1.DateEntered , 101 ) = 
                                CONVERT( varchar , n2.DateEntered , 101 ) ) = 2
                THEN 1
         END ) AS SameDay ,
        -- Get DateEntered for Number = 1, used later.
        ( SELECT n4.DateEntered FROM #Numbers n4
          WHERE CONVERT( varchar , n1.DateEntered , 101 ) = 
                CONVERT( varchar , n4.DateEntered , 101 ) And
                n4.Number = 1
        ) AS OneDateEntered
FROM #Numbers as n1
) AS n
) AS nn
-- Use the NewDateEntered and NewNumber values if present for sorting
ORDER BY COALESCE( nn.NewDateEntered , nn.DateEntered ) ,
         COALESCE( nn.NewNumber , nn.Number )

-- The query below was suggested by SQL Server MVP "BP Margolin"
SELECT DateEntered, Number
FROM (SELECT n1.*, minNumber, maxNumber
      FROM #Numbers as n1
            -- Find min & maximum number for each day
      JOIN (SELECT DateEntered = convert(char(10), DateEntered, 102),
                       minNumber = MIN( Number ),
                       maxNumber = MAX( Number )
            FROM #Numbers
            GROUP BY convert(char(10), DateEntered, 102)
      ) AS n2
      ON convert(char(10), n1.DateEntered, 102) =
           convert(char(10), n2.DateEntered, 102)
) AS n3
ORDER BY convert(char(10), DateEntered, 102),
         -- Since the values will be within 500-number DELTA , use that to
         -- offset values from 1 to 499
         CASE WHEN maxNumber <= minNumber + 500 THEN Number ELSE NULL END ,
         -- offset values from >= 500
         CASE WHEN maxNumber > minNumber + 500 
                 THEN ( CASE WHEN Number >= maxNumber - 500
                              THEN Number
                              ELSE Number + 1000
                        END )
                 ELSE NULL
         END

This page was last updated on May 01, 2006 04:28 PM.