Блог

Главная - Вопросы по программированию - Вычислительный Баланс

Вычислительный Баланс

#sqlite

Вопрос:

Со следующим запросом:

 select b1.Name DrBook, c1.Name DrControl, b2.Name CrBook, c2.Name CrControl, tn.Amount
from Transactions tn
left join Books b1 on b1.Id = tn.DrBook
left join Books b2 on b2.Id = tn.CrBook
left join ControlLedgers c1 on c1.Id = tn.DrControl
left join ControlLedgers c2 on c2.Id = tn.CrControl

Я получаю этот набор результатов для баланса:

  --------------------- ----------- --------------------- ----------- -------- 
|       DrBook        | DrControl |       CrBook        | CrControl | Amount |
 --------------------- ----------- --------------------- ----------- -------- 
| Current Assets      | Cash      | Fund                | Initial   | 100000 |
| Current Assets      | Cash      | Fund                | Initial   | 100000 |
| Current Assets      | Cash      | Fund                | Initial   | 100000 |
| Current Assets      | Cash      | Fund                | Initial   | 100000 |
| Current Assets      | Cash      | Fund                | Initial   | 100000 |
| Expenses            | Foods     | Current Liabilities | Payables  |  10000 |
| Current Liabilities | Payables  | Current Assets      | Cash      |   5000 |
 --------------------- ----------- --------------------- ----------- --------

Чтобы представить баланс в своем приложении, я сейчас делаю два запроса и получаю два набора результатов, подобных этим:

запрос 1:

 select b1.Name DrBook, c1.Name DrControl, SUM(tn.Amount) Amount
from Transactions tn
left join Books b1 on b1.Id = tn.DrBook
left join ControlLedgers c1 on c1.Id = tn.DrControl
group by DrBook, DrControl

набор результатов 1:

  --------------------- ----------- -------- 
|       DrBook        | DrControl | Amount |
 --------------------- ----------- -------- 
| Current Assets      | Cash      | 500000 |
| Expenses            | Foods     |  10000 |
| Current Liabilities | Payables  |   5000 |
 --------------------- ----------- --------

запрос 2:

 select b1.Name CrBook, c1.Name CrControl, SUM(tn.Amount) Amount
from Transactions tn
left join Books b1 on b1.Id = tn.CrBook
left join ControlLedgers c1 on c1.Id = tn.CrControl
group by CrBook, CrControl

набор результатов 2:

  --------------------- ----------- -------- 
|       CrBook        | CrControl | Amount |
 --------------------- ----------- -------- 
| Current Assets      | Cash      |   5000 |
| Current Liabilities | Payables  |  10000 |
| Fund                | Initial   | 500000 |
 --------------------- ----------- --------

и вычтите результирующий набор 2 из результирующего набора 1, если это Активы или Расходы (в данном случае Текущие активы и расходы), и результирующий набор 1 из результирующего набора 2, если это Обязательства, Доходы или Фонд (в данном случае Текущие обязательства и Фонд), чтобы получить конечный результирующий набор, подобный этому:

  --------------------- --------------- --------- 
|        Book         | ControlLedger | Balance |
 --------------------- --------------- --------- 
| Current Assets      | Cash          |  495000 |
| Expenses            | Food          |   10000 |
| Current Liabilities | Payables      |    5000 |
| Fund                | Initial       |  500000 |
 --------------------- --------------- ---------

I’ve tried some case statement to get the final result set through sql query instead of computing manually in application code BUT those didn’t work!

EDIT

Here’s the definition for the Table:

 CREATE TABLE "Transactions"(
    "Id"            INTEGER NOT NULL,
    "Date"          TEXT NOT NULL,
    "DrBook"        INTEGER NOT NULL,
    "CrBook"        INTEGER NOT NULL,
    "DrControl"     INTEGER NOT NULL,
    "CrControl"     INTEGER NOT NULL,
    "DrLedger"      INTEGER,
    "CrLedger"      INTEGER,    
    "DrSubLedger"   INTEGER,
    "CrSubLedger"   INTEGER,
    "DrPartyGroup"  INTEGER,
    "CrPartyGroup"  INTEGER,
    "DrParty"       INTEGER,
    "CrParty"       INTEGER,
    "DrMember"      INTEGER,
    "CrMember"      INTEGER,
    "Amount"        INTEGER NOT NULL,
    "Narration"     TEXT,

    FOREIGN KEY("DrBook") REFERENCES "Books"("Id"),
    FOREIGN KEY("CrBook") REFERENCES "Books"("Id"),
    FOREIGN KEY("DrControl") REFERENCES "ControlLedgers"("Id"),
    FOREIGN KEY("CrControl") REFERENCES "ControlLedgers"("Id"),
    FOREIGN KEY("DrLedger") REFERENCES "Ledgers"("Id"),
    FOREIGN KEY("CrLedger") REFERENCES "Ledgers"("Id"),
    FOREIGN KEY("DrSubLedger") REFERENCES "SubLedgers"("Id"),
    FOREIGN KEY("CrSubLedger") REFERENCES "SubLedgers"("Id"),
    FOREIGN KEY("DrPartyGroup") REFERENCES PartyGroups("Id"),
    FOREIGN KEY("CrPartyGroup") REFERENCES PartyGroups("Id"),
    FOREIGN KEY("DrParty") REFERENCES "Parties"("Id"),
    FOREIGN KEY("CrParty") REFERENCES "Parties"("Id"),
    FOREIGN KEY("DrMember") REFERENCES "Members"("Id"),
    FOREIGN KEY("CrMember") REFERENCES "Members"("Id")  
);

для каждого Журнала я вставляю одну строку, и она содержит информацию о дебете, Кредите и Сумме. У меня нет Dr/CrProduct или Dr/CrServices, так как это было разработано для ведения бухгалтерского учета и учета отдельных лиц и семей.

Для покупки продуктов питания у г-на А, например, я прохожу (1):

 Expenses -> Food -> Rice -> Fine Rice A/c          Dr. 10000
    Current Liabilities -> Payables A/C            Cr. 10000

если это в кредит и когда сумма покупки оплачивается наличными, я передаю (2):

 Current Liabilities -> Payables A/C                 Dr. 10000
    Current Assets -> Cash -> In Hand -> Emon A/c   Cr. 10000

В таблице это становится:

  ---- ------------ -------- -------- ----------- ----------- ---------- ---------- ------------- ------------- -------------- -------------- --------- --------- ---------- ---------- -------- ---------------- 
| Id |    Date    | DrBook | CrBook | DrControl | CrControl | DrLedger | CrLedger | DrSubLedger | CrSubLedger | DrPartyGroup | CrPartyGroup | DrParty | CrParty | DrMember | CrMember | Amount |   Narration    |
 ---- ------------ -------- -------- ----------- ----------- ---------- ---------- ------------- ------------- -------------- -------------- --------- --------- ---------- ---------- -------- ---------------- 
|  3 | 2020-06-15 |      3 |      5 |         9 |        18 |        2 |          |           2 |             |              |            4 |         |       1 |          |          |  10000 | Some Narration |
|  3 | 2020-06-15 |      5 |      2 |        18 |         7 |          |        1 |             |           1 |            4 |              |       1 |         |          |          |  10000 |                |
 ---- ------------ -------- -------- ----------- ----------- ---------- ---------- ------------- ------------- -------------- -------------- --------- --------- ---------- ---------- -------- ----------------

и вот краткий разбор первого ряда:

  -------------- ---------------- --------------------- 
|   Columns    |     Values     |      Mappings       |
 -------------- ---------------- --------------------- 
| DrBook       | 3              | Expenses            |
| CrBook       | 5              | Current Liabilities |
| DrControl    | 9              | Food                |
| CrControl    | 18             | Payables            |
| DrLedger     | 2              | Rice                |
| DrSubLedger  | 2              | Fine Rice           |
| CrPartyGroup | 4              | Groceries           |
| CrParty      | 1              | Mr. A               |
| Amount       | 10000          |                     |
| Narration    | Some Narration |                     |
 -------------- ---------------- ---------------------

Комментарии:

1. Строки таблицы Transactions содержат значения для всех столбцов DrBook, DrControl, CrBook, CrControl? или в каждой строке есть нули для 2 из них? Было бы лучше опубликовать примеры данных.

2. @forpas, я добавил подробное объяснение того, как я вставляю эти строки и что они означают.

3. На мой взгляд, мало что можно сделать (на мой взгляд). Возможно, нет необходимости в cte t4, потому что агрегирование и вычитание можно выполнить за 1 шаг.

4. @forpas, да, удалил t4 и обновил код в ответ

5. Я бы написал это так: sum(case when Id <=3 then 1 else -1 end * Amount)

Ответ №1:

Не уверен, что это единственный способ:

 with t1(Id, Book, Control, Amount) as (
    select tn.DrBook, b1.Name, c1.Name, sum(tn.Amount)
    from Transactions tn
    left join Books b1 on b1.Id = tn.DrBook
    left join ControlLedgers c1 on c1.Id = tn.DrControl
    group by DrBook, DrControl
), 
t2 as (
    select tn.CrBook, b1.Name, c1.Name, -1*sum(tn.Amount)
    from Transactions tn
    left join Books b1 on b1.Id = tn.CrBook
    left join ControlLedgers c1 on c1.Id = tn.CrControl
    group by CrBook, CrControl
), 
t3 as (
    select * from t1 union all select * from t2
)
select Book, Control,
    case when Id <=3 then sum(Amount)
    else -1*sum(Amount) end Balance
from t3 group by Book, Control order by Id

и это дает именно то, что я ожидал:

  --------------------- ---------- --------- 
|        Book         | Control  | Balance |
 --------------------- ---------- --------- 
| Current Assets      | Cash     |  495000 |
| Current Liabilities | Payables |    5000 |
| Expenses            | Foods    |   10000 |
| Fund                | Initial  |  500000 |
 --------------------- ---------- ---------
Метки: