Post reply

Work out closing balance using opening balance of prior month

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

    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

  • September 12, 2025 at 8:50 pm

    #4650792

    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)

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

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