SQL calculating running total as you go down the rows but also taking other fields into account

Question

I'm hoping you guys can help with this problem.

I have a set of data which I have displayed via excel.

I'm trying to work out the rolling new cap allowance but need to deduct from previous weeks bookings. I don't want to use a cursor so can anyone help.

I'm going to group by the product id so it will need to start afresh for every product.

In the image, Columns A to D are fixed and I am trying to calculate the data in column E ('New Cap'). The 'New Cap' is the expected results.

Column F gives a detailed formula of what im trying to do.

Not sure what I've done for the post to be marked down.

Thanks

Update: The formula looks like this.

Does this answer your question? [Calculate a Running Total in SQL Server](https://stackoverflow.com/questions/860966/calculate-a-running-total-in-sql-server) — Stu, Apr 30 '21 at 16:47
Hi, no it doesnt as I need to take into account previous weeks booking numbers to calculate a new cap total. — Um7an, Apr 30 '21 at 16:51
Please edit your question and include sample data as text and expected results, see [Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example) [not as images](https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-errors-when-asking-a-question) — Stu, Apr 30 '21 at 16:57
in the image, Columns A to D are fixed and I am trying to calculate column E the 'New Cap'. The 'New Cap' is the expected results. — Um7an, Apr 30 '21 at 17:11

score 0 · Answer 1 · answered Apr 30 '21 at 18:35

0

You want the sum of the cap through this row minus the sum of booked through the previous row. This is easy to do with window functions:

select t.*,
       (sum(cap + booked) over (partition by productid order by weekbeg) - booked
       ) as new_cap
from t;

answered Apr 30 '21 at 18:35

Gordon Linoff

1,242,037
58
646
786

Hi Gordan, thanks for the quick reply. Apologies as I have not been able to explain the problem well. I have added another image in the above to show what the formula looks like for the 'New Cap' on every row. Thanks – Um7an May 01 '21 at 10:49
@Um7an . . . Are you saying this doesn't work? I'm pretty sure this is the logic you want. – Gordon Linoff May 01 '21 at 14:03

score 0 · Answer 2 · answered May 01 '21 at 11:16

0

You can get the new running total using lag and sum over window functions - calculate the cap-booked first, then use sum over() for the running total:

    select weekbeg, ProductId, Cap, Booked, 
      Sum(n) over(partition by productid order by weekbeg) New_Cap
    from (
      select *, cap - Lag(booked,1,0) over(partition by productid order by weekbeg)n
      from t
    )t

answered May 01 '21 at 11:16

Stu

30,392
6
14
33

This is brilliant, exactly what I needed. thank you. Is it possible to put a case statement around it so that if the New_Cap is < 0 then it uses the CAP for the current week as the running total needs to start over again. – Um7an May 02 '21 at 09:51
Is it possibe so that If the New_Cap is < 0 then it only uses the CAP for the current week. select case when Sum(n) over(partition by Productid order by weekbeg) <0 then t.Cap else Sum(n) over(partition by Productid order by weekbeg) end New_Cap The only problem with the above I have is that the running total doesn't start again all I get its the Cap for the following weeks. – Um7an May 02 '21 at 10:11
That wasn't particularly clear from your question, looking again at your example spreadsheet, it just resets to 9 and remains 9, that made no sense. I'll give it some more thought it will probably require another nest level. – Stu May 02 '21 at 10:41
1

thanks @Stu, apologies, first post so still learning how to word it properly. – Um7an May 02 '21 at 11:15

SQL calculating running total as you go down the rows but also taking other fields into account

2 Answers2