How to spill over quantities to following columns when cumulative sum crosses boundary

Question

I have to display dynamic columns in which the sum of qty is equal to 1000. Up to the first boundary those rows are displayed as COL1. After that start from that row, and row by row add the qty and until the sum is 1000 again. Then those are display in COL2. Then the same procedure for COL3 and so on.

This is my source table ....

  ID    QTY
-------------
  1     240
  2     101
  3     43
  4     43
  5     24
  6     43
  7     59
  8     11
  9     65
  10    200
  11    16
  12    1
  13    195
  14    50
  15    40

The expected output:

ID  COL1  COL2
1    240    0
2    101    0
3     43    0
4     43    0
5     24    0
6     43    0
7     59    0
8     11    0
9     65    0
10    200   0
11     16   0
12      1   0
13    154   41
14      0   50
15      0   40

score 2 · Accepted Answer · edited May 23 '17 at 12:19

Solution for SQL Server 2012+

As you probably guessed this is done by using Dynamic SQL. Following steps are taken:

Populate a staging table (#staging) with the required columns all having the SUM of all previous rows: SUM(qty) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). This is done to be able to use LAG in the next step. Reason: you can't have a windowed function over another windowed function.
Select from the staging table using the overflow mechanism. This uses the LAG function to see if the upper or lower boundary was crossed.

The procedure uses the following constructs:

Windowing functions: SUM() OVER(...), LAG(...) OVER(...) and ROW_NUMBER() OVER(...).
Tally tables for numbering: see this SO thread for more info.
FOR XML PATH('') for string concatenation.

PS: I've doubled your sample data to test for three columns.

The script:

CREATE TABLE #tt(id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,qty INT NOT NULL);
INSERT INTO #tt(qty)VALUES
(240),(101),(43),(43),(24),(43),(59),(11),(65),(200),(16),(1),(195),(50),(40),(240),(101),(43),(43),(24),(43),(59),(11),(65),(200),(16),(1),(195),(50),(40);

DECLARE @tot_cols INT=(CASE WHEN 0=(SELECT SUM(qty) FROM #tt) THEN 1 ELSE (SELECT SUM(qty) FROM #tt) END-1)/1000+1; -- 0 .. 1000 // 1001 .. 2000 // ...

DECLARE @staging_cols NVARCHAR(MAX)=(
    SELECT N',col'+n+N'=SUM(qty) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)'
    FROM (
            SELECT TOP (@tot_cols) CAST(ROW_NUMBER() OVER(ORDER BY t1.number) AS VARCHAR) AS n
            FROM master.dbo.spt_values AS t1 CROSS JOIN master.dbo.spt_values AS t2
         ) AS tally
    WHERE n<=@tot_cols
    FOR XML PATH('')
);
DECLARE @staging_create NVARCHAR(MAX)=
    N'SELECT id,qty'+@staging_cols+' INTO #staging FROM #tt;';

DECLARE @select_cols NVARCHAR(MAX)=(
    SELECT N',col'+n+N'='+
               N'CASE WHEN col'+n+N'>'+b_upper+N' ' +
                    N'THEN CASE WHEN LAG(col'+n+N') OVER (ORDER BY id)<='+b_upper+N' '+
                           N'THEN '+b_upper+N'-LAG(col'+n+N') OVER (ORDER BY id) '+
                           N'ELSE 0 '+
                           N'END ' +
                    N'WHEN col'+n+N'>'+b_lower+N' '+
                    N'THEN CASE WHEN LAG(col'+n+N') OVER (ORDER BY id)<='+b_lower+N' '+ 
                           N'THEN col'+n+N'-'+b_lower+N' '+
                           N'ELSE qty ' +
                           N'END ' +
                    N'ELSE 0 '+
                N'END'
    FROM (
            SELECT TOP (@tot_cols) CAST(ROW_NUMBER() OVER(ORDER BY t1.number) AS VARCHAR) AS n
            FROM master.dbo.spt_values t1 CROSS JOIN master.dbo.spt_values t2
         ) AS tally
         CROSS APPLY (
            SELECT b_lower=CAST(CASE WHEN n=1 THEN 0 ELSE (n-1)*1000 END AS VARCHAR),
                   b_upper=CAST(n*1000 AS VARCHAR)
         ) AS boundaries
    WHERE n<=@tot_cols
    FOR XML PATH('')
);
DECLARE @select_result NVARCHAR(MAX)=
    N'SELECT id'+REPLACE(REPLACE(@select_cols,N'&lt;',N'<'),N'&gt;',N'>')+N' FROM #staging ORDER BY id;';

DECLARE @stmt NVARCHAR(MAX)=@staging_create+@select_result+'DROP TABLE #staging;';
EXEC sp_executesql @stmt;

DROP TABLE #tt;

Solution for SQL Server 2008

This has the same basics (staging table with cumulative sum, lagging to compare to the previous value, tally table...) but uses other constructs.

2008R2 doesn't support SUM() over a window and doesn't support LAG. The cumulative sum is done using a CURSOR (the only reasonable approach in 2008R2-); lagging is done by self-linking the staging table to the previous id. Here goes:

CREATE TABLE #tt(id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,qty INT NOT NULL);
INSERT INTO #tt(qty)VALUES
(240),(101),(43),(43),(24),(43),(59),(11),(65),(200),(16),(1),(195),(50),(40),(240),(101),(43),(43),(24),(43),(59),(11),(65),(200),(16),(1),(195),(50),(40);

DECLARE @tot_cols INT=(CASE WHEN 0=(SELECT SUM(qty) FROM #tt) THEN 1 ELSE (SELECT SUM(qty) FROM #tt) END-1)/1000+1; -- 0 .. 1000 // 1001 .. 2000 // ...

DECLARE @staging_cols NVARCHAR(MAX)=(
    SELECT N',col'+n+N'=0'
    FROM (
            SELECT TOP (@tot_cols) CAST(ROW_NUMBER() OVER(ORDER BY t1.number) AS VARCHAR) AS n
            FROM master.dbo.spt_values AS t1 CROSS JOIN master.dbo.spt_values AS t2
         ) AS tally
    WHERE n<=@tot_cols
    FOR XML PATH('')
);
DECLARE @staging_create NVARCHAR(MAX)=
    N'SELECT id,qty'+@staging_cols+' INTO #staging FROM #tt;ALTER TABLE #staging ADD CONSTRAINT PK_staging PRIMARY KEY CLUSTERED (id);';

DECLARE @spillover_cols_sel NVARCHAR(MAX)=STUFF(REPLACE(@staging_cols,N'=0',N''),1,1,N'');
DECLARE @spillover_cols_upd NVARCHAR(MAX)=STUFF(REPLACE(@staging_cols,N'=0',N'=@tot_qty'),1,1,N'');
DECLARE @staging_fill NVARCHAR(MAX)=
    N'DECLARE c_s CURSOR FOR SELECT qty FROM #staging ORDER BY id FOR UPDATE OF '+@spillover_cols_sel+';'+
    N'OPEN c_s; DECLARE @qty INT; DECLARE @tot_qty INT; SET @tot_qty=0; WHILE 1=1 BEGIN '+
    N'FETCH NEXT FROM c_s INTO @qty; IF @@FETCH_STATUS<>0 BREAK; SET @tot_qty=@tot_qty+@qty;'+
    N'UPDATE #staging SET '+@spillover_cols_upd+' WHERE CURRENT OF c_s;'+
    N'END CLOSE c_s;DEALLOCATE c_s;';

DECLARE @select_cols NVARCHAR(MAX)=(
    SELECT N',col'+n+N'='+
               N'CASE WHEN bt.col'+n+N'>'+b_upper+N' ' +
                    N'THEN CASE WHEN ISNULL(lt.col'+n+N',0)<='+b_upper+N' '+
                           N'THEN '+b_upper+N'-ISNULL(lt.col'+n+N',0) '+
                           N'ELSE 0 '+
                           N'END ' +
                    N'WHEN bt.col'+n+N'>'+b_lower+N' '+
                    N'THEN CASE WHEN ISNULL(lt.col'+n+N',0)<='+b_lower+N' '+    
                           N'THEN bt.col'+n+N'-'+b_lower+N' '+
                           N'ELSE bt.qty ' +
                           N'END ' +
                    N'ELSE 0 '+
                N'END'
    FROM (
            SELECT TOP (@tot_cols) CAST(ROW_NUMBER() OVER(ORDER BY t1.number) AS VARCHAR) AS n
            FROM master.dbo.spt_values t1 CROSS JOIN master.dbo.spt_values t2
         ) AS tally
         CROSS APPLY (
            SELECT b_lower=CAST(CASE WHEN n=1 THEN 0 ELSE (n-1)*1000 END AS VARCHAR),
                   b_upper=CAST(n*1000 AS VARCHAR)
         ) AS boundaries
    WHERE n<=@tot_cols
    FOR XML PATH('')
);

DECLARE @select_result NVARCHAR(MAX)=
    N'SELECT bt.id'+REPLACE(REPLACE(@select_cols,N'&lt;',N'<'),N'&gt;',N'>')+N' '+
    N'FROM #staging AS bt LEFT JOIN #staging AS lt ON lt.id=bt.id-1 ORDER BY bt.id;';

DECLARE @stmt NVARCHAR(MAX)=@staging_create+@staging_fill+@select_result+'DROP TABLE #staging;';
EXEC sp_executesql @stmt;

DROP TABLE #tt;

Result for both scripts:

+----+------+------+------+
| id | col1 | col2 | col3 |
+----+------+------+------+
|  1 |  240 |    0 |    0 |
|  2 |  101 |    0 |    0 |
|  3 |   43 |    0 |    0 |
|  4 |   43 |    0 |    0 |
|  5 |   24 |    0 |    0 |
|  6 |   43 |    0 |    0 |
|  7 |   59 |    0 |    0 |
|  8 |   11 |    0 |    0 |
|  9 |   65 |    0 |    0 |
| 10 |  200 |    0 |    0 |
| 11 |   16 |    0 |    0 |
| 12 |    1 |    0 |    0 |
| 13 |  154 |   41 |    0 |
| 14 |    0 |   50 |    0 |
| 15 |    0 |   40 |    0 |
| 16 |    0 |  240 |    0 |
| 17 |    0 |  101 |    0 |
| 18 |    0 |   43 |    0 |
| 19 |    0 |   43 |    0 |
| 20 |    0 |   24 |    0 |
| 21 |    0 |   43 |    0 |
| 22 |    0 |   59 |    0 |
| 23 |    0 |   11 |    0 |
| 24 |    0 |   65 |    0 |
| 25 |    0 |  200 |    0 |
| 26 |    0 |   16 |    0 |
| 27 |    0 |    1 |    0 |
| 28 |    0 |   23 |  172 |
| 29 |    0 |    0 |   50 |
| 30 |    0 |    0 |   40 |
+----+------+------+------+

it throws error Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'ROWS'. Msg 195, Level 15, State 10, Line 1 'LAG' is not a recognized built-in function name. — Er Ketan Vavadiya, Feb 20 '16 at 06:28
@ErKetanVavadiya Are you sure you are running on SQL Server 2012? I am running this script on SQL Server 2012 without problem. Could you check what `SELECT @@VERSION;` returns on the database you are running this script on? — TT., Feb 20 '16 at 06:46
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4042.0 (X64) Mar 26 2015 21:18:04 Copyright (c) Microsoft Corporation Express Edition with Advanced Services (64-bit) on Windows NT 6.2 (Build 9200: ) — Er Ketan Vavadiya, Feb 20 '16 at 06:51
Thanks for ur support.... Thanks... i have sql sever 2012 also .. i am trying in it — Er Ketan Vavadiya, Feb 20 '16 at 06:54

How to spill over quantities to following columns when cumulative sum crosses boundary

1 Answers1

Solution for SQL Server 2012+

Solution for SQL Server 2008