Получить баланс открытия и закрытия

У меня есть эта структура таблицы и запрос, написанный с помощью какого-то парня здесь, и он отлично работает. Я хочу получить DROP TABLE [ TransactionMaster ]; DROP TABLE [ VoucherType ]; CREATE TABLE [ VoucherType ] ( [ VoucherTypeCode ] [ tinyint ] NOT NULL PRIMARY KEY , [ FullName ] [ nvarchar ] ( 255 ) NOT NULL ); INSERT INTO [ VoucherType ] ВАЛЮТЫ ( 1 , «Ваучер оплаты наличными» ); INSERT INTO [ VoucherType ] VALUES ( 2 , «Ваучер квитанции» ); INSERT INTO [ VoucherType ] VALUES ( 3 , «Ваучер для банковских платежей» ); INSERT INTO [ VoucherType ] ЗНАЧЕНИЯ ( 4 , «Ваучер банковского чека» ); 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 ]) ССЫЛКИ [ VoucherType ] ([ VoucherTypeCode ]) ) INSERT INTO [ TransactionMaster ] ЦЕННОСТИ ( 1 , 2 , 'Asim' , '2018-03-21' , '2500' , 'CRV- 0001-LHR ' ); INSERT INTO [ TransactionMaster ] ЦЕННОСТИ ( 2 , 4 , 'Ali' , '2018-03-21' , '1150' , 'BRV-2421-KHI' ); INSERT INTO [ TransactionMaster ] ЗНАЧЕНИЯ ( 3 , 1 , 'Erick' , '2018-03-23' , '1200' , 'CPV-5435-ISL' ); INSERT INTO [ TransactionMaster ] ЦЕННОСТИ ( 4 , 3 , 'Asim' , '2018-03-24' , '1000' , 'BPV-2345-CAN' ); INSERT INTO [ TransactionMaster ] ЗНАЧЕНИЯ ( 5 , 2 , 'Mehboob' , '2018-03-25' , '2400' , 'CRV-2976-PSH' ); INSERT INTO [ TransactionMaster ] ЗНАЧЕНИЯ ( 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 ] ЗНАЧЕНИЯ ( 8 , 3 , 'Али' , '2018-03-21' , '500' , 'BRV-2001-RWP' ); и с data1 как ( выберите в . ID ИНИД , а . VoucherTypeCode , PayeeName , . Сумма InAmount , TransactionDate , Код ссылки , FullName из TransactionMaster внутренней присоединиться к [ VoucherType ] Ь на с . VoucherTypeCode = б . VoucherTypeCode , где а . VoucherTypeCode в ( 1 , 3 ) ), data2 as ( выберите a . Id outid , a . VoucherTypeCode , PayeeName , a . Сумма OutAmount , TransactionDate , RefNo , FullName из TransactionMaster, внутренний 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 between the dates. I have commented the date on which I want to get the date and if I run that date check the expected 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.000put I want is shown below.

Here is the structure and sample data:

    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                 1150t prettypr(Opening+InAmount) - Out Amountted">Closing Balance

Here is the query

Opening

The Closing Balanceput right now is like this::

OUTER APPLY

The expected output is this:

SUM()

The formula is that 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 will be the CASE WHEN and Opening will be previous rows Closing Balance. So for first record opening will be 0 and closing will be 3100 and for second opening = 3100 and closing 3600 and so on.

sql-server,

0

Ответов: 1

2

use IN to calculate the Amount before the date.

use conditional OUT to calculate the amount

IN

Explanation on the OUT statement

the amount is treated as IN or OUT depending on the VoucherTypeCode. For code 1 and 3 it is IN / positive and others is OUT / negative What the case statement does is to convert the amount to positive or negative value depending on the VoucherTypeCode

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

SO basically you will get

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

so when you SUM() it up, it will be -2500 - 1150 + 1200 + . .

sql-server,
Похожие вопросы
Яндекс.Метрика