Asset and Depreciation Report by Month.sql

SELECT
    a.asset_category AS "Category:Data",
    a.asset_name AS "Asset Name:Data",
    a.name AS "Asset No.:Link/Asset",
    a.purchase_date AS "Purchase Date:Date",
    
    ROUND(a.gross_purchase_amount + IFNULL(ava.total_difference, 0), 2) AS "Gross Asset Amount:Float",    
 
    SUM(CASE WHEN MONTH(ds.schedule_date) = 1 AND YEAR(ds.schedule_date) = YEAR(%(cutoff_date)s) THEN ds.depreciation_amount ELSE 0 END) AS "January:Float",
    SUM(CASE WHEN MONTH(ds.schedule_date) = 2 AND YEAR(ds.schedule_date) = YEAR(%(cutoff_date)s) THEN ds.depreciation_amount ELSE 0 END) AS "February:Float",
    SUM(CASE WHEN MONTH(ds.schedule_date) = 3 AND YEAR(ds.schedule_date) = YEAR(%(cutoff_date)s) THEN ds.depreciation_amount ELSE 0 END) AS "March:Float",
    SUM(CASE WHEN MONTH(ds.schedule_date) = 4 AND YEAR(ds.schedule_date) = YEAR(%(cutoff_date)s) THEN ds.depreciation_amount ELSE 0 END) AS "April:Float",
    SUM(CASE WHEN MONTH(ds.schedule_date) = 5 AND YEAR(ds.schedule_date) = YEAR(%(cutoff_date)s) THEN ds.depreciation_amount ELSE 0 END) AS "May:Float",
    SUM(CASE WHEN MONTH(ds.schedule_date) = 6 AND YEAR(ds.schedule_date) = YEAR(%(cutoff_date)s) THEN ds.depreciation_amount ELSE 0 END) AS "June:Float",
    SUM(CASE WHEN MONTH(ds.schedule_date) = 7 AND YEAR(ds.schedule_date) = YEAR(%(cutoff_date)s) THEN ds.depreciation_amount ELSE 0 END) AS "July:Float",
    SUM(CASE WHEN MONTH(ds.schedule_date) = 8 AND YEAR(ds.schedule_date) = YEAR(%(cutoff_date)s) THEN ds.depreciation_amount ELSE 0 END) AS "August:Float",
    SUM(CASE WHEN MONTH(ds.schedule_date) = 9 AND YEAR(ds.schedule_date) = YEAR(%(cutoff_date)s) THEN ds.depreciation_amount ELSE 0 END) AS "September:Float",
    SUM(CASE WHEN MONTH(ds.schedule_date) = 10 AND YEAR(ds.schedule_date) = YEAR(%(cutoff_date)s) THEN ds.depreciation_amount ELSE 0 END) AS "October:Float",
    SUM(CASE WHEN MONTH(ds.schedule_date) = 11 AND YEAR(ds.schedule_date) = YEAR(%(cutoff_date)s) THEN ds.depreciation_amount ELSE 0 END) AS "November:Float",
    SUM(CASE WHEN MONTH(ds.schedule_date) = 12 AND YEAR(ds.schedule_date) = YEAR(%(cutoff_date)s) THEN ds.depreciation_amount ELSE 0 END) AS "December:Float",
    
    --IFNULL(SUM(ds.depreciation_amount), 0) AS "Depreciation Amount:Float",
    ROUND((a.gross_purchase_amount + IFNULL(ava.total_difference, 0) - IFNULL(SUM(ds.depreciation_amount), 0)), 2) AS "Net Book Value:Float"
FROM
    `tabAsset` a
LEFT JOIN
    `tabAsset Depreciation Schedule` ads ON a.name = ads.asset
LEFT JOIN
    `tabDepreciation Schedule` ds ON ads.name = ds.parent AND ds.schedule_date <= %(cutoff_date)s AND ds.journal_entry IS NOT NULL
LEFT JOIN
    (SELECT
        asset,
        SUM(-difference_amount) AS total_difference
     FROM
        `tabAsset Value Adjustment`
     WHERE
        date <= %(cutoff_date)s AND docstatus = 1
     GROUP BY
        asset) ava ON a.name = ava.asset
WHERE
    a.purchase_date <= %(cutoff_date)s AND a.docstatus = 1
GROUP BY
    a.name
ORDER BY
    a.purchase_date