Summary reset

Transact-SQL
1

We Have an acumulative summary but needs to be reset when riches a top limit like 8k, example: Is this posible? thanks in advance good day to you all.

2085.06 2085.06 2085.06
5078.48 7163.54 7163.54
210 7373.54 7373.54
6089.75 13463.29 6089.75 <
1200 14663.29 7289.75
1512.9 16176.19 1512.9 <
748.33 16924.52 2261.23
1200 18124.52 3461.23
2146.29 20270.81 5607.52
2140 22410.81 7747.52

Query:
SELECT a.id, a.importe, SUM(a.importe) OVER (PARTITION BY a.RFCCTE ORDER BY a.id)
FROM @? a
ORDER BY a.id;Sin título

2

Welcome. You mention cumulative above 8k but you have highlighted items in yellow that seem unrelated to 8k. Can you please expound

3

When the summary reaches the limit of 8k or above it resets to left import, the first column its the import, te second the summary, the third how we want to be resets when >= 8000 and then continue the summary from that point

4

please provide usable sample data in the following format

create table #galion(import decimal(6,2), summary decimal(6,2))

insert into #galion
select 2085.06, 2085.06 union
select 5078.48, 7163.54

and do this for all of the sample rows you provided a picture of. Help us help you.
That way we can run and test your data on our local sql server and try to provide you the correct answer. We cant do that with images and describing things with first column, second column and third etc

5

I suspect the 'Quirky Update' will be the quickest way to do this in tsql. You can google this.
The technique is non-relational and relies on tsql specific syntax.

6

img1

7

img2

8

What I'm trying is to sum an import in another column, but when it reaches >=8000 resets and continues from there, I'm trying my best to express my idea not good in english sorry for that.

9

This query is wrong becouse the next time the summatory will be always > 8000 so the group does not change, is there a way to reset sum() to the last import value?

10

This is the complete result from the query.

img3
img3787×313 3.98 KB

11

Please provide the data as text that we can copy not as images.

12

how's this?

create table #a (id int identity(1,1),
				 StartDate date,
				 RFC varchar(30),
				 Amount decimal(10,2))

insert into #a values 
('1/1/2020', 'abc', 1050),
('1/4/2020', 'abc', 2625),
('1/6/2020', 'abc', 1389.50),
('1/7/2020', 'abc', 525),
('1/8/2020', 'abc', 2625),
('1/9/2020', 'abc', 4516.24),
('1/10/2020', 'abc', 6650),
('1/11/2020', 'abc', 5550)

select v.*,
		sum(Amount) over (partition by grp order by StartDate) as CumulativeAmount
from (
Select  *, 
		sum(Amount) over (partition by RFC order by StartDate) as CumulativeAmount,
		cast(sum(Amount) over (partition by RFC order by StartDate) / 8000 as int) as grp
 from #a) v
13

Thanks to all, I've done it with Quirky Update.. Solve

DECLARE @running_sum decimal(18,2)
, @temp decimal(18,2)

UPDATE @xx
SET @temp = CumulativeAmount = case when COALESCE(@running_sum, 0) + Amount >= 8000 then Amount else COALESCE(@running_sum, 0) + Amount end
, @running_sum = CASE WHEN @temp < 8000 THEN @temp ELSE 0 END
OPTION (FORCE ORDER)

Thanks a lot to everyone, have a nice afternoon.

14

This solution es better thanks Mike :slight_smile:

15

img4

16

with quirky update
IMG5

17

You need to follow all the rules for the quirky update including MAXDOP 1.

18

Thanks Ifor, good morning.