• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 9
  • Last Modified:

TSQL: How to perform a running total that also checks for criteria and restarts? Example inside.

is there a way in TSQL to have a running total that also checks for criteria?

For example I know that using a windowed function I can create the running total that partitions against storenumber and weekstartdate as shown in column D.

I also want the function to check the criteria in column E and when it hits the criteria I want the running total to stop. I know I can do that with a CASE statement.

However I want the running total to restart when it hits the next week as shown in the example.

Any ideas on how to accomplish this?
Ryan Simmons
Ryan Simmons
  • 2
1 Solution
Ryan SimmonsBusiness Analyst IIIAuthor Commented:
Found the answer to my question on stackoverflow:

I can use a SUM with a IIF function
Ryan SimmonsBusiness Analyst IIIAuthor Commented:
I found the solution on another website.
Nitin SontakkeDeveloperCommented:
You may wish to have a look at the following:


Look at the OVER clause in particular.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now