TAAS

Solutions

1) SQL
SELECT fk_main_ledger_id, 
    vhr_ledg_currency,

    (SUM(CASE WHEN dat_transaction < '2022-12-01' THEN dbl_base_cur_debit ELSE 0 END)) AS dbl_base_cur_ob_debit,
    (SUM(CASE WHEN dat_transaction < '2022-12-01' THEN dbl_base_cur_credit ELSE 0 END)) AS dbl_base_cur_ob_credit,
    (SUM(CASE WHEN dat_transaction < '2022-12-01' THEN dbl_ledg_cur_debit ELSE 0 END)) AS dbl_ledg_cur_ob_debit,
    (SUM(CASE WHEN dat_transaction < '2022-12-01' THEN dbl_ledg_cur_credit ELSE 0 END)) AS dbl_ledg_cur_ob_credit,

    (SUM(CASE WHEN dat_transaction BETWEEN '2022-12-01' AND '2022-12-31' THEN dbl_base_cur_debit ELSE 0 END)) AS dbl_base_cur_current_debit,
    (SUM(CASE WHEN dat_transaction BETWEEN '2022-12-01' AND '2022-12-31' THEN dbl_base_cur_credit ELSE 0 END)) AS dbl_base_cur_current_credit,
    (SUM(CASE WHEN dat_transaction BETWEEN '2022-12-01' AND '2022-12-31' THEN dbl_ledg_cur_debit ELSE 0 END)) AS dbl_ledg_cur_current_debit,
    (SUM(CASE WHEN dat_transaction BETWEEN '2022-12-01' AND '2022-12-31' THEN dbl_ledg_cur_credit ELSE 0 END)) AS dbl_ledg_cur_current_credit,

    (SUM(CASE WHEN dat_transaction > '2022-12-31' THEN dbl_base_cur_debit ELSE 0 END)) AS dbl_base_cur_after_current_debit,
    (SUM(CASE WHEN dat_transaction > '2022-12-31' THEN dbl_base_cur_credit ELSE 0 END)) AS dbl_base_cur_after_current_credit,
    (SUM(CASE WHEN dat_transaction > '2022-12-31' THEN dbl_ledg_cur_debit ELSE 0 END)) AS dbl_ledg_cur_after_current_debit,
    (SUM(CASE WHEN dat_transaction > '2022-12-31' THEN dbl_ledg_cur_credit ELSE 0 END)) AS dbl_ledg_cur_after_current_credit

FROM (
    SELECT fk_main_ledger_id,
        vhr_ledg_currency,
        dat_transaction,
        dbl_base_cur_debit,
        dbl_base_cur_credit,
        dbl_ledg_cur_debit,
        dbl_ledg_cur_credit
    FROM transaction.tbl_transaction
    WHERE fk_ledger_id = 1
        AND fk_branch_id = 1
        AND fk_department_id = 1) AS tmp
GROUP BY fk_main_ledger_id, vhr_ledg_currency;
-OB -> dbl_ledg_cur_ob_debit - dbl_ledg_cur_ob_credit, If > 0 then Dr, else Cr
-Debit -> dbl_ledg_cur_current_debit
-Credit -> dbl_ledg_cur_current_credit
-After To Date Balance -> dbl_ledg_cur_after_current_debit - dbl_ledg_cur_after_current_credit, If > 0 then Dr, else Cr
Closing Balance -> OB + (Debit-Credit) + After To Date Balance, If > 0 then Dr, else Cr

Base Currency Balance -> Total all recods -> (dbl_base_cur_ob_debit-dbl_base_cur_ob_credit) + (dbl_base_cur_current_debit-dbl_base_cur_current_credit) + (dbl_base_cur_after_current_debit-dbl_base_cur_after_current_credit), If > 0 then Dr, else Cr

TESTING DONE

TESTING DONE