How can I populate a balance field in SQL with grouped Part Numbers-CodePudding

I have over 800,000 rows in a table. This is an example of what I have. What I am trying to do is run an update query that populates the Balance column.

Part No	Current QOH	Supply	Demand	Previous Part No	ID
ABC123	15	0	1		1
ABC123	15	5	3	ABC123	2
ABC123	15	0	5	ABC123	3
DEF321	22	0	16	ABC123	4
DEF321	22	0	3	DEF321	5
DEF321	22	30	5	DEF321	6
DEF321	22	0	1	DEF321	7
DEF321	22	14	4	DEF321	8
DEF321	22	0	9	DEF321	9
DEF321	22	0	4	DEF321	10

There are many Part Nos, They are all grouped, as shown.

For the first Part No in a group, I want to start with Current QOH, subtract the Demand and add the Supply. Then for all the remaining Part Nos in the group, I don't start with Current QOH, instead I start with the previous row Balance. Then from there I subtract the Demand and add the Supply. Once we get to the next group of Part Nos, I start with Current QOH again. I have the Previous Part No which I populated using Lag. That's helpful in determining if the Part No is the first in a group or not.

After populating Balance, it should look like this:

Part No	Current QOH	Supply	Demand	Balance	Previous Part No	ID
ABC123	15	0	1	14		1
ABC123	15	5	3	16	ABC123	2
ABC123	15	0	5	11	ABC123	3
DEF321	22	0	16	6	ABC123	4
DEF321	22	0	3	3	DEF321	5
DEF321	22	30	5	28	DEF321	6
DEF321	22	0	1	27	DEF321	7
DEF321	22	14	4	37	DEF321	8
DEF321	22	0	9	28	DEF321	9
DEF321	22	0	4	24	DEF321	10

Currently, I load the data into Excel, manually populate the first Balance field, then apply a formula, which I copy down. Then I load the data back into SQL.

I also tried a VB script that looks at each row, one at a time and makes the calculations and populates Balance. But with 800K records, that script takes hours to run.

I am looking for an SQL solution.

Thanks!

CodePudding user response：

You can use window functions to calculate your running balance like so:

select partNo, CurrentQOH, supply, Demand, 
    First_Value(CurrentQOH) over(partition by partno order by id) 
      Sum(supply-demand) over(partition by partno order by id) Balance,
    PreviousPartNo, Id
from t