• 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?
Running-Total-with-Logic-Example.xlsx
0
Ryan Simmons
Asked:
Ryan Simmons
  • 2
1 Solution
 
Ryan SimmonsBusiness Analyst IIIAuthor Commented:
Found the answer to my question on stackoverflow:
https://stackoverflow.com/questions/16528906/t-sql-if-statement-embedded-in-a-sum-function

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

https://docs.microsoft.com/en-us/sql/t-sql/functions/sum-transact-sql

Look at the OVER clause in particular.
0
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