Welcome to WebmasterWorld Guest from 54.145.23.244

Forum Moderators: open

Message Too Old, No Replies

Sort Data from table by month

Even when there is no data for that month

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

Full Member

10+ Year Member

joined:Sept 24, 2002
posts: 214
votes: 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

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

Administrator

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 31, 2003
posts:12537
votes: 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.
8:34 pm on Jan 5, 2009 (gmt 0)

Full Member

10+ Year Member

joined:Sept 24, 2002
posts: 214
votes: 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

 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members