Running total with reset condition

Question

Running total with reset condition

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

Talvin Singh - Wednesday, September 13, 2017 2:32 AM
This works very well!
i was able to test it on a large dataset, works blindingly fast!
really appreciate this

Thank you for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 2

Please mark Jeff's answer as correct, if you wouldn't mind.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

Steve Jones - SSC Editor - Wednesday, September 13, 2017 8:37 AM
Please mark Jeff's answer as correct, if you wouldn't mind.

Thank you both.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

george_at_sql SSCommitted Points: 1967 More actions · Answer 4

with data
as (SELECT *
  FROM (VALUES (101,10,10)
      ,(102,20,30)
      ,(103,30,30)
      ,(104,12,12)
      ,(105,18,30)
      ,(106,10,10)
      ,(107,10,20)) v(cat,value,running_total)
   )
,cte(cat1,value1,running_total1,grp1,grpid1)
as (select cat,value,running_total
     ,value as grp
     ,cast(1 as int) as grpid
   from data
   where cat=101
   UNION ALL
   select a.cat,a.value,a.running_total
     ,case when a.value+b.grp1<=30 then
         a.value+b.grp1
       else a.value
      end as grp
     ,case when a.value+b.grp1<=30 then
         b.grpid1
       else b.grpid1+1
      end as grpid
    from data a
    join cte b
     on a.cat=b.cat1+1
   )
select *
from cte

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

Steve Jones - SSC Editor wrote:

Please mark Jeff's answer as correct, if you wouldn't mind.

BWAAA-HAAA! Well that didn't work. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)