codbex / codbex-olympus

Enterprise Resource Planing
Eclipse Public License 2.0
0 stars 0 forks source link

[Hestia] Add report for monthly VAT #11

Open MinaDoncheva opened 4 months ago

MinaDoncheva commented 4 months ago

Add report using this SQL query:

SELECT 
    SUM(VAT) AS CASHFLOW_VAT,
    DATE_TRUNC('month', TRANSACTION_DATE) AS CASHFLOW_DATE
FROM (
    SELECT 
        SALESINVOICE_DATE AS TRANSACTION_DATE,
        SALESINVOICE_VAT AS VAT
    FROM CODBEX_SALESINVOICE
    UNION ALL
    SELECT 
        PURCHASEINVOICE_DATE AS TRANSACTION_DATE,
        -PURCHASEINVOICE_VAT AS VAT
    FROM CODBEX_PURCHASEINVOICE
) AS CombinedData
GROUP BY DATE_TRUNC('month', TRANSACTION_DATE)
ORDER BY CASHFLOW_DATE DESC;