Sort Data from table by month

Forum Moderators: open

Message Too Old, No Replies

Sort Data from table by month

Even when there is no data for that month

Red_Eye

4:44 pm on Jan 5, 2009 (gmt 0)

I have an orders table, I want to get a monthly totals for this table. But some months might not have any orders in so I want it to return 0 for these months.

Jan 0
feb 10
Mar 20
Apr 0
May 50
Jun 10
Jul 10
Aug 0
Sep 0
Oct 9
Nov 33
Dec 22

The Query I have at the moment is

SELECT MONTH(Datecomplete) AS MONTHNO, DATENAME(month,DateComplete) AS MonthComplete, SUM(Price*QTY) AS Total
FROM OrderParts INNER JOIN
Orders ON OrderParts.OrderID = Orders.OrderID
WHERE AND YEAR(DateComplete)=@Year
GROUP BY MONTH(Datecomplete),DATENAME(month,DateComplete)
ORDER BY MONTHNO

Any help would be appriciated

coopster

5:28 pm on Jan 5, 2009 (gmt 0)

There are quite a few options ... one may be that you can modify your query by using something like a UNION with each month number coded. Another may be that you can use your application logic to show a zero sum total for any months with no corresponding totals.

Red_Eye

8:34 pm on Jan 5, 2009 (gmt 0)

Thanks for getting back to me. I would quite like to do it in the database rather than the application. I had thought of adding a table call months with the following format

MonthID Name
1 jan
2 feb
3 mar

etc.

Then use an outer join to include all rows from the month table and a case select rule for any months with no data.

could you give me an example of how to code a union with the month numbers hard coded.

Thanks again for your help