sql server 2008运行2个日期之间的总计

I need to get running totals between 2 dates in my sql server table and update the records simultaneoulsy. My data is as below and ordered by date,voucher_no

我需要在我的sql server表中的两个日期之间运行总计并同时更新记录。我的数据如下,按日期排序,voucher_no

DATE         VOUCHER_NO    OPEN_BAL   DEBITS   CREDITS    CLOS_BAL 
-------------------------------------------------------------------    
10/10/2017       1            100       10                  110    
12/10/2017       2            110                5          105
13/10/2017       3            105       20                  125

Now if i insert a record with voucher_no 4 on 12/10/2017 the output should be like

现在如果我在12/10/2017插入带有voucher_no 4的记录,输出应该是这样的

DATE         VOUCHER_NO    OPEN_BAL   DEBITS   CREDITS    CLOS_BAL
------------------------------------------------------------------    
10/10/2017       1            100       10                  110    
12/10/2017       2            110                5          105    
12/10/2017       4            105       4                   109
13/10/2017       3            109       20                  129

I have seen several examples which find running totals upto a certain date but not between 2 dates or from a particular date to end of file

我已经看到几个例子,它们可以查找到某个日期的运行总计,但不会发现两个日期之间或从特定日期到文件结尾

1 个解决方案

#1

You should consider changing your database structure. I think it will be better to keep DATE, VOUCHER_NO, DEBITS, CREDITS in one table. And create view to calculate balances. In that case you will not have to update table after each insert. In this case your table will look like

您应该考虑更改数据库结构。我认为将DATE,VOUCHER_NO,DEBITS,CREDITS保留在一个表中会更好。并创建视图来计算余额。在这种情况下,您不必在每次插入后更新表。在这种情况下,您的表格看起来像

create table myTable (
    DATE date 
    , VOUCHER_NO int
    , DEBITS int
    , CREDITS int
)

insert into myTable values
    ('20171010', 1, 10, null),( '20171012', 2, null, 5)
    , ('20171013', 3, 20, null), ('20171012', 4, 4, null)

And view will be

而观点将是

;with cte as (
    select
        DATE, VOUCHER_NO, DEBITS, CREDITS, bal = isnull(DEBITS, CREDITS) * case when DEBITS is null then -1 else 1 end
        , rn = row_number() over (order by DATE, VOUCHER_NO)
    from 
        myTable
)

select
    a.DATE, a.VOUCHER_NO, a.DEBITS, a.CREDITS
    , OPEN_BAL = sum(b.bal + case when b.rn = 1 then 100 else 0 end) - a.bal
    , CLOS_BAL = sum(b.bal + case when b.rn = 1 then 100 else 0 end)
from
    cte a
    join cte b on a.rn >= b.rn
group by a.DATE, a.VOUCHER_NO, a.rn, a.bal, a.DEBITS, a.CREDITS

Here's another solution if you can not change your db structure. In this case you must run update statement each time after inserts. In both cases I assume that initial balance is 100 while recalculation

如果您无法更改数据库结构,这是另一种解决方案。在这种情况下,每次插入后都必须运行update语句。在这两种情况下,我都假设重新计算时初始余额为100

create table myTable (
    DATE date 
    , VOUCHER_NO int
    , OPEN_BAL int
    , DEBITS int
    , CREDITS int
    , CLOS_BAL int
)

insert into myTable values 
('20171010', 1, 100, 10, null, 110)
,( '20171012', 2, 110, null, 5, 105)
, ('20171013', 3, 105, 20, null, 125)
, ('20171012', 4, null, 4, null, null)

;with cte as (
    select
        DATE, VOUCHER_NO, DEBITS, CREDITS, bal = isnull(DEBITS, CREDITS) * case when DEBITS is null then -1 else 1 end
        , rn = row_number() over (order by DATE, VOUCHER_NO)
    from 
        myTable
)
, cte2 as (
    select
        a.DATE, a.VOUCHER_NO
        , OPEN_BAL = sum(b.bal + case when b.rn = 1 then 100 else 0 end) - a.bal
        , CLOS_BAL = sum(b.bal + case when b.rn = 1 then 100 else 0 end)
    from
        cte a
        join cte b on a.rn >= b.rn
    group by a.DATE, a.VOUCHER_NO, a.rn, a.bal
)

update a
set a.OPEN_BAL = b.OPEN_BAL, a.CLOS_BAL = b.CLOS_BAL
from
    myTable a
    join cte2 b on a.DATE = b.DATE and a.VOUCHER_NO = b.VOUCHER_NO