WITH
tbl_start_balance AS (
SELECT 1000868.31::numeric(28,2) AS dbl_start_balance
),
tbl_raw AS (
SELECT
CAST(v.operation_id AS numeric(15,0)) AS id_operation,
CAST(v.char_type AS int) * -1 AS int_oper_type,
CAST(v.oper_date AS timestamp) AS date_oper_date,
CAST(v.qty AS numeric(28,2)) AS dbl_qty,
CAST(v.indatetime AS timestamp) AS dttm_in,
CAST(v.rest AS numeric(28,2)) AS dbl_rest_orig
FROM (VALUES
(33581974000, 1, '2021-08-20 00:00:00', 1000011.0000000000, '2021-08-20 13:10:25.700', 0),
(33582024800, -1, '2021-08-20 00:00:00', 1000011.1100000000, '2021-08-20 13:51:08.903', 0),
(33582208700, 1, '2021-08-20 00:00:00', 825000.1500000000, '2021-08-20 15:33:38.776', 0),
(33582407110, -1, '2021-08-20 00:00:00', 825000.0000000000, '2021-08-20 16:29:39.980', 0),
(33582408610, 1, '2021-08-20 00:00:00', 1000011.0000000000, '2021-08-20 16:34:53.183', 0),
(33582419620, -1, '2021-08-20 00:00:00', 1000011.0000000000, '2021-08-20 17:09:24.250', 0),
(33582519290, 1, '2021-08-20 00:00:00', 15000.0000000000, '2021-08-20 19:57:20.546', 0)
) AS v(operation_id, char_type, oper_date, qty, indatetime, rest)
),
tbl_expected AS (
SELECT
CAST(v.operation_id AS numeric(15,0)) AS id_operation,
CAST(v.char_type AS int) * -1 AS int_oper_type,
CAST(v.oper_date AS timestamp) AS date_oper_date,
CAST(v.qty AS numeric(28,2)) AS dbl_qty,
CAST(v.indatetime AS timestamp) AS dttm_in,
CAST(v.rest AS numeric(28,2)) AS dbl_rest_expected
FROM (VALUES
(33581974000, -1, '2021-08-20 00:00:00', 1000011.00, '2021-08-20 13:10:25.7', 857.31),
(33582024800, 1, '2021-08-20 00:00:00', 1000011.11, '2021-08-20 13:51:08.903', 1000868.42),
(33582208700, -1, '2021-08-20 00:00:00', 825000.15, '2021-08-20 15:33:38.776', 175868.27),
(33582407110, 1, '2021-08-20 00:00:00', 825000.00, '2021-08-20 16:29:39.98', 1000868.27),
(33582408610, -1, '2021-08-20 00:00:00', 1000011.00, '2021-08-20 16:34:53.183', 857.27),
(33582419620, 1, '2021-08-20 00:00:00', 1000011.00, '2021-08-20 17:09:24.25', 1000868.27),
(33582519290, -1, '2021-08-20 00:00:00', 15000.00, '2021-08-20 19:57:20.546', 985868.27)
) AS v(operation_id, char_type, oper_date, qty, indatetime, rest)
ORDER BY dttm_in
),
tbl_calc_raw AS (
SELECT
t1.id_operation,
COALESCE(SUM(t2.int_oper_type * t2.dbl_qty), 0) + (t1.int_oper_type * t1.dbl_qty) AS dbl_rest_after_operation
FROM tbl_raw t1
LEFT JOIN tbl_raw t2
ON (
t1.dttm_in > t2.dttm_in
--OR (t1.dttm_in = t2.dttm_in AND t1.id_operation >= t2.id_operation)
)
GROUP BY t1.id_operation, t1.int_oper_type, t1.dbl_qty
),
tbl_calc AS (
SELECT
id_operation,
dbl_rest_after_operation + (SELECT dbl_start_balance FROM tbl_start_balance) AS dbl_rest_after_operation
FROM tbl_calc_raw
),
tbl_compare AS (
SELECT
t1.id_operation,
t1.dttm_in,
t1.dbl_rest_expected,
t2.dbl_rest_after_operation AS dbl_rest_actual,
(t2.dbl_rest_after_operation - t1.dbl_rest_expected) AS dbl_rest_diff
FROM tbl_expected t1
LEFT JOIN tbl_calc t2
ON t1.id_operation = t2.id_operation
ORDER BY dttm_in
),
tbl_final AS (
SELECT
id_operation,
dttm_in,
dbl_rest_expected,
dbl_rest_actual,
dbl_rest_diff
FROM tbl_compare
ORDER BY dttm_in
)
SELECT * FROM tbl_compare
Telegram: DB developers channel