TAAS

Add Eraser On Grid

Solutions:-

SELECT fk_branch_id,
        TO_CHAR(dat_transaction, 'Month-YYYY') AS str_month,
        TO_CHAR(dat_transaction, 'YYYY') AS str_year,
        fk_ledger_id,
        vhr_ledg_currency,
        SUM(dbl_ledg_cur_debit) AS dbl_debit,
        SUM(dbl_ledg_cur_credit) AS dbl_credit
    FROM transaction.tbl_transaction
    WHERE dat_transaction BETWEEN '2023-01-01' AND '2023-12-31'
        AND vhr_ledg_currency IN ('QAR', 'INR', 'SAR', 'USD')
        --AND fk_ledger_id = 1 OR sin_ac_category = ??(Account label drop down based)
        --AND fk_branch_id = 1
        --AND fk_department_id = 1
    GROUP BY fk_branch_id, fk_ledger_id, str_month, str_year, vhr_ledg_currency
    ORDER BY vhr_ledg_currency
Logic:-
- Model
MdlCurrencyCopReportData {
  intSlNo: number = 0;
  binBranchId: number= 0;
  binAccountId: number= 0;
  strYear: string = "";
  strMonth: string = "";
  strDisplayValue:string = "";
  objCurrencyData: any = {};
}
objCurrencyData:any = {};
for strCurrency of ['QAR', 'INR', 'SAR', 'USD'] {
   objCurrencyData['dbl'+strCurrency+'Debit'] = 0.00;
   objCurrencyData['dbl'+strCurrency+'Credit'] = 0.00;
}

objCurrencyCopReportData:any = {
'BRANCH' : lstMdlCurrencyCopReportData : MdlCurrencyCopReportData [] = [],
'ACCOUNT' : lstMdlCurrencyCopReportData : MdlCurrencyCopReportData [] = [],
'MONTH' : lstMdlCurrencyCopReportData : MdlCurrencyCopReportData [] = [],
'YEAR' : lstMdlCurrencyCopReportData : MdlCurrencyCopReportData [] = [],
}
*Loop sql recotd
strCurrencyDrString = "dbl"+vhr_ledg_currency+'Debit';
strCurrencyCrString = "dbl"+vhr_ledg_currency+'Credit';
// Branch
objBranch = objCurrencyCopReportData['BRANCH'].find -> MdlCurrencyCopReportData.binBranchId = fk_branch_id
if objBranch  == null {
  objBranch   = new MdlCurrencyCopReportData ();
  objBranch.binBranchId = fk_branch_id
  objBranch.strDisplayValue = based on fk_branch_id -> fill the field in TS
  objBranch.objCurrencyData   = copy of objCurrencyData
  objCurrencyCopReportData['BRANCH'].Add(objBranch);
}
objBranch.objCurrencyData[strCurrencyDrString] += dbl_debit;
objBranch.objCurrencyData[strCurrencyCrString ] += dbl_credit;
// Ledger
....
// Month
.....
// Year
....

NOT USE

SELECT
    fk_branch_id,
    str_month,
    str_year,
    fk_ledger_id,
    MAX(CASE WHEN vhr_ledg_currency = 'QAR' THEN dbl_debit ELSE 0 END) AS dbl_debit_QAR,
    MAX(CASE WHEN vhr_ledg_currency = 'QAR' THEN dbl_credit ELSE 0 END) AS dbl_credit_QAR,
    MAX(CASE WHEN vhr_ledg_currency = 'INR' THEN dbl_debit ELSE 0 END) AS dbl_debit_INR,
    MAX(CASE WHEN vhr_ledg_currency = 'INR' THEN dbl_credit ELSE 0 END) AS dbl_credit_INR,
    MAX(CASE WHEN vhr_ledg_currency = 'SAR' THEN dbl_debit ELSE 0 END) AS dbl_debit_SAR,
    MAX(CASE WHEN vhr_ledg_currency = 'SAR' THEN dbl_credit ELSE 0 END) AS dbl_credit_SAR,
    MAX(CASE WHEN vhr_ledg_currency = 'USD' THEN dbl_debit ELSE 0 END) AS dbl_debit_USD,
    MAX(CASE WHEN vhr_ledg_currency = 'USD' THEN dbl_credit ELSE 0 END) AS dbl_credit_USD
FROM (
    -- Your original query here
    SELECT
        fk_branch_id,
        TO_CHAR(dat_transaction, 'MM-YYYY') AS str_month,
        TO_CHAR(dat_transaction, 'YYYY') AS str_year,
        fk_ledger_id,
        vhr_ledg_currency,
        SUM(dbl_ledg_cur_debit) AS dbl_debit,
        SUM(dbl_ledg_cur_credit) AS dbl_credit
    FROM transaction.tbl_transaction
    WHERE dat_transaction BETWEEN '2023-01-01' AND '2023-12-31'
        AND vhr_ledg_currency IN ('QAR', 'INR', 'SAR', 'USD')
    GROUP BY fk_branch_id, fk_ledger_id, str_month, str_year, vhr_ledg_currency
) AS subquery
GROUP BY fk_branch_id, str_month, str_year, fk_ledger_id
ORDER BY fk_branch_id, str_year, str_month, fk_ledger_id;

TESTING DONE

TESTING DONE