none
Reset sum() OVER PARTITION BY for condition RRS feed

  • Pregunta

  • Hello, I would like to help myself, I have a table with 4 columns, (Id, Grp, Amount, SumMonto).
    The Amount column is money type and handles 4 decimal places. so values ​​of 0.0001 are stored.
    I want to add the Cumulative Amount column and every time its value is equal to or greater than 0.01 (rounded value) it is recorded in the row that
    corresponds to the accumulated sum and for those that are less than 0.01, zero must be registered

    This is my example code

    if object_id('tempdb..#example') is not null   
        drop table #example
     
    CREATE TABLE #example (
    Id INTEGER identity(1,1),
    Grp  INTEGER NULL,
    DateT VARCHar(8),
    Amount money NULL
    );
     
    INSERT INTO #example VALUES (1,'20210101',0.0037);
    INSERT INTO #example VALUES (1,'20210102',0.0037);
    INSERT INTO #example VALUES (1,'20210103',0.0037);
    INSERT INTO #example VALUES (1,'20210104',0.0037);
    INSERT INTO #example VALUES (1,'20210105',0.0037);
    INSERT INTO #example VALUES (1,'20210106',0.0037);
    INSERT INTO #example VALUES (1,'20210107',0.0037);
    INSERT INTO #example VALUES (1,'20210108',0.0012);
    INSERT INTO #example VALUES (1,'20210109',0.0012);
    INSERT INTO #example VALUES (2,'20210101',0.0012);
    INSERT INTO #example VALUES (2,'20210102',0.0012);
    INSERT INTO #example VALUES (2,'20210103',0.0012);
    INSERT INTO #example VALUES (2,'20210104',0.0012);
    INSERT INTO #example VALUES (2,'20210105',0.0012);
    INSERT INTO #example VALUES (2,'20210106',0.0100);
    INSERT INTO #example VALUES (2,'20210107',0.0018);
    INSERT INTO #example VALUES (2,'20210108',0.0018);
    INSERT INTO #example VALUES (2,'20210109',0.0548);
    

    I have made the accumulation with sum (Amount) but it only accumulates the value and I don't know how to reset the accumulation when it is greater than or equal to 0.01 and that
    it registers the zero in the row that does not accumulate the penny

    this my code and the output (I have used case instructions but I couldn't so I copy the base instruction)

    select  Id,Grp,DateT,Amount ,
                  sum(Amount) OVER
                          (
                                 PARTITION BY Grp
                                 ORDER BY DateT --ROWS UNBOUNDED PRECEDING
                          ) Accumulated
           from #example
    	   
    	   
    Id          Grp         DateT                 Amount                Accumulated
    ----------- ----------- --------------------- --------------------- ---------------------
    1           1           20210101           0.0037                0.0037
    2           1           20210102           0.0037                0.0074
    3           1           20210103           0.0037                0.0111
    4           1           20210104           0.0037                0.0148
    5           1           20210105           0.0037                0.0185
    6           1           20210106           0.0037                0.0222
    7           1           20210107           0.0037                0.0259
    8           1           20210108           0.0012                0.0126
    9           1           20210109           0.0012                0.0138
    10          2           20210101           0.0012                0.0012
    11          2           20210102           0.0012                0.0024
    12          2           20210103           0.0012                0.0036
    13          2           20210104           0.0012                0.0048
    14          2           20210105           0.0012                0.0060
    15          2           20210106           0.0100                0.0160
    16          2           20210107           0.0018                0.0178
    17          2           20210108           0.0018                0.0196
    18          2           20210109           0.0548                0.0744

    In the following table I indicate how the values ​​should come out when the sum is greater than or equal to 0.01, otherwise there should be zero

    Id          Grp         DateT                 Amount                Accumulated
    ----------- ----------- --------------------- --------------------- ---------------------
    1           1           20210101           0.0037                	0.0000
    2           1           20210102           0.0037                	0.0000
    3           1           20210103           0.0037                	0.0100
    4           1           20210104           0.0037                	0.0000
    5           1           20210105           0.0037                	0.0000
    6           1           20210106           0.0037                	0.0100
    7           1           20210107           0.0037                	0.0000
    8           1           20210108           0.0012                	0.0000
    9           1           20210109           0.0012                	0.0000
    10          2           20210101           0.0012                	0.0000
    11          2           20210102           0.0012                	0.0000
    12          2           20210103           0.0012                	0.0000
    13          2           20210104           0.0012                	0.0000
    14          2           20210105           0.0012                	0.0100
    15          2           20210106           0.0100                	0.0100
    16          2           20210107           0.0018                	0.0000
    17          2           20210108           0.0018                	0.0000
    18          2           20210109           0.0548                	0.0600

    This routine would apply to an average of 7 million records or more for the purpose of finding an optimal option.
    thank you very much any recommendation


    In the following table I indicate how the values ​​should come out when the sum is greater than or equal to 0.01, otherwise there should be zero
    In the following table I indicate how the values ​​should come out when the sum is greater than or equal to 0.01, otherwise there should be zero
    In the following table I indicate how the values ​​should come out when the sum is greater than or equal to 0.01, otherwise there should be zero
    lunes, 18 de enero de 2021 17:59

Todas las respuestas