Reset the total to zero when ItemCount is >= 6

Transact-SQL
1

Here's my SQL query: I want to have a sum of the item count reset to 0 when the sum >= 6.
Please suggest what i can use to get the result.

Select 1 as ID,4 ItemCount UNION ALL
Select 2 as ID,3 ItemCount UNION ALL
Select 3 as ID,5 ItemCount UNION ALL
Select 4 as ID,2 ItemCount UNION ALL
Select 5 as ID,6 ItemCount UNION ALL
Select 6 as ID,3 ItemCount UNION ALL
Select 7 as ID,7 ItemCount UNION ALL
Select 8 as ID,1 ItemCount UNION ALL
Select 9 as ID,3 ItemCount UNION ALL
Select 10 as ID,3 ItemCount

Expected output is:

Select 1 as ID,Select 4 ItemCount, 0 as SumTotal UNION ALL
Select 2 as ID,Select 3 ItemCount, 7 as SumTotal UNION ALL
Select 3 as ID,Select 5 ItemCount, 0 as SumTotal UNION ALL
Select 4 as ID,Select 2 ItemCount, 7 as SumTotal UNION ALL
Select 5 as ID,Select 6 ItemCount, 6 as SumTotal UNION ALL
Select 6 as ID,Select 3 ItemCount, 0 as SumTotal UNION ALL
Select 7 as ID,Select 7 ItemCount, 10 as SumTotal UNION ALL
Select 8 as ID,Select 1 ItemCount, 0 as SumTotal UNION ALL
Select 9 as ID,Select 3 ItemCount, 0 as SumTotal UNION ALL
Select10 as ID,Select 3 ItemCount, 7 as SumTotal
4

this is a bit confusing to me " I want to have a sum of the item count reset to 0 when the sum >= 6" but then the expected output is totally different than the requirement?

6

It's like - if the sum is < 6 , you show 0 and add that number to a temp result and you move on to the next row.
If the sum exceeds 6, then show whatever is the sum and reset the sum to 0.

Select 1 as ID,Select 4 ItemCount, 0 as SumTotal UNION ALL
--> Sum =0, Temp = 4
Select 2 as ID,Select 3 ItemCount, 7 as SumTotal UNION ALL
--> Sum exceeds 6, Calculate 4+3 = 7 and show it, reset sum to 0
Select 3 as ID,Select 5 ItemCount, 0 as SumTotal UNION ALL
--> Sum =0, Temp = 5
Select 4 as ID,Select 2 ItemCount, 7 as SumTotal UNION ALL
--> Sum exceeds 6, Calculate 5 +2 = 7 and show it , reset sum to 0

Please let me know if this resolves your doubt.

7

:face_with_raised_eyebrow::flushed:
doubt x 100 = ??

more confused now . explanations after each -> are not consistent. Sum, temp etc.

"then show whatever is the sum and reset the sum to 0." what?!?

9

hi

GOT IT .. hope this helps :slight_smile: :slight_smile:

drop create data ... 
drop table #data 
go 

drop table #output 
go 


create table #data
(
id int ,
itemcount int 
)
go 

create table #output 
(
id int ,
itemcount int ,
sumtotal int
)
go 

insert into #data
Select 1 as ID,4 ItemCount UNION ALL
Select 2 as ID,3 ItemCount UNION ALL
Select 3 as ID,5 ItemCount UNION ALL
Select 4 as ID,2 ItemCount UNION ALL
Select 5 as ID,6 ItemCount UNION ALL
Select 6 as ID,3 ItemCount UNION ALL
Select 7 as ID,7 ItemCount UNION ALL
Select 8 as ID,1 ItemCount UNION ALL
Select 9 as ID,3 ItemCount UNION ALL
Select 10 as ID,3 ItemCount
go 

insert into #output 
Select 1 as ID, 4 ItemCount, 0 as SumTotal UNION ALL
Select 2 as ID, 3 ItemCount, 7 as SumTotal UNION ALL
Select 3 as ID, 5 ItemCount, 0 as SumTotal UNION ALL
Select 4 as ID, 2 ItemCount, 7 as SumTotal UNION ALL
Select 5 as ID, 6 ItemCount, 6 as SumTotal UNION ALL
Select 6 as ID, 3 ItemCount, 0 as SumTotal UNION ALL
Select 7 as ID, 7 ItemCount, 10 as SumTotal UNION ALL
Select 8 as ID, 1 ItemCount, 0 as SumTotal UNION ALL
Select 9 as ID, 3 ItemCount, 0 as SumTotal UNION ALL
Select 10 as ID, 3 ItemCount, 7 as SumTotal
go 


select 'data',* from #data
go 

select 'expected output',* from #output
go
SQL ...working 
; WITH cte 
     AS (SELECT *, 
                itemcount AS sumtotal 
         FROM   #data 
         WHERE  id = 1 
         UNION ALL 
         SELECT b.*, 
                b.itemcount + ( CASE 
                                  WHEN a.sumtotal >= 6 THEN 0 
                                  ELSE a.sumtotal 
                                END ) 
         FROM   cte a 
                JOIN #data b 
                  ON a.id + 1 = b.id) 
SELECT 'my output', 
       id, 
       itemcount, 
       CASE 
         WHEN sumtotal < 6 THEN 0 
         ELSE sumtotal 
       END 
FROM   cte 

go

image
image.png371×789 8.56 KB

1 Like