Hello Will, I'd advise building yourself a calendar table, these are always a useful facility in any application where you're going to be doing report generation, I've attached the SQL script which creates and populates your calendar table, this basically creates a record for each date in a range and breaks into different date parts, then for your query below we select the dates from the calendar table, ensuring we get a record for each group, be it day, month, year etc and then we'll LEFT OUTER JOIN the order table on that date, this ensures we get a row for every date in the range and it'll just return NULL for the months where orders don't exist.
SQL CREATE Script: http://pastebin.com/m57ba4578 SQL INERT Script: http://pastebin.com/m65d6ee0f That script will create a table and insert records for the next 10 years, change the dates on the insert script to suit your own requirements. The script for your main query would then look something like this: http://pastebin.com/m1e234813 I have no way of testing that script, its working off the info you gave, if it doesn't work then let me know your table structure and some example data and we'll bash something together. Hope that helps you on your way. Rob -----Original Message----- From: Will Tomlinson [mailto:w...@wtomlinson.com] Sent: 25 March 2009 11:26 To: cf-talk Subject: SQL server - Order totals by month, even without month data I have your typical tblorders. I'm trying to query it to get order totals by month, and I'm outputting them in <cfchart> All goes fine, except in 2009. We have no order data for April-December of course, so the query returns just three rows (Jan, Feb, March). I need it to return all 12 months, with 0 for the months with no orders (April-Dec) I've played with this query and can't get anything to work for me. Any ideas? Thanks! Will select year(orderdate) as orderYear, month(orderdate) as monthOrdered, sum(ordertotal) as totalOrders, dateName(month, orderdate) as monthName from tblorders where orderpaid = 1 and year(orderdate) = 2009 group by year(orderdate), month(orderdate), dateName(month, orderdate) order by year(orderdate) asc, month(orderdate) asc ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320912 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
