某些行的期初余额错误

Question

我想计算业务应用程序的期初和期末余额。但是对于某些行，错误的期初余额正在产生。我有以下数据表：

供应商付款

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

费用

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

收入

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 查询

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

输出

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

计算期末余额的公式为：

Closing = Opening + Income - Expense - Bill

正如我们可以注意到的，日期2018-09-27的期初余额-30,000这是错误的。应该是-23,000.同样，日期2019-03-29的期初余额也是错误的。

所需结果

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

有可能

在任何一天都没有售出任何物品，但有费用或账单支付给供应商，反之亦然。

此外，任何表都可能在同一日期有两个条目。

Answer 1

您的问题在于SUM和LAG的列顺序。如果您仅从 t CTE 中进行选择，您会看到您不是按三个日期中的任何一个(可用日期(中的任何一个进行排序，而是按您指定的顺序排序。因此，您将首先获得第一列的 NULL，第一列的 NULL，依此类推。您需要做的是更快地引入 EventDate，并在 t 中按它对所有内容进行排序。

;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

请注意 t CTE 中的CROSS APPLY，其中我将所有日期COALESCE为一个宇宙化日期。我不得不重命名第一个 CTE 以匹配您的示例数据表名称，但您了解了它的要点。