1

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.

  • How are the "cutting" numbers defined? (43, 86, etc) – ypercubeᵀᴹ Jan 10 '17 at 21:45
  • the rows are being cut up into 7 groups. So I just took 300 and divided by 7 to get ~43. So 1-42 is 43 rows and 1-85 is 86 rows and so on of the theoretical data. I think I have to use NTile. But the performance doesn't seem very good. – Sevanna Starks Jan 10 '17 at 22:05
  • NTile is the right choice for sorting data into an arbitrary number of 'containers' based on some evaluation – Duffy Jan 10 '17 at 22:22
  • I ended up doing something a bit different then NTIle. NTILE made the execution plan look way too complicated and was a bit more labor intensive. – Sevanna Starks Jan 10 '17 at 23:31
0

So I ended up doing the following Idea:

DECLARE
@Page           INT,
@PageSize           INT,
@NumberofPartions   INT
SET @NumberofPartions = 7
SELECT @pagesize= CEILING(CAST(COUNT(*) AS FLOAT)/7) FROM Table1
SELECT @page = DAY(GETDATE()) 

SELECT @page =
CASE
    WHEN @page IN (1,8,15,22,29) THEN 1
    WHEN @page IN (2,9,16,23,30) THEN 2
    WHEN @page IN (3,10,17,24,31) THEN 3
    WHEN @page IN (4,11,18,25) THEN 4
    WHEN @page IN (5,12,19,26) THEN 5
    WHEN @page IN (6,13,20,27) THEN 6
    ELSE 7
    END;
WITH 
numbered_Table1 AS
    (
    SELECT 
        t1.*,
        ROW_NUMBER() OVER (ORDER BY t1.IndexID) [rownumber] 
    FROM 
        Table1 t1
    )
SELECT * 
FROM numbered_Table1 cHOA

ORDER BY
CASE WHEN cHOA.rownumber BETWEEN (@page -1) * @pagesize AND  @page     * @pagesize THEN 1
     WHEN cHOA.rownumber BETWEEN  @page     * @pagesize AND (@page +1) * @pagesize THEN 2
     WHEN cHOA.rownumber BETWEEN (@page +1) * @pagesize AND (@page +2) * @pagesize THEN 3
     WHEN cHOA.rownumber BETWEEN (@page +2) * @pagesize AND (@page +3) * @pagesize THEN 4
     WHEN cHOA.rownumber BETWEEN (@page +3) * @pagesize AND (@page +4) * @pagesize THEN 5
     WHEN cHOA.rownumber BETWEEN (@page +4) * @pagesize AND (@page +5) * @pagesize THEN 6
     WHEN cHOA.rownumber BETWEEN (@page +5) * @pagesize AND (@page +6) * @pagesize THEN 7
     WHEN cHOA.rownumber BETWEEN (@page +7) * @pagesize AND (@page +7) * @pagesize THEN 8
     WHEN cHOA.rownumber BETWEEN (@page -7) * @pagesize AND (@page -6) * @pagesize THEN 9
     WHEN cHOA.rownumber BETWEEN (@page -6) * @pagesize AND (@page -5) * @pagesize THEN 10
     WHEN cHOA.rownumber BETWEEN (@page -5) * @pagesize AND (@page -4) * @pagesize THEN 11
     WHEN cHOA.rownumber BETWEEN (@page -4) * @pagesize AND (@page -3) * @pagesize THEN 12
     WHEN cHOA.rownumber BETWEEN (@page -3) * @pagesize AND (@page -2) * @pagesize THEN 13
     WHEN cHOA.rownumber BETWEEN (@page -2) * @pagesize AND (@page -1) * @pagesize THEN 14
     END,
cHOA.IndexID

Was there a better way?

0

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) > 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.

  • This is similar to what I ended up doing. – Sevanna Starks Jan 10 '17 at 23:25
  • Yes, the idea is the same but the execution more complicated ;) – ypercubeᵀᴹ Jan 11 '17 at 0:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.