Che, If you are going to use isNull in this case you will need an ugly case statement inside your query. Even then, I'm not sure it's possible. You can wrap it in a stored proc and do some looping to return a temp table. If all you are doing is displaying this data in CF I would just loop from 0 to 23 and tease out the values rather than try to insert "placeholders" into the data.
-Mark -----Original Message----- From: Che Vilnonis [mailto:[EMAIL PROTECTED] Sent: Friday, August 26, 2005 7:39 AM To: CF-Talk Subject: RE: Quick Query Question...[hopefully] Dave, below is my query...how would I make the query work with isnull() on SQL Server 2000? Thanks, Che. SELECT Datepart(hh,OrderDate) AS Hour, Count(OrderDate) AS TotalOrdersPerHour FROM Orders WHERE Status = 'SHIPPED' AND (OrderDate BETWEEN '8/1/2005' AND '8/31/2005 23:59:59') GROUP BY Datepart(hh,OrderDate), Datepart(hh,OrderDate) ORDER BY Datepart(hh,OrderDate) ASC Che, You can use isnull() in SQL Server and nvl() in Oracle. I don't know which DB you're using. Dave -----Original Message----- If have a query that returns a 24 hour breakdown of orders by the hour on a monthly basis. (see below). As you can see, Hour 5 & 6 are missing because there are no values to return. What would be the easiest way to add Hour 5 and Hour 6 to the query's recordset with a corresponding value of zero for each hour? The trick is, from month to month, the hours that have no values might change. Any idee-ers? Thanks, Che. HOUR TOTALORDERSPERHOUR 0 5 1 5 2 4 3 2 4 2 7 3 8 2 9 10 10 7 11 7 12 3 13 5 14 11 15 10 16 6 17 13 18 7 19 13 20 9 21 16 22 11 23 14 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:216473 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
