Wrong Opening Balance for some rows

I want to calculate Opening and Closing Balance of a business application. But for some rows wrong Opening Balance is producing. I have following Data Tables:

SupplierPayments

DateOfPayment   Bill

2018-06-01      4000
2018-06-01      9000
2018-06-19      2000
2018-06-19      6000
2019-03-28      3000
2019-03-29      5000

Expensis

DateOfExpense   Expense

2018-08-14      2,000
2019-02-26      8,000
2019-03-28      2000
2019-03-29      2000

Income

DateSold        Income

2018-09-27      24,000
2018-10-17      8,000
2019-01-01      13,000
2019-03-28      10,000

SQL Server 2012 Query

with Income( DateSold, Income ) as (
    select DateSold,isnull(sum(TotalBill),0)
    from SalesInvoice group by DateSold
), SupplierPayments( DateOfPayment,Bill ) as(
    select DateOfPayment,isnull(sum(BillPaidAmount),0) 
    from SupplyInvoicePaymentHistory group by DateOfPayment
), Expensis( DateOfExpense, Expense ) as(
    select Date ,isnull(sum(Amount),0) 
    from GeneralExpense group by Date
), t as (
    select i.DateSold
        ,e.DateOfExpense
        ,sp.DateOfPayment
        ,i.income
        , e.Expense
        ,sp.Bill
        , sum(isnull(i.income,0)-(isnull(e.Expense,0)+isnull(sp.Bill,0))) over (order by i.DateSold,e.DateOfExpense,sp.DateOfPayment) as closing_balance 
    from income i 
    full outer  join expensis e on e.DateOfExpense = i.DateSold 
    full outer join SupplierPayments sp on sp.DateOfPayment=e.DateOfExpense

) 
select m.EventDate, m.DateSold
    ,m.DateOfExpense
    ,m.DateOfPayment
    ,isnull(m.opening_balance,0) as Opening_Balance
    ,isnull(m.Income,0) as Income
    ,isnull(m.Expense,0) as Expensis
    ,isnull(m.Bill,0) as SupplierPayments
    ,isnull(m.closing_balance,0) as Closing_Balance 
from (
        select coalesce(coalesce(DateOfPayment, DateOfExpense), DateSold) EventDate, DateSold
        ,DateOfExpense
        ,DateOfPayment
        ,lag(closing_balance,1,0) over (order by DateSold, DateOfExpense,DateOfPayment) as opening_balance
        ,Income
        ,Expense
        ,closing_balance
        ,Bill 
    from t
) as m order by m.EventDate ASC

Output

EventDate   DateSold ExpenseDate    PaymentDate  Opening  Income Expense Bill   Closing

2018-06-01  NULL        NULL        2018-06-01   0        0      0      13000   -13000
2018-06-19  NULL        NULL        2018-06-19  -13000    0      0      8000    -21000
2018-08-14  NULL        2018-08-14  NULL        -21000    0      2000    0      -23000
2018-09-27  2018-09-27  NULL        NULL        -30000    24000  0       0      -6000 
2019-01-01  2019-01-01  NULL        NULL        -6000     13000  0       0      7000
2019-03-28  2019-03-28  2019-03-28  2019-03-28  7000      10000  2000   3000    12000
2019-03-29  NULL        2019-03-29  2019-03-29  -23000     0     2000   5000   -30000

Formula to calculate closing balance is as:

Closing = Opening + Income - Expense - Bill

As we can notice that opening balance for date 2018-09-27 is -30,000 which is wrong. It should be -23,000. similarly opening balance for date 2019-03-29 is also wrong.

Required Result

EventDate   DateSold ExpenseDate    PaymentDate  Opening  Income Expense Bill   Closing

2018-06-01  NULL        NULL        2018-06-01   0        0      0      13000   -13000
2018-06-19  NULL        NULL        2018-06-19  -13000    0      0      8000    -21000
2018-08-14  NULL        2018-08-14  NULL        -21000    0      2000    0      -23000
2018-09-27  2018-09-27  NULL        NULL        -23000    24000  0       0      1000 
2019-01-01  2019-01-01  NULL        NULL        1000      13000  0       0      14000
2019-03-28  2019-03-28  2019-03-28  2019-03-28  14000     10000  2000   3000    19000
2019-03-29  NULL        2019-03-29  2019-03-29  19000      0     2000   5000   12000

It is possible that in any day there is No item sold but there is expense or bill paid to supplier and vice versa.

Also it is possible that any tables has two entries on the same date.

Answer

Your issue lies within the order by columns for both SUM and LAG. If you selected from t CTE alone, you’d see that you’re not ordering by whatever is in any of the three dates (what’s available), you’re ordering by them in the order you specified. So you’ll get NULLs for the first column first, NULLs for the second column first, etc. What you need to do is introduce the EventDate sooner, in t, and order everything by it.

;with xIncome( DateSold, Income ) as (
    select DateSold,isnull(sum(income),0)
    from income group by DateSold
), xSupplierPayments( DateOfPayment,Bill ) as(
    select DateOfPayment,isnull(sum(bill),0) 
    from supplierpayments group by DateOfPayment
), xExpensis( DateOfExpense, Expense ) as(
    select DateOfExpense Date ,isnull(sum(expense),0) 
    from expensis group by Dateofexpense
), t as (
    select i.DateSold
        ,e.DateOfExpense
        ,sp.DateOfPayment
        ,consolidated.date consolidatedDate
        ,i.income
        , e.Expense
        ,sp.Bill
        , sum(isnull(i.income,0)-(isnull(e.Expense,0)+isnull(sp.Bill,0))) over (order by consolidated.date) as closing_balance 
    from xincome i 
    full outer  join xexpensis e on e.DateOfExpense = i.DateSold 
    full outer join xSupplierPayments sp on sp.DateOfPayment=e.DateOfExpense
    cross apply (select coalesce(i.DateSold,e.DateOfExpense,sp.DateOfPayment) as date) consolidated

) 
select consolidatedDate, m.DateSold
    ,m.DateOfExpense
    ,m.DateOfPayment
    ,isnull(m.opening_balance,0) as Opening_Balance
    ,isnull(m.Income,0) as Income
    ,isnull(m.Expense,0) as Expensis
    ,isnull(m.Bill,0) as SupplierPayments
    ,isnull(m.closing_balance,0) as Closing_Balance 
from (
        select consolidatedDate
        ,DateSold
        ,DateOfExpense
        ,DateOfPayment
        ,lag(closing_balance,1,0) over (order by consolidatedDate) as opening_balance
        ,Income
        ,Expense
        ,closing_balance
        ,Bill 
    from t
) as m order by m.consolidatedDate ASC

Notice the CROSS APPLY in t CTE, where I COALESCE all the dates into a single cosnolidated date. I had to rename the first CTEs to match your sample data table names, but you get the gist of it.