I need to build a query to get all the account's monthly balances between Jan/2020 and Dec/2020.
Basically I need this:
Account_id| Year | Jan |Feb| Mar| Apr|May | Jun| Jul| Aug|Sep| Oct| Nov|Dec
--------- ---- --- --- --- --- --- --- --- --- --- --- --- ---
1 | 2020 | 200 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |-500| 400| 0
2 | 2020 | 0 | 0 | 0 | 500| 0 | 0 | 900| 0 | 0 | 100| 0 | 0
3 | 2020 | 100 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 200| 500| 0
Sample Data Table Accounts
account_id| costumer_id|.....
--------- ---- --- --- --
1 | x1 |......
2 | x2 |.....
Table Transfer_ins
id | account_id|amount
--------- ---- --- -
xx | 1 | 100
xx | 2 | 300
Table Transfer_outs
id | account_id|amount
--------- ---- --- --
xx | 1 | 100
xx | 2 | 300
Table Transfer_ins
id | account_id| amount|
--------- ---- --- --- --
yy | 1 | 100 |
yy | 2 | 300 |
Table d_month
id| month_id|action_month|
--------- ---- --- --- --
x1| 1 | january |
x4 2 | february |
Table pix_movements
id |account_id| in_or_out |pix_amount
--------- ---- --- --- -- ----
yy | 1 | pix_out | 300
yy | 2 | pix_in | 900
Table d_time
time_id|action_stamp | week_id | month_id |year_id
--------- ---- --- --- -- ------------------------
uu |2020-01-04T15:00:55.960Z| zz | yy | xx
uu |2020-01-04T15:00:55.960Z| zz | yy | xx
This is what I wrote:
SELECT
account_id, action_month,
ISNULL(SUM(CASE WHEN month(action_month) = 1 THEN transfer_ins.amount END)+SUM(CASE WHEN month(action_month) = 1 AND pix_movements.in_or_out = "pix_in" THEN pix.movements.amount END)-SUM(CASE WHEN month(action_month) = 1 THEN transfer_outs.amount END)-SUM(CASE WHEN month(action_month) = 1 AND pix_movements.in_or_out = "pix_out" THEN pix.movements.amount END), 0) Jan
ISNULL(SUM(CASE WHEN month(action_month) = 2 THEN transfer_ins.amount END)+SUM(CASE WHEN month(action_month) = 2 AND pix_movements.in_or_out = "pix_in" THEN pix.movements.amount END)-SUM(CASE WHEN month(action_month) = 2 THEN transfer_outs.amount END)-SUM(CASE WHEN month(action_month) = 2 AND pix_movements.in_or_out = "pix_out" THEN pix.movements.amount END), 0) Feb
ISNULL(SUM(CASE WHEN month(action_month) = 3 THEN transfer_ins.amount END)+SUM(CASE WHEN month(action_month) = 3 AND pix_movements.in_or_out = "pix_in" THEN pix.movements.amount END)-SUM(CASE WHEN month(action_month) = 3 THEN transfer_outs.amount END)-SUM(CASE WHEN month(action_month) = 3 AND pix_movements.in_or_out = "pix_out" THEN pix.movements.amount END), 0) Mar
ISNULL(SUM(CASE WHEN month(action_month) = 4 THEN transfer_ins.amount END)+SUM(CASE WHEN month(action_month) = 4 AND pix_movements.in_or_out = "pix_in" THEN pix.movements.amount END)-SUM(CASE WHEN month(action_month) = 4 THEN transfer_outs.amount END)-SUM(CASE WHEN month(action_month) = 4 AND pix_movements.in_or_out = "pix_out" THEN pix.movements.amount END), 0) Apr
ISNULL(SUM(CASE WHEN month(action_month) = 5 THEN transfer_ins.amount END)+SUM(CASE WHEN month(action_month) = 5 AND pix_movements.in_or_out = "pix_in" THEN pix.movements.amount END)-SUM(CASE WHEN month(action_month) = 5 THEN transfer_outs.amount END)-SUM(CASE WHEN month(action_month) = 5 AND pix_movements.in_or_out = "pix_out" THEN pix.movements.amount END), 0) May
ISNULL(SUM(CASE WHEN month(action_month) = 6 THEN transfer_ins.amount END)+SUM(CASE WHEN month(action_month) = 6 AND pix_movements.in_or_out = "pix_in" THEN pix.movements.amount END)-SUM(CASE WHEN month(action_month) = 6 THEN transfer_outs.amount END)-SUM(CASE WHEN month(action_month) = 6 AND pix_movements.in_or_out = "pix_out" THEN pix.movements.amount END), 0) Jun
ISNULL(SUM(CASE WHEN month(action_month) = 7 THEN transfer_ins.amount END)+SUM(CASE WHEN month(action_month) = 7 AND pix_movements.in_or_out = "pix_in" THEN pix.movements.amount END)-SUM(CASE WHEN month(action_month) = 7 THEN transfer_outs.amount END)-SUM(CASE WHEN month(action_month) = 7 AND pix_movements.in_or_out = "pix_out" THEN pix.movements.amount END), 0) Jul
ISNULL(SUM(CASE WHEN month(action_month) = 8 THEN transfer_ins.amount END)+SUM(CASE WHEN month(action_month) = 8 AND pix_movements.in_or_out = "pix_in" THEN pix.movements.amount END)-SUM(CASE WHEN month(action_month) = 8 THEN transfer_outs.amount END)-SUM(CASE WHEN month(action_month) = 8 AND pix_movements.in_or_out = "pix_out" THEN pix.movements.amount END), 0) Aug
ISNULL(SUM(CASE WHEN month(action_month) = 9 THEN transfer_ins.amount END)+SUM(CASE WHEN month(action_month) = 9 AND pix_movements.in_or_out = "pix_in" THEN pix.movements.amount END)-SUM(CASE WHEN month(action_month) = 9 THEN transfer_outs.amount END)-SUM(CASE WHEN month(action_month) = 9 AND pix_movements.in_or_out = "pix_out" THEN pix.movements.amount END), 0) Sep
ISNULL(SUM(CASE WHEN month(action_month) = 10 THEN transfer_ins.amount END)+SUM(CASE WHEN month(action_month) = 10 AND pix_movements.in_or_out = "pix_in" THEN pix.movements.amount END)-SUM(CASE WHEN month(action_month) = 10 THEN transfer_outs.amount END)-SUM(CASE WHEN month(action_month) = 10 AND pix_movements.in_or_out = "pix_out" THEN pix.movements.amount END), 0) Oct
ISNULL(SUM(CASE WHEN month(action_month) = 11 THEN transfer_ins.amount END)+SUM(CASE WHEN month(action_month) = 11 AND pix_movements.in_or_out = "pix_in" THEN pix.movements.amount END)-SUM(CASE WHEN month(action_month) = 11 THEN transfer_outs.amount END)-SUM(CASE WHEN month(action_month) = 11 AND pix_movements.in_or_out = "pix_out" THEN pix.movements.amount END), 0) Nov
ISNULL(SUM(CASE WHEN month(action_month) = 11 THEN transfer_ins.amount END)+SUM(CASE WHEN month(action_month) = 12 AND pix_movements.in_or_out = "pix_in" THEN pix.movements.amount END)-SUM(CASE WHEN month(action_month) = 12 THEN transfer_outs.amount END)-SUM(CASE WHEN month(action_month) = 12 AND pix_movements.in_or_out = "pix_out" THEN pix.movements.amount END), 0) Dic
FROM
accounts
INNER JOIN
pix_movemets ON accounts.account_id = pix_movements.account_id
INNER JOIN
transfer_outs ON accounts.account_id = transfer_outs.account_id
INNER JOIN
transfer_ins ON accounts.account_id = transfer_ins.account_id
GROUP BY
account_id
But I keep getting errors, I don't know how to do it.
I understand that I need to SUM the amount of the transfer_ins.amount + pix_movements.pix_amount where = pix_movements.in_or_out = "in". The result of each month I need to subtract with transfer_outs.amount + pix_movements.pix_amount where = pix.movements.in_or_out = "out" by account.
The tables are in the image below. I think that I need to use the tables: transfer_ins transfer_outs accounts pix_movements d_time d_year
Schema-Entity:
