Forum Discussion

davids4500's avatar
davids4500
Copper Contributor
Apr 25, 2025

Changing dates on a sql select query to previous year

I want to be able look at the current years sales by month as in the current sql script.

Then I want to look at the same information for the previous year as well.

SELECT
    CASE EXTRACT(MONTH FROM t.transdate)
        WHEN 1 THEN 'Jan'
        WHEN 2 THEN 'Feb'
        WHEN 3 THEN 'Mar'
        WHEN 4 THEN 'Apr'
        WHEN 5 THEN 'May'
        WHEN 6 THEN 'Jun'
        WHEN 7 THEN 'Jul'
        WHEN 8 THEN 'Aug'
        WHEN 9 THEN 'Sep'
        WHEN 10 THEN 'Oct'
        WHEN 11 THEN 'Nov'
        WHEN 12 THEN 'Dec'
    END AS "Month",

    SUM(sm.quantity * sm.exsell) AS "Monthly Sales",
    SUM(sm.quantity * sm.cost) AS "Sales Cost",
    SUM(sm.quantity * sm.exsell) - SUM(sm.quantity * sm.cost) AS "Gross Profit"
FROM stockmovement sm
JOIN transactions t ON t.transref = sm.transref
JOIN item i ON i.itemref = sm.itemref
JOIN maker m ON m.makerref = i.makerref
WHERE t.transource = 'D'
AND sm.itemref <> '100037'
AND sm.itemref <> '176176'


AND t.transdate < CAST(EXTRACT(YEAR FROM DATEADD(1 MONTH TO CURRENT_DATE)) || '-' || EXTRACT(MONTH FROM DATEADD(1 MONTH TO CURRENT_DATE)) || '-' || '01' AS DATE)
AND t.transdate >= CAST(EXTRACT(YEAR FROM DATEADD(-1 YEAR TO CURRENT_DATE)) || '-' || EXTRACT(MONTH FROM DATEADD(-1 YEAR TO CURRENT_DATE)) || '-' || '01' AS DATE)


GROUP BY EXTRACT(MONTH FROM t.transdate)
ORDER BY EXTRACT(MONTH FROM t.transdate)

2 Replies

  • SivertSolem's avatar
    SivertSolem
    Iron Contributor

    This looks like Oracle flavored SQL.
    Microsoft T-SQL does not use EXTRACT like this.

     

    I suggest you look at this block, and I do believe you'll be able to intuit yourself how to get last years result.

    AND t.transdate < CAST(EXTRACT(YEAR FROM DATEADD(1 MONTH TO CURRENT_DATE)) || '-' || EXTRACT(MONTH FROM DATEADD(1 MONTH TO CURRENT_DATE)) || '-' || '01' AS DATE)
    AND t.transdate >= CAST(EXTRACT(YEAR FROM DATEADD(-1 YEAR TO CURRENT_DATE)) || '-' || EXTRACT(MONTH FROM DATEADD(-1 YEAR TO CURRENT_DATE)) || '-' || '01' AS DATE)

     

Resources