TAAS

Solutions

1) GUI Changes
-Period need MM/YYYY only
-Add Currency - Base1(***) default, Not need BASE2
-Summary List to PLB Report
-List Column - Issue Date change to Fortnight
2) SQL
SELECT dat_period_start,
    dat_period_end,
    SUM(dbl_fare_sale) AS dbl_fare_sale,
    SUM(dbl_tax_sale) AS dbl_tax_sale,
    SUM(dbl_fare_refund) AS dbl_fare_refund,
    SUM(dbl_tax_refund) AS dbl_tax_refund
FROM (
    SELECT sudn.dat_issue AS dat_issue_or_cancel,
        CASE WHEN TO_CHAR(sudn.dat_issue, 'DD')::INT <= 15 THEN DATE_TRUNC('month', sudn.dat_issue)::date ELSE (DATE_TRUNC('month', sudn.dat_issue) + interval '15 day')::date END AS dat_period_start,
        CASE WHEN TO_CHAR(sudn.dat_issue, 'DD')::INT <= 15 THEN (DATE_TRUNC('month', sudn.dat_issue) + interval '14 day')::date ELSE (DATE_TRUNC('month', sudn.dat_issue) + interval '1 month - 1 day')::date END AS dat_period_end,
        sudf.dbl_fare AS dbl_fare_sale,
        sudf.dbl_tax AS dbl_tax_sale,
        0.00 AS dbl_fare_refund,
        0.00 AS dbl_tax_refund
    FROM service.tbl_supp_doc_no sudn
        LEFT JOIN service.tbl_supp_doc_fare sudf
            ON sudn.fk_issue_base_fare_id = sudf.pk_supp_doc_fare_id --*1
    WHERE sudn.sin_last_action != 0 
        AND sudn.sin_sys_sale_side_status > 0

    UNION ALL

    SELECT sudn.dat_cancel AS dat_issue_or_cancel,
        CASE WHEN TO_CHAR(sudn.dat_issue, 'DD')::INT <= 15 THEN DATE_TRUNC('month', sudn.dat_cancel)::date ELSE (DATE_TRUNC('month', sudn.dat_cancel) + interval '15 day')::date END AS dat_period_start,
        CASE WHEN TO_CHAR(sudn.dat_issue, 'DD')::INT <= 15 THEN (DATE_TRUNC('month', sudn.dat_cancel) + interval '14 day')::date ELSE (DATE_TRUNC('month', sudn.dat_cancel) + interval '1 month - 1 day')::date END AS dat_period_end,
        0.00 AS dbl_fare_sale,
        0.00 AS dbl_tax_sale,
        sudf.dbl_fare AS dbl_fare_refund,
        sudf.dbl_tax AS dbl_tax_refund
    FROM service.tbl_supp_doc_no sudn
        LEFT JOIN service.tbl_supp_doc_fare sudf
            ON sudn.fk_cancel_base_fare_id = sudf.pk_supp_doc_fare_id --*2
    WHERE sudn.sin_last_action != 0 
        AND sin_sys_refund_side_status > 0) AS tmp
GROUP BY dat_period_start, dat_period_end

*Fortnight Column value is dat_period_start - dat_period_end -> Format date