Forum Discussion
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
- SivertSolemIron 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)
- rodgerkongIron Contributor
Are you sure this is a T-SQL script?