Work out closing balance using opening balance of prior month

• Bhagat

  SSC-Addicted

  Points: 419

  More actions

  September 12, 2025 at 4:28 pm

  #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.

  

  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