calculate a running sum, with value reset when a contion is hit

LeonPlusFruit 1 Reputation point
2022-06-23T19:26:56.127+00:00

Hello,

I'm trying to add a column called csum which would calculate a running sum of days with type: 'm', but ignore days if the type is 'v',
the calculation will end until when a row with the type 'o' is hit. Then the running sum will be reset and start over again.

id days type csum
1 30 v 0
2 25 m 25
3 30 v 25
4 30 m 55
5 25 m 80
6 30 o 110
7 30 v 0
8 5 m 5
9 10 o 15
10 20 v 0
10 10 m 10
11 15 v 10
12 10 o 20
13 15 m 15
14 10 m 25
15 15 v 25
16 10 o 35

Can some one help or give me hint please?

Thanks,

Leon

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,237 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Alexei Stoyanovsky 3,411 Reputation points
    2022-06-24T06:07:15.967+00:00

    To calculate this, you need to find the id 1 greater than that of the previous 'o' row, or just 1 if no 'o's happened yet. Next it is a straightforward conditional sum for non-'v's between that id and the current row.

    0 comments No comments