sql - How to "reset" running SUM after it reaches a threshold?

Question

I wrote a query that creates two columns: the_day, and the amount_raised on that day. Here is what I have:

And I would like to add a column that has a running sum of amount_raised:

Ultimately, I would like the sum column to reset after it reaches 1 million.

The recursive approach is above my pay grade, so if anyone knows a way to reset the sum without creating an entirely new table, please comment (maybe with a RESET function?). Thank you

Answer 1

I'd like to thank Juan Carlos Oropeza for providing a script and SQLFiddle with the test data. George, you should have done that.

The query itself it rather simple.

At first calculate a simple running sum (CTE_RunningSum) and divide it by 1,000,000 to get number of whole millions.

Then calculate the running sum again with partitioning by the number of millions.

SQL Fiddle

I included the columns RunningSum and Millions in the final result to illustrate how the query works.

WITH
CTE_RunningSum
AS
(
  SELECT 
    ID
    ,day_t
    ,collect
    ,SUM(collect) OVER(ORDER BY day_t, id) AS RunningSum
    ,(SUM(collect) OVER(ORDER BY day_t, id)) / 1000000 AS Millions
  FROM myTable
)
SELECT
  ID
  ,day_t
  ,collect
  ,RunningSum
  ,Millions
  ,SUM(collect) OVER(PARTITION BY Millions ORDER BY day_t, id) AS Result
FROM CTE_RunningSum
ORDER BY day_t, id;

Result

|  id |                       day_t | collect | runningsum | millions |  result |
|-----|-----------------------------|---------|------------|----------|---------|
|  90 |     March, 11 2015 00:00:00 |   69880 |      69880 |        0 |   69880 |
|  13 |     March, 25 2015 00:00:00 |   69484 |     139364 |        0 |  139364 |
|  49 |     March, 27 2015 00:00:00 |   57412 |     196776 |        0 |  196776 |
|  41 |     March, 30 2015 00:00:00 |   56404 |     253180 |        0 |  253180 |
|  99 |     April, 03 2015 00:00:00 |   59426 |     312606 |        0 |  312606 |
|   1 |     April, 10 2015 00:00:00 |   65825 |     378431 |        0 |  378431 |
| 100 |     April, 27 2015 00:00:00 |   60884 |     439315 |        0 |  439315 |
|  50 |       May, 11 2015 00:00:00 |   39641 |     478956 |        0 |  478956 |
|  58 |       May, 11 2015 00:00:00 |   49759 |     528715 |        0 |  528715 |
|  51 |       May, 17 2015 00:00:00 |   32895 |     561610 |        0 |  561610 |
|  15 |       May, 19 2015 00:00:00 |   50847 |     612457 |        0 |  612457 |
|  66 |       May, 29 2015 00:00:00 |   66332 |     678789 |        0 |  678789 |
|   4 |      June, 04 2015 00:00:00 |   46891 |     725680 |        0 |  725680 |
|  38 |      June, 09 2015 00:00:00 |   64732 |     790412 |        0 |  790412 |
|  79 |      June, 14 2015 00:00:00 |   62843 |     853255 |        0 |  853255 |
|  37 |      June, 28 2015 00:00:00 |   54315 |     907570 |        0 |  907570 |
|  59 |      June, 30 2015 00:00:00 |   34885 |     942455 |        0 |  942455 |
|  71 |      July, 08 2015 00:00:00 |   46440 |     988895 |        0 |  988895 |
|  31 |      July, 10 2015 00:00:00 |   39649 |    1028544 |        1 |   39649 |
|  91 |      July, 12 2015 00:00:00 |   65048 |    1093592 |        1 |  104697 |
|  57 |      July, 14 2015 00:00:00 |   60394 |    1153986 |        1 |  165091 |
|  98 |      July, 20 2015 00:00:00 |   34481 |    1188467 |        1 |  199572 |
|   3 |      July, 26 2015 00:00:00 |   58672 |    1247139 |        1 |  258244 |
|  95 |    August, 19 2015 00:00:00 |   52393 |    1299532 |        1 |  310637 |
|  74 |    August, 20 2015 00:00:00 |   37972 |    1337504 |        1 |  348609 |
|  20 |    August, 27 2015 00:00:00 |   36882 |    1374386 |        1 |  385491 |
|   2 | September, 07 2015 00:00:00 |   39408 |    1413794 |        1 |  424899 |
|  14 | September, 09 2015 00:00:00 |   40234 |    1454028 |        1 |  465133 |
|   6 | September, 17 2015 00:00:00 |   65957 |    1519985 |        1 |  531090 |
|  93 | September, 29 2015 00:00:00 |   47213 |    1567198 |        1 |  578303 |
|  35 | September, 30 2015 00:00:00 |   49446 |    1616644 |        1 |  627749 |
|  86 |   October, 11 2015 00:00:00 |   34291 |    1650935 |        1 |  662040 |
|  75 |   October, 12 2015 00:00:00 |   31448 |    1682383 |        1 |  693488 |
|  19 |   October, 14 2015 00:00:00 |   48509 |    1730892 |        1 |  741997 |
|  56 |   October, 26 2015 00:00:00 |   30072 |    1760964 |        1 |  772069 |
|  48 |   October, 28 2015 00:00:00 |   58527 |    1819491 |        1 |  830596 |
|  40 |  November, 05 2015 00:00:00 |   67293 |    1886784 |        1 |  897889 |
|  33 |  November, 09 2015 00:00:00 |   41944 |    1928728 |        1 |  939833 |
|  34 |  November, 11 2015 00:00:00 |   35516 |    1964244 |        1 |  975349 |
|  85 |  November, 20 2015 00:00:00 |   43920 |    2008164 |        2 |   43920 |
|  18 |  November, 23 2015 00:00:00 |   44925 |    2053089 |        2 |   88845 |
|  62 |  December, 24 2015 00:00:00 |   34678 |    2087767 |        2 |  123523 |
|  67 |  December, 25 2015 00:00:00 |   35323 |    2123090 |        2 |  158846 |
|  81 |  December, 28 2015 00:00:00 |   37071 |    2160161 |        2 |  195917 |
|  54 |   January, 02 2016 00:00:00 |   32330 |    2192491 |        2 |  228247 |
|  70 |   January, 06 2016 00:00:00 |   47875 |    2240366 |        2 |  276122 |
|  28 |   January, 23 2016 00:00:00 |   40250 |    2280616 |        2 |  316372 |
|  65 |   January, 25 2016 00:00:00 |   49404 |    2330020 |        2 |  365776 |
|  73 |   January, 26 2016 00:00:00 |   65879 |    2395899 |        2 |  431655 |
|   5 |  February, 05 2016 00:00:00 |   53953 |    2449852 |        2 |  485608 |
|  32 |  February, 11 2016 00:00:00 |   44988 |    2494840 |        2 |  530596 |
|  53 |  February, 25 2016 00:00:00 |   68948 |    2563788 |        2 |  599544 |
|  83 |     March, 11 2016 00:00:00 |   47244 |    2611032 |        2 |  646788 |
|   8 |     March, 25 2016 00:00:00 |   51809 |    2662841 |        2 |  698597 |
|  82 |     March, 25 2016 00:00:00 |   66506 |    2729347 |        2 |  765103 |
|  88 |     April, 06 2016 00:00:00 |   69288 |    2798635 |        2 |  834391 |
|  89 |     April, 14 2016 00:00:00 |   43162 |    2841797 |        2 |  877553 |
|  52 |     April, 23 2016 00:00:00 |   47772 |    2889569 |        2 |  925325 |
|   7 |     April, 27 2016 00:00:00 |   33368 |    2922937 |        2 |  958693 |
|  84 |     April, 27 2016 00:00:00 |   57644 |    2980581 |        2 | 1016337 |
|  17 |       May, 17 2016 00:00:00 |   35416 |    3015997 |        3 |   35416 |
|  61 |       May, 17 2016 00:00:00 |   64603 |    3080600 |        3 |  100019 |
|  87 |      June, 07 2016 00:00:00 |   41865 |    3122465 |        3 |  141884 |
|  97 |      June, 08 2016 00:00:00 |   64982 |    3187447 |        3 |  206866 |
|  92 |      June, 15 2016 00:00:00 |   58684 |    3246131 |        3 |  265550 |
|  23 |      June, 26 2016 00:00:00 |   46147 |    3292278 |        3 |  311697 |
|  46 |      June, 30 2016 00:00:00 |   61921 |    3354199 |        3 |  373618 |
|  94 |      July, 03 2016 00:00:00 |   55535 |    3409734 |        3 |  429153 |
|  60 |      July, 07 2016 00:00:00 |   63607 |    3473341 |        3 |  492760 |
|  45 |      July, 20 2016 00:00:00 |   51965 |    3525306 |        3 |  544725 |
|  96 |      July, 20 2016 00:00:00 |   46684 |    3571990 |        3 |  591409 |
|  29 |    August, 09 2016 00:00:00 |   37707 |    3609697 |        3 |  629116 |
|  69 |    August, 11 2016 00:00:00 |   37194 |    3646891 |        3 |  666310 |
|  80 |    August, 19 2016 00:00:00 |   62673 |    3709564 |        3 |  728983 |
|  36 |    August, 28 2016 00:00:00 |   48237 |    3757801 |        3 |  777220 |
|  39 |    August, 29 2016 00:00:00 |   48159 |    3805960 |        3 |  825379 |
|  25 |    August, 30 2016 00:00:00 |   60958 |    3866918 |        3 |  886337 |
|  68 | September, 04 2016 00:00:00 |   50167 |    3917085 |        3 |  936504 |
|  55 | September, 08 2016 00:00:00 |   31193 |    3948278 |        3 |  967697 |
|  64 | September, 10 2016 00:00:00 |   31157 |    3979435 |        3 |  998854 |
|  42 | September, 14 2016 00:00:00 |   52878 |    4032313 |        4 |   52878 |
|  43 | September, 15 2016 00:00:00 |   54728 |    4087041 |        4 |  107606 |
|  77 | September, 18 2016 00:00:00 |   65320 |    4152361 |        4 |  172926 |
|  12 | September, 23 2016 00:00:00 |   43597 |    4195958 |        4 |  216523 |
|  30 | September, 26 2016 00:00:00 |   32764 |    4228722 |        4 |  249287 |
|  10 | September, 27 2016 00:00:00 |   47038 |    4275760 |        4 |  296325 |
|  47 |   October, 08 2016 00:00:00 |   46280 |    4322040 |        4 |  342605 |
|  26 |   October, 10 2016 00:00:00 |   69487 |    4391527 |        4 |  412092 |
|  63 |   October, 30 2016 00:00:00 |   49561 |    4441088 |        4 |  461653 |
|  78 |  November, 15 2016 00:00:00 |   40138 |    4481226 |        4 |  501791 |
|  27 |  November, 27 2016 00:00:00 |   57378 |    4538604 |        4 |  559169 |
|  21 |  December, 01 2016 00:00:00 |   35336 |    4573940 |        4 |  594505 |
|  16 |  December, 03 2016 00:00:00 |   39671 |    4613611 |        4 |  634176 |
|  22 |  December, 13 2016 00:00:00 |   34574 |    4648185 |        4 |  668750 |
|  72 |   January, 29 2017 00:00:00 |   55084 |    4703269 |        4 |  723834 |
|  44 |   January, 30 2017 00:00:00 |   36742 |    4740011 |        4 |  760576 |
|  24 |  February, 01 2017 00:00:00 |   31061 |    4771072 |        4 |  791637 |
|  76 |  February, 12 2017 00:00:00 |   35059 |    4806131 |        4 |  826696 |
|   9 |  February, 27 2017 00:00:00 |   39767 |    4845898 |        4 |  866463 |
|  11 |  February, 28 2017 00:00:00 |   66007 |    4911905 |        4 |  932470 |