How do I create a balance sheet with SQL Server

Question

I have some data in my table where there is some data left and payment. When I use my query below, I get the information of the balance between the searching dates.
But the previous account balance is not being added to the account in the middle of this searching date
I want - first a new line will be created where the previous balance will be written (minus the sum of the money paid from the sum of the remaining money on the date prior to the search date).
The current account will be displayed by adjusting.
I would have benefited if an expert had wasted his precious time and collaborated on the solution. Thanks in advance

select
a.Date,
a.Retailer_Name as name,
a.Retailer_Address as addres,
a.Contact_No as Contact_no,
a.Due_Amount as Due,
a.Payment_amount As Payment,
sum(isnull(a.Due_Amount,0)-isnull(a.Payment_amount,0)) OVER( ORDER BY [Date] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as Balance
from [dbo].[Retailer_Statement] a
where a.Retailer_Name='jewel'
and Date Between '2021-10-25' and '2021-10-28'

Answer 1

Try something like this:

;
with B as
(
    select sum(isnull(Due_Amount, 0) - isnull(Payment_amount, 0)) as Balance
    from Retailer_Statement 
    where Retailer_Name = 'jewel' and Date < '2021-10-25' 
),
Q as
(
    select null as Date, 'Previous balance' as name, null as address, null as Contact_no, null as Due, null as Payment, Balance, 0 as g
    from B
    union all
    select 
        a.Date,
        a.Retailer_Name as name,
        a.Retailer_Address as addres,
        a.Contact_No as Contact_no,
        a.Due_Amount as Due,
        a.Payment_amount As Payment,
        Balance + sum(isnull(a.Due_Amount,0)-isnull(a.Payment_amount,0)) OVER( ORDER BY [Date] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as Balance,
        1
    from Retailer_Statement a
    cross apply B
    where a.Retailer_Name='jewel'
    and Date Between '2021-10-25' and '2021-10-28'
)
select Date, name, address, Contact_no, Due, Payment, Balance
from Q
order by g, date