我正在为一家商店开发一个应用程序。在这项业务中,有可能在任何一天都没有出售任何物品,但有费用或账单支付给供应商,反之亦然。期末余额的计算公式为:
Closing_Balance = Opening_Balance + Income - Expense - Bill
我有以下表格
供应商付款
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 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.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 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
输出
DateSold ExpenseDate PaymentDate Opening Income Expense Bill Closing
NULL NULL 2018-06-01 0 0 0 4,000 -4,000
NULL NULL 2018-06-19 -4000 0 0 2,000 -6,000
NULL 2018-08-14 NULL -6,000 0 2,000 0 -8,000
NULL 2019-02-26 NULL -8,000 0 8,000 0 -16,000
NULL 2019-03-29 2019-03-29 -16,000 0 2000 5000 -23,000
2018-09-27 NULL NULL -23,000 24,000 0 0 1,000
2018-10-17 NULL NULL 1,000 8,000 0 0 9,000
2019-01-01 NULL NULL 9,000 13,000 0 0 22,000
2019-03-28 2019-03-28 2019-03-28 22,000 10,000 2000 3000 27,000
由于日期列的顺序,Closing
平衡是错误的。 我想要以下输出,其中 Date 根据 3 个日期列按升序排列
所需结果
DateSold ExpenseDate PaymentDate Opening Income Expense Bill Closing
NULL NULL 2018-06-01 0 0 0 4,000 -4,000
NULL NULL 2018-06-19 -4000 0 0 2,000 -6,000
NULL 2018-08-14 NULL -6,000 0 2,000 0 -8,000
2018-09-27 NULL NULL -8,000 24,000 0 0 16,000
2018-10-17 NULL NULL 16,000 8,000 0 0 24,000
2019-01-01 NULL NULL 24,000 13,000 0 0 37,000
NULL 2019-02-26 NULL 37,000 0 8,000 0 29,000
2019-03-28 2019-03-28 2019-03-28 29,000 10,000 2000 3000 34,000
NULL 2019-03-29 2019-03-29 34,000 0 2000 5000 29,000
我认为union all
group by
可能是一个更好的方法:
select dte, sum(bill) as bill, sum(expense) as expense,
sum(income) as income,
sum(income - expense - bill) over (order by dte) - (income - expense - bill) as opening_balance
sum(income - expense - bill) over (order by dte) as closing_balance
from ((select DateOfPayment as dte, Bill, 0 as expense, 0 as income
from SupplierPayments
) union all
(select DateOfExpense, 0, Expense, 0 as income
from expenses
) union all
(select datesold, 0, 0, income
from income
)
) d
group by dte
order by dte;
该查询比full join
查询简单一些,因为您不必处理这么多NULL
值。 更重要的是,如果其中一个表在同一日期有两个条目,则会产生正确的答案。
示例数据:
declare @SupplierPayments table(DateOfPayment date, Bill int);
insert into @SupplierPayments values
('2018-06-01', 4000),
('2018-06-19', 2000),
('2019-03-28', 3000),
('2019-03-29', 5000);
declare @Expensis table(DateOfExpense date, Expense int);
insert into @Expensis values
('2018-08-14',2000),
('2019-02-26',8000),
('2019-03-28',2000),
('2019-03-29',2000);
declare @Income table(DateSold date, Income int);
insert into @Income values
('2018-09-27',24000),
('2018-10-17',8000),
('2019-01-01',13000),
('2019-03-28',10000);
要获得Closing
列,每行使用公式就足够了(无需Opening column
(。然后,要获取Closing
值,只需在该公式的列上使用累积总和就足够了(只需查看查询即可(。累积和可以通过带有order by
over
子句的sum
函数轻松实现。
select EventDate, DateOfExpense, DateOfPayment, DateSold, Income, Expense, Bill,
sum(Income - Expense - Bill)
over (order by EventDate rows between unbounded preceding and 1 preceding) Opening,
sum(Income - Expense - Bill)
over (order by EventDate) Closing
from (
select coalesce(coalesce(DateOfPayment, DateOfExpense), DateSold) EventDate, *
from (
select DateOfPayment, sum(coalesce(Bill, 0)) Bill from @SupplierPayments group by DateOfPayment
) sp
full join (
select DateOfExpense, sum(coalesce(Expense, 0)) Expense from @Expensis group by DateOfExpense
) e on sp.DateOfPayment = e.DateOfExpense
full join (
select DateSold, sum(coalesce(Income, 0)) Income from @Income group by DateSold
) i on sp.DateOfPayment = i.DateSold
) a order by EventDate