Work out closing balance using opening balance of prior month

Question

Post reply

Work out closing balance using opening balance of prior month

Bhagat

SSC-Addicted

Points: 440

September 12, 2025 at 4:28 pm

Go to Answer

#4650676

Hello, I think I need a recursive cte query but unsure of the logic.

I have data for 3 Divisions for 12 months.

For Jan, Closing (H) value is the sum of Open + Created + Won + Lost

For all other months, it then also needs to include prior months closing, so the formula would be:

ClosingPriorMonth + Open + Created + Won + Lost

ClosingPriorMonth is included in this screenshot for illustration and isn't part of the sql script (at the end), but that is what I'm looking for help with.

Hope that makes sense.

Thank you.

Screenshot 2025-09-12 170342

DDL

CREATE TABLE [dbo].[Balance](
[Date] [date] NULL,
[Division] [nvarchar](100) NULL,
[OpenOpps] [int] NULL,
[CreatedOpps] [int] NULL,
[WonOpps] [int] NULL,
[LostOpps] [int] NULL,
[RID] [int] NULL
) ON [PRIMARY]
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2024-01-01' AS Date), N'Cloud & Security', 0, 0, 0, 0, 1)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2024-02-01' AS Date), N'Cloud & Security', 0, 0, 0, 0, 2)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2024-03-01' AS Date), N'Cloud & Security', 0, 0, 0, 0, 3)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2024-04-01' AS Date), N'Cloud & Security', 0, 0, 0, 0, 4)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2024-05-01' AS Date), N'Cloud & Security', 0, 0, 0, 0, 5)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2024-06-01' AS Date), N'Cloud & Security', 0, 0, 0, 0, 6)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2024-07-01' AS Date), N'Cloud & Security', 0, 0, 0, 0, 7)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2024-08-01' AS Date), N'Cloud & Security', 0, 0, 0, 0, 8)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2024-09-01' AS Date), N'Cloud & Security', 0, 0, -1, 0, 9)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2024-10-01' AS Date), N'Cloud & Security', 0, 0, 0, 0, 10)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2024-11-01' AS Date), N'Cloud & Security', 0, 0, -1, 0, 11)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2024-12-01' AS Date), N'Cloud & Security', 3, 8, -9, -6, 12)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2025-01-01' AS Date), N'Cloud & Security', 4, 11, -26, -14, 13)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2025-02-01' AS Date), N'Cloud & Security', 4, 33, -35, -13, 14)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2025-03-01' AS Date), N'Cloud & Security', 6, 26, -61, -28, 15)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2025-04-01' AS Date), N'Cloud & Security', 6, 8, -23, -40, 16)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2025-05-01' AS Date), N'Cloud & Security', 6, 21, -47, -30, 17)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2025-06-01' AS Date), N'Cloud & Security', 8, 34, -58, -24, 18)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2025-07-01' AS Date), N'Cloud & Security', 11, 16, -46, -68, 19)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2025-08-01' AS Date), N'Cloud & Security', 26, 15, -38, -30, 20)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2025-09-01' AS Date), N'Cloud & Security', 106, 23, -22, -34, 21)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2025-10-01' AS Date), N'Cloud & Security', 127, 0, 0, -15, 22)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2025-11-01' AS Date), N'Cloud & Security', 140, 0, -1, -6, 23)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2025-12-01' AS Date), N'Cloud & Security', 144, 0, 0, -1, 24)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2024-01-01' AS Date), N'Communications', 0, 0, 0, -4, 1)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2024-02-01' AS Date), N'Communications', 0, 2, -1, -8, 2)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2024-03-01' AS Date), N'Communications', 1, 1, 0, -10, 3)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2024-04-01' AS Date), N'Communications', 1, 0, 0, 0, 4)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2024-05-01' AS Date), N'Communications', 2, 1, 0, -4, 5)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2024-06-01' AS Date), N'Communications', 2, 0, 0, -4, 6)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2024-07-01' AS Date), N'Communications', 2, 0, 0, 0, 7)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2024-08-01' AS Date), N'Communications', 2, 0, 0, -1, 8)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2024-09-01' AS Date), N'Communications', 2, 0, 0, -1, 9)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2024-10-01' AS Date), N'Communications', 2, 1, 0, -2, 10)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2024-11-01' AS Date), N'Communications', 2, 1, 0, -3, 11)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2024-12-01' AS Date), N'Communications', 13, 36, -27, -26, 12)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2025-01-01' AS Date), N'Communications', 13, 29, -43, -27, 13)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2025-02-01' AS Date), N'Communications', 13, 52, -98, -17, 14)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2025-03-01' AS Date), N'Communications', 17, 46, -89, -47, 15)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2025-04-01' AS Date), N'Communications', 18, 33, -125, -35, 16)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2025-05-01' AS Date), N'Communications', 19, 45, -124, -45, 17)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2025-06-01' AS Date), N'Communications', 20, 30, -118, -86, 18)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2025-07-01' AS Date), N'Communications', 25, 45, -105, -81, 19)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2025-08-01' AS Date), N'Communications', 45, 35, -75, -87, 20)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2025-09-01' AS Date), N'Communications', 195, 51, -80, -61, 21)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2025-10-01' AS Date), N'Communications', 268, 0, -5, -12, 22)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2025-11-01' AS Date), N'Communications', 307, 0, -1, -3, 23)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2025-12-01' AS Date), N'Communications', 315, 0, 0, -3, 24)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2024-01-01' AS Date), N'Managed Services', 0, 0, 0, 0, 1)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2024-02-01' AS Date), N'Managed Services', 0, 0, 0, -1, 2)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2024-03-01' AS Date), N'Managed Services', 0, 0, 0, 0, 3)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2024-04-01' AS Date), N'Managed Services', 0, 0, 0, 0, 4)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2024-05-01' AS Date), N'Managed Services', 0, 0, 0, 0, 5)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2024-06-01' AS Date), N'Managed Services', 0, 0, 0, -1, 6)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2024-07-01' AS Date), N'Managed Services', 0, 0, 0, -1, 7)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2024-08-01' AS Date), N'Managed Services', 0, 0, 0, 0, 8)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2024-09-01' AS Date), N'Managed Services', 0, 0, 0, 0, 9)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2024-10-01' AS Date), N'Managed Services', 0, 0, 0, 0, 10)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2024-11-01' AS Date), N'Managed Services', 0, 0, 0, 0, 11)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2024-12-01' AS Date), N'Managed Services', 10, 25, -41, -11, 12)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2025-01-01' AS Date), N'Managed Services', 11, 25, -50, -38, 13)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2025-02-01' AS Date), N'Managed Services', 12, 61, -77, -19, 14)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2025-03-01' AS Date), N'Managed Services', 20, 55, -123, -28, 15)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2025-04-01' AS Date), N'Managed Services', 22, 19, -71, -27, 16)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2025-05-01' AS Date), N'Managed Services', 25, 41, -120, -40, 17)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2025-06-01' AS Date), N'Managed Services', 26, 55, -101, -31, 18)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2025-07-01' AS Date), N'Managed Services', 41, 57, -102, -40, 19)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2025-08-01' AS Date), N'Managed Services', 42, 47, -93, -29, 20)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2025-09-01' AS Date), N'Managed Services', 202, 33, -25, -20, 21)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2025-10-01' AS Date), N'Managed Services', 241, 0, -11, -9, 22)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2025-11-01' AS Date), N'Managed Services', 260, 0, -1, -2, 23)
GO
INSERT dbo.[Balance] ([Date], [Division], [OpenOpps], [CreatedOpps], [WonOpps], [LostOpps], [RID]) VALUES (CAST(N'2025-12-01' AS Date), N'Managed Services', 336, 0, 0, -4, 24)
GO

Steve Collins

SSCrazy

Points: 2231
More actions
September 12, 2025 at 10:49 pm
Answer
#4650877
Yes I agree pietlinden has the correct approach imo. SUM OVER provides a running total without recursion
```
select *, sum(OpenOpps+CreatedOpps+WonOpps+LostOpps) over (partition by Division order by [Date]) running_total
from #balance
where [Date] >= '20250101'
order by Division, [Date];
```
Btw, I disagree with those who suggest to always specify the windowing range. The default is [edit: corrected] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Why repeat it? The best part is no part
- This reply was modified 2 weeks, 6 days ago by Steve Collins.
- This reply was modified 2 weeks, 6 days ago by Steve Collins.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63616 More actions · Answer 1

In other words, a running total of Open + Created + Won + Lost

You could calculate them individually and then add... or...

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW?? (using a windowing function)

Jeff Moden SSC Guru Points: 1004648 More actions · Answer 2

Steve Collins wrote:

Btw, I disagree with those who suggest to always specify the windowing range. The default is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Why repeat it? The best part is no part

The way you have that printed above is not correct. The default for windowing functions is RANGE and you can get performance increase by specifying ROWS, in most cases.

For proof, go to the following link...

https://learn.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql#remarks

...and in the first paragraph after the gray box, see the 3rd sentence, which says...

If ROWS or RANGE isn't specified but ORDER BY is specified, RANGE UNBOUNDED PRECEDING AND CURRENT ROW is used as default for window frame.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Collins SSCrazy Points: 2231 More actions · Answer 3

Ah yes, I see that now. Thanks Jeff. In my head I did check to make sure the dates within the partition were unique and then told myself "nothing to worry about, leave off the rows/range and you'll get the right answer." Which is true yet my recollection of the default was incorrect and it's RANGE not ROWS. Maybe there's a tiny performance advantage to specifying ROWS (if appropriate, which is usually the case) and that's why people always add it. In terms of output ROWS vs RANGE matters when there are duplicates in the ORDER BY values within the window partition

This reply was modified 2 weeks, 6 days ago by Steve Collins.

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Bhagat SSC-Addicted Points: 440 More actions · Answer 4

Thank you,

I think I have it working now:

ClosingOpps = SUM(OpenOpps + CreatedOpps + WonOpps + LostOpps) OVER (PARTITION BY Division ORDER BY [Date] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

NewOpening = SUM(OpenOpps + CreatedOpps + WonOpps + LostOpps) OVER (PARTITION BY Division ORDER BY [Date] ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)