Thanks Mark. Currently, I'm using CF to handle the problem since so few rows are returned. That said, what I wasn't sure about was how to create/code what I guess would be a 'virtual' table with the hours 0-23 in order to do a Join. Any idea-ers?
Regards, Che. -----Original Message----- From: Mark A Kruger [mailto:[EMAIL PROTECTED] Sent: Friday, August 26, 2005 10:52 AM To: CF-Talk Subject: RE: Quick Query Question...[hopefully] This would give you all the hours with zeros where no hours existed. Purely theoretical and untested :) I'm sure Jochem will tell us if I'm right or give me the whammy (ha). -mark table named "hourly" with a column called hour - 0 through 23 in it. SELECT h.hour, Count(o.OrderDate) ASTotalOrdersPerHour FROM hourly h LEFT JOIN Orders o ON h.hour = Datepart(hh, o.OrderDate) WHERE Status = 'SHIPPED' AND (OrderDate BETWEEN '8/1/2005' AND '8/31/2005 23:59:59') GROUP BY hour, count(o.orderDate) ORDER BY hour ASC -----Original Message----- From: Che Vilnonis [mailto:[EMAIL PROTECTED] Sent: Friday, August 26, 2005 8:04 AM To: CF-Talk Subject: RE: Quick Query Question...[hopefully] Huh? Ya got me with that one Mark. Care to explain in detail? Also, would it be easier to use CF to insert the missing hours of the day and values of zero (if yes, what would be the easist)? I hate to use CF when I might be able to use the db... but were only talking about 24 rows of data. ~Che -----Original Message----- Che, try this, create a table 1 row and the numbers 0 through 23 in them, then do a left join to your group by query :) -Mark -----Original Message----- 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 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:216661 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
