Grouping data to move up and down in a Table depending on Month day I am using TSQL.  I have a set of data in a table. I have a stored procedure that runs every day that populates this table. The thing is I need the order of the data to change each day of the month. This data needs to dynamically split up into 7 groups.

For example:

I have 300 pieces of data, I run the query on the first day and I have items 1-300. The second day I would run it and I would have at the top of my list 43-300 and at the bottom 1-42 (in that order). The third day I would have 86-300 on top and then 1-85 on the bottom (in that order). The fourth day and so on throughout the month would be continuing this. 

I already have the initial sort, which has to remain in tack. I need a secondary sort (grouping) that would allow this movement.

I am not sure how to do this. Could anyone give me any ideas on how to do such a thing. I know how to do this if it was based on the week, but month is a bit more confusing.
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.