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:216655
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
