I wish to return the data for Feb 2025, Jan 2025 and Dec 2024 on a report using SQL Server.
Please advise where I have gone wrong:
This is what I have and a demo table:
CREATE TABLE myTable(id int, area varchar(20), myDate DATE, Qty
int);
INSERT INTO myTable ([id],[area],[myDate],[Qty])
VALUES
(1,'Shipping','2024-12-24',3),
(2,'IT','2024-12-15', 5),
(3,'Shipping','2024-12-26',6),
(4,'HR','2024-12-28',2),
(5,'IT','2024-12-30',1),
(6,'Shipping','2025-01-01',2),
(7,'Shipping','2025-01-03',1),
(8,'Shipping','2025-02-01',2),
(9,'Shipping','2024-11-28',1);
When I use TOP and ORDER BY ASC:
SELECT TOP(3) YEAR(myDate) [Year], MONTH(myDate) [Month],
DATENAME(MONTH,myDate) [Month Name], Sum(Qty) [Total]
FROM myTable WHERE area = 'Shipping'
GROUP BY YEAR(myDate), MONTH(myDate),
DATENAME(MONTH, myDate)
ORDER BY 1,2 ASC
I get this:
Year Month Month Name Total
2024 11 November 1
2024 12 December 9
2025 1 January 4
When I use TOP and ORDER BY DESC like this:
SELECT TOP(3) YEAR(myDate) [Year], MONTH(myDate) [Month],
DATENAME(MONTH,myDate) [Month Name], Sum(Qty) [Total]
FROM myTable WHERE area = 'Shipping'
GROUP BY YEAR(myDate), MONTH(myDate),
DATENAME(MONTH, myDate)
ORDER BY 1,2 DESC
I get this:
Year Month Month Name Total
2024 12 December 9
2024 11 November 1
2025 2 February 2
Desired output like so:
Year Month Month Name Total
2024 12 December 9
2025 1 January 4
2025 2 February 2
I don't want it hardcoded for those months, I will need the three most recent months every month.
Why not pre-filter in the WHERE clause?
SELECT YEAR(myDate) [Year], MONTH(myDate) [Month],
DATENAME(MONTH,myDate) [Month Name], Sum(Qty) [Total]
FROM myTable
WHERE area = 'Shipping'
AND myDate >= '2024-12-01'
GROUP BY YEAR(myDate), MONTH(myDate),
DATENAME(MONTH, myDate)
ORDER BY [Year], [Month] DESC
This is better than TOP 3 because the TOP 3 strategy requires computing groups for the entire table before it can decide which rows to use. This way, you can decide which rows matter, likely with the help of an index, and then only need to compute those groups.
The trick is if you always want to base this on the current date, since you want to see February data even though it is not yet February. Clearly we want the date to tick into the next month a little early. I suspect you want the most recent month for which you have data, in which case you can do this with a nested SELECT and the EOMonth() function:
SELECT YEAR(myDate) [Year], MONTH(myDate) [Month],
DATENAME(MONTH,myDate) [Month Name], Sum(Qty) [Total]
FROM myTable
WHERE area = 'Shipping'
AND myDate > (SELECT dateadd(month, -3, EOMonth(MAX(myDate))) FROM myTable)
GROUP BY YEAR(myDate), MONTH(myDate), DATENAME(MONTH, myDate)
ORDER BY [YEAR] DESC, [Month] DESC;
Finally, it's poor practice to order by column ordinals like that.