Grouping data to move up and down in a Table depending on Month day
Lets assume that the query is:

SELECT 
    -- long list
FROM 
    -- complex joins
WHERE
    -- multiple conditions
ORDER BY 
    a, b ;


and that the cutting numbers (0, 42, 85) can be found by some functions fun(@X) where @X is a number from 1 to 7. Then we can use a CASE expression in the ORDER BY:

SELECT 
    -- long list
FROM 
    -- complex joins
WHERE
    -- multiple conditions
ORDER BY
    -- the "cutoff"  
    CASE WHEN ROW_NUMBER() OVER (ORDER BY a,b) &gt; fun(@X)
        THEN 0 ELSE 1 
    END,
    a, b ;


If the cutoff is as simple as diving the rows into 7 equal (or almost) parts, then we can use ((@X-1) * COUNT(*) OVER ()) / 7 as fun(X), where @X is between 1 and 7.

If the total count of the returned rows is 300, the expression will give 0, 42, 85, 128, 171, 214 and 257.