代码之家 › 专栏 › 技术社区 › Doonie Darkoo

取得期初余额和期末余额

sql-server

Doonie Darkoo · 技术社区 · 6 年前

我在这里的某个人的帮助下编写了这个表结构和查询,它工作得很好。我想得到这两个日期之间的期初和期末余额。我已经评论了我想要获取日期的日期,如果我运行该日期检查,我想要的预期输出如下所示。

以下是结构和示例数据:

DROP TABLE [TransactionMaster];
DROP TABLE [VoucherType];

CREATE TABLE [VoucherType](
    [VoucherTypeCode] [tinyint] NOT NULL PRIMARY KEY,
    [FullName] [nvarchar](255) NOT NULL
);


INSERT INTO [VoucherType] VALUES (1, 'Cash Payment Voucher');
INSERT INTO [VoucherType] VALUES (2, 'Cash Receipt Voucher');
INSERT INTO [VoucherType] VALUES (3, 'Bank Payment Voucher');
INSERT INTO [VoucherType] VALUES (4, 'Bank Receipt Voucher');


CREATE TABLE [TransactionMaster](
    [ID] [bigint] NOT NULL PRIMARY KEY,
    [VoucherTypeCode] [tinyint] NOT NULL,
    [PayeeName] [varchar](255) NOT NULL,
    [TransactionDate] datetime,
    [Amount] NUMERIC,
    [RefNo] [nvarchar](50) NULL
    CONSTRAINT [FK_tbl_TransactionMaster_tbl_VoucherType] FOREIGN KEY([VoucherTypeCode])
    REFERENCES [VoucherType] ([VoucherTypeCode])
)



INSERT INTO [TransactionMaster] VALUES (1, 2, 'Asim', '2018-03-21', '2500', 'CRV-0001-LHR');
INSERT INTO [TransactionMaster] VALUES (2, 4, 'Ali', '2018-03-21', '1150', 'BRV-2421-KHI');
INSERT INTO [TransactionMaster] VALUES (3, 1, 'Erick', '2018-03-23', '1200', 'CPV-5435-ISL');
INSERT INTO [TransactionMaster] VALUES (4, 3, 'Asim', '2018-03-24', '1000', 'BPV-2345-CAN');
INSERT INTO [TransactionMaster] VALUES (5, 2, 'Mehboob', '2018-03-25', '2400', 'CRV-2976-PSH');
INSERT INTO [TransactionMaster] VALUES (6, 1, 'Erick', '2018-03-25', '2900', 'CPV-2323-KOH');
INSERT INTO [TransactionMaster] VALUES (7, 1, 'Feroze', '2018-03-21', '3100', 'CRV-0531-SRG');
INSERT INTO [TransactionMaster] VALUES (8, 3, 'Ali', '2018-03-21', '500', 'BRV-2001-RWP');

这是问题

    with data1 as (
select a.id inid,a.VoucherTypeCode,PayeeName,a.Amount InAmount,TransactionDate,RefNo,FullName from TransactionMaster a inner join  [VoucherType] b on a.VoucherTypeCode = b.VoucherTypeCode
where a.VoucherTypeCode in (1,3)
),

data2 as (
select a.id outid,a.VoucherTypeCode,PayeeName,a.Amount OutAmount,TransactionDate,RefNo,FullName from TransactionMaster a inner join  [VoucherType] b on a.VoucherTypeCode = b.VoucherTypeCode
where a.VoucherTypeCode in (2,4)
)
select *,COALESCE(a.TransactionDate,b.TransactionDate) as FullDate from data1 a full join data2 b on inid = outid and a.TransactionDate = b.TransactionDate

--WHERE COALESCE(a.TransactionDate,b.TransactionDate) BETWEEN '2018-03-23 00:00:00.000' AND '2018-03-24 00:00:00.000'
order by FullDate

现在的输出如下:

inid                 VoucherTypeCode PayeeName                                                                                                                                                                                                                                                       InAmount                                TransactionDate         RefNo                                              FullName                                                                                                                                                                                                                                                        outid                VoucherTypeCode PayeeName                                                                                                                                                                                                                                                       OutAmount                               TransactionDate         RefNo                                              FullName                                                                                                                                                                                                                                                        FullDate
-------------------- --------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------- ----------------------- -------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------- --------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------- ----------------------- -------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------------------
7                    1               Feroze                                                                                                                                                                                                                                                          3100                                    2018-03-21 00:00:00.000 CRV-0531-SRG                                       Cash Payment Voucher                                                                                                                                                                                                                                            NULL                 NULL            NULL                                                                                                                                                                                                                                                            NULL                                    NULL                    NULL                                               NULL                                                                                                                                                                                                                                                            2018-03-21 00:00:00.000
8                    3               Ali                                                                                                                                                                                                                                                             500                                     2018-03-21 00:00:00.000 BRV-2001-RWP                                       Bank Payment Voucher                                                                                                                                                                                                                                            NULL                 NULL            NULL                                                                                                                                                                                                                                                            NULL                                    NULL                    NULL                                               NULL                                                                                                                                                                                                                                                            2018-03-21 00:00:00.000
NULL                 NULL            NULL                                                                                                                                                                                                                                                            NULL                                    NULL                    NULL                                               NULL                                                                                                                                                                                                                                                            2                    4               Ali                                                                                                                                                                                                                                                             1150                                    2018-03-21 00:00:00.000 BRV-2421-KHI                                       Bank Receipt Voucher                                                                                                                                                                                                                                            2018-03-21 00:00:00.000
NULL                 NULL            NULL                                                                                                                                                                                                                                                            NULL                                    NULL                    NULL                                               NULL                                                                                                                                                                                                                                                            1                    2               Asim                                                                                                                                                                                                                                                            2500                                    2018-03-21 00:00:00.000 CRV-0001-LHR                                       Cash Receipt Voucher                                                                                                                                                                                                                                            2018-03-21 00:00:00.000
3                    1               Erick                                                                                                                                                                                                                                                           1200                                    2018-03-23 00:00:00.000 CPV-5435-ISL                                       Cash Payment Voucher                                                                                                                                                                                                                                            NULL                 NULL            NULL                                                                                                                                                                                                                                                            NULL                                    NULL                    NULL                                               NULL                                                                                                                                                                                                                                                            2018-03-23 00:00:00.000
4                    3               Asim                                                                                                                                                                                                                                                            1000                                    2018-03-24 00:00:00.000 BPV-2345-CAN                                       Bank Payment Voucher                                                                                                                                                                                                                                            NULL                 NULL            NULL                                                                                                                                                                                                                                                            NULL                                    NULL                    NULL                                               NULL                                                                                                                                                                                                                                                            2018-03-24 00:00:00.000
6                    1               Erick                                                                                                                                                                                                                                                           2900                                    2018-03-25 00:00:00.000 CPV-2323-KOH                                       Cash Payment Voucher                                                                                                                                                                                                                                            NULL                 NULL            NULL                                                                                                                                                                                                                                                            NULL                                    NULL                    NULL                                               NULL                                                                                                                                                                                                                                                            2018-03-25 00:00:00.000
NULL                 NULL            NULL                                                                                                                                                                                                                                                            NULL                                    NULL                    NULL                                               NULL                                                                                                                                                                                                                                                            5                    2               Mehboob                                                                                                                                                                                                                                                         2400                                    2018-03-25 00:00:00.000 CRV-2976-PSH                                       Cash Receipt Voucher                                                                                                                                                                                                                                            2018-03-25 00:00:00.000

预期输出如下:

    inid                 VoucherTypeCode PayeeName                                                                                                                                                                                                                                                       InAmount                                TransactionDate         RefNo                                              FullName                                                                                                                                                                                                                                                        outid                VoucherTypeCode PayeeName                                                                                                                                                                                                                                                       OutAmount                               TransactionDate         RefNo                                              FullName                                                                                                                                                                                                                                                        FullDate                            Opening
-------------------- --------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------- ----------------------- -------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------- --------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------- ----------------------- -------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------------------
3                    1               Erick                                                                                                                                                                                                                                                           1200                                    2018-03-23 00:00:00.000 CPV-5435-ISL                                       Cash Payment Voucher                                                                                                                                                                                                                                            NULL                 NULL            NULL                                                                                                                                                                                                                                                            NULL                                    NULL                    NULL                                               NULL                                                                                                                                                                                                                                                            2018-03-23 00:00:00.000                 -50
4                    3               Asim                                                                                                                                                                                                                                                            1000                                    2018-03-24 00:00:00.000 BPV-2345-CAN                                       Bank Payment Voucher                                                                                                                                                                                                                                            NULL                 NULL            NULL                                                                                                                                                                                                                                                            NULL                                    NULL                    NULL                                               NULL                                                                                                                                                                                                                                                            2018-03-24 00:00:00.000                 1150

公式是 (Opening+InAmount) - Out Amount 将会是 Closing Balance 和 Opening 将是前一行 期终余额 是的。所以对于第一个记录,打开将为0,关闭将为3100,对于第二个记录,打开=3100,关闭3600,依此类推。

1 回复 | 直到 6 年前

Squirrel 6 年前

使用 OUTER APPLY 计算日期之前的金额。

使用条件 SUM() 计算金额

select  *
from    [TransactionMaster] m
        inner join [VoucherType] t  on  m.VoucherTypeCode   = t.VoucherTypeCode 
        outer apply
        (   
             select Opening = sum(case when [VoucherTypeCode] in (1, 3) 
                                       then Amount 
                                       else -Amount 
                                       end)
             from   [TransactionMaster] x
             where  x.TransactionDate   < m.TransactionDate
        ) o
where   m.TransactionDate   between '2018-03-23' and '2018-03-24'
order by ID

关于 CASE WHEN 陈述

金额被视为 IN 或 OUT 取决于凭证类型代码。代码1和3是 在 /积极的和其他的 出局 /否定的 case语句的作用是根据vouchertypecode将金额转换为正值或负值

SELECT VoucherTypeCode, Amount
      case when [VoucherTypeCode] in (1, 3)  
      then +Amount 
      else -Amount 
      end
FROM  ...

所以基本上你会得到

1 2 2500 -2500
2 4 1150 -1150
3 1 1200  1200

所以当你把它加起来的时候,它是-2500-1150+1200+。是的。