时间戳

首页 > 解决方案 > 前一天的期末余额作为今天的期初余额

sql - 前一天的期末余额作为今天的期初余额

问题描述

我正在为一家小型电子企业开发数据库应用程序。我需要一个 SQL 查询，它将前一天的期末余额作为当天的期初余额。我有以下数据表

Expensis

ExpenseID   Date         Expense 

1           2019-03-01   2,000
2           2019-03-02   1,000
3           2019-03-03   500

收入

IncomeID    Date        Income

1         2019-03-01    10,000
2         2019-03-02    13,000
3         2019-03-03    10,000

所需结果

Date        Opening Balance     Income      Expense    Closing Balance

2019-03-01      0               10,000      2,000       8,000
2019-03-02      8,000           13,000      1,000       20,000  
2019-03-03      20,000          10,000      5,00        29,500

标签： sqlsql-serversql-server-2008

解决方案

可以sum递归使用聚合函数（不能使用lag窗口解析函数sql server 2008）

with Expensis( ExpenseID, Date, Expense ) as
(
 select  1, '2019-03-01', 2000 union all
 select  2, '2019-03-02', 1000 union all
 select  3, '2019-03-03', 500            
 ), Income( IncomeID, Date, Income ) as
     (
     select  1, '2019-03-01', 10000 union all
     select  2, '2019-03-02', 13000 union all
     select  3, '2019-03-03', 10000                 
    ), t as
    (
    select i.date, 
           i.income,
           e.expense,
           sum(i.income-e.expense) over (order by i.date) as closing_balance              
      from income i
      join expensis e on e.date = i.date
     )
     select date, 
            ( closing_balance - income + expense ) as opening_balance,
            income, expense, closing_balance
       from t;

date        opening balance income  expense closing balance
----------  --------------- ------  ------- ---------------
2019-03-01  0               10000   2000    8000
2019-03-02  8000            13000   1000    20000
2019-03-03  20000           10000   500     29500

Demo

推荐阅读