GET_PARTITIONS.sql

DECLARE @TASKS TABLE ( TASK_ID INT IDENTITY PRIMARY KEY , TASK_NAME VARCHAR(256), TASK_COST FLOAT , PARTITION_ID INT DEFAULT  0)

DECLARE @MAX_PARTITION INT = 4 

WITH INIT_TASKS AS
 (SELECT TOP @MAX_PARTITION  
         ROW_NUMBER(OVER() ORDER BY TASK_COST DESC) AS INIT_PARTITION
        ,PARTITION_ID
    FROM @TASKS
   ORDER BY TASK_COST DESC) 
 UPDATE INIT_TASKS 
    SET PARTITION_ID = INIT_PARTITION



WHILE EXISTS (SELECT * FROM @TASKS WHERE PARTITION_ID = 0)
     WITH HIGEST_COST AS
      (SELECT TOP 1 *
         FROM @TASKS
        WHERE PARTITION_ID = 0
        ORDER BY TASK_COST DESC)     
     UPDATE   HIGEST_COST SET PARTITION_ID =
      (SELECT TOP 1 PARTITION_ID
         FROM @TASKS
        WHERE PARTITION_ID <> 0
        GROUP BY PARTITION_ID
        ORDER BY SUM(TASK_COST))


SELECT * FROM @TASKS