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 (sql v17.9)
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 accumulating to 0.01 and resetting the value to zero when it is not 0.01.
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
