RE: Quick Query Question...[hopefully]
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 FROMhourly 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 FROMOrders 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
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 FROMhourly 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 FROMOrders 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
Re: Quick Query Question...[hopefully]
Actually what Mark Kruger said would probably work best in this situation 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 What he's saying is create a table called Hours with column row called hour add 23 rows (0-23) and then left join your query to that query. select * from foo, goo where foo.hour = goo.hour(+) That will return all the hours from the hour table. Not complex at all. On 8/26/05, Che Vilnonis <[EMAIL PROTECTED]> wrote: > works well. thanks so much. > > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Friday, August 26, 2005 10:07 AM > To: CF-Talk > Subject: RE: Quick Query Question...[hopefully] > > > There's probably a number of ways to do it. Mine may or may not be the most > efficient, but it gets the job done. > > . do your query here...query name="query" (or whatever) > > > > > Hour > Order Qty > > > > > > #thishour# > >#query.TotalOrderPerHour[hit]#0 > > > > > -Original Message- > From: Che Vilnonis [mailto:[EMAIL PROTECTED] > Sent: Friday, August 26, 2005 9:16 AM > To: CF-Talk > Subject: RE: Quick Query Question...[hopefully] > > > Yeah... I could use the help. I have an idea... but its probably NOT the > best or most efficient way to do it in CF. Enlighten me, por favor! > ~Che > > > ** > The information contained in this message, including attachments, may > contain > privileged or confidential information that is intended to be delivered only > to the > person identified above. If you are not the intended recipient, or the > person > responsible for delivering this message to the intended recipient, ALLTEL > requests > that you immediately notify the sender and asks that you do not read the > message or its > attachments, and that you delete them without copying or sending them to > anyone else. > > > > > ~| 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:216506 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
RE: Quick Query Question...[hopefully]
works well. thanks so much. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, August 26, 2005 10:07 AM To: CF-Talk Subject: RE: Quick Query Question...[hopefully] There's probably a number of ways to do it. Mine may or may not be the most efficient, but it gets the job done. . do your query here...query name="query" (or whatever) Hour Order Qty #thishour# #query.TotalOrderPerHour[hit]#0 -Original Message- From: Che Vilnonis [mailto:[EMAIL PROTECTED] Sent: Friday, August 26, 2005 9:16 AM To: CF-Talk Subject: RE: Quick Query Question...[hopefully] Yeah... I could use the help. I have an idea... but its probably NOT the best or most efficient way to do it in CF. Enlighten me, por favor! ~Che ** The information contained in this message, including attachments, may contain privileged or confidential information that is intended to be delivered only to the person identified above. If you are not the intended recipient, or the person responsible for delivering this message to the intended recipient, ALLTEL requests that you immediately notify the sender and asks that you do not read the message or its attachments, and that you delete them without copying or sending them to anyone else. ~| 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:216488 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=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Quick Query Question...[hopefully]
There's probably a number of ways to do it. Mine may or may not be the most efficient, but it gets the job done. do your query here...query name="query" (or whatever) Hour Order Qty #thishour# #query.TotalOrderPerHour[hit]#0 -Original Message- From: Che Vilnonis [mailto:[EMAIL PROTECTED] Sent: Friday, August 26, 2005 9:16 AM To: CF-Talk Subject: RE: Quick Query Question...[hopefully] Yeah... I could use the help. I have an idea... but its probably NOT the best or most efficient way to do it in CF. Enlighten me, por favor! ~Che ** The information contained in this message, including attachments, may contain privileged or confidential information that is intended to be delivered only to the person identified above. If you are not the intended recipient, or the person responsible for delivering this message to the intended recipient, ALLTEL requests that you immediately notify the sender and asks that you do not read the message or its attachments, and that you delete them without copying or sending them to anyone else. ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:216486 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
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 FROMOrders 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 ~| 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:216477 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
RE: Quick Query Question...[hopefully]
Yeah... I could use the help. I have an idea... but its probably NOT the best or most efficient way to do it in CF. Enlighten me, por favor! ~Che -Original Message- Che, I agree with Mark. Anything you do in the DB is going to be more complex than in CF in this particular case. I'd just do it in CF. If you need help with that code, let us know. Dave -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 FROMOrders 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 ~| 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:216480 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=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Quick Query Question...[hopefully]
Che, I agree with Mark. Anything you do in the DB is going to be more complex than in CF in this particular case. I'd just do it in CF. If you need help with that code, let us know. Dave -Original Message- From: Che Vilnonis [mailto:[EMAIL PROTECTED] Sent: Friday, August 26, 2005 9: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 FROMOrders 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:216479 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
RE: Quick Query Question...[hopefully]
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- From: Che Vilnonis [mailto:[EMAIL PROTECTED] Sent: Friday, August 26, 2005 7:50 AM To: CF-Talk Subject: RE: Quick Query Question...[hopefully] Mark, that's what I thought. I tried to find a simple solution using the db, but I don't think it's going to work very well. Thanks, Che. -Original Message- From: Mark A Kruger [mailto:[EMAIL PROTECTED] Sent: Friday, August 26, 2005 8:47 AM To: CF-Talk Subject: RE: Quick Query Question...[hopefully] 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 ~| 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:216476 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
RE: Quick Query Question...[hopefully]
Mark, that's what I thought. I tried to find a simple solution using the db, but I don't think it's going to work very well. Thanks, Che. -Original Message- From: Mark A Kruger [mailto:[EMAIL PROTECTED] Sent: Friday, August 26, 2005 8:47 AM To: CF-Talk Subject: RE: Quick Query Question...[hopefully] 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 ~| 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:216474 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
RE: Quick Query Question...[hopefully]
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 FROMOrders 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. HOURTOTALORDERSPERHOUR 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
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 FROMOrders 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. HOURTOTALORDERSPERHOUR 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 ~| 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:216472 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
RE: Quick Query Question...[hopefully]
Che, You can use isnull() in SQL Server and nvl() in Oracle. I don't know which DB you're using. Dave -Original Message- From: Che Vilnonis [mailto:[EMAIL PROTECTED] Sent: Thursday, August 25, 2005 4:53 PM To: CF-Talk Subject: Quick Query Question...[hopefully] 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. HOURTOTALORDERSPERHOUR 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 ** The information contained in this message, including attachments, may contain privileged or confidential information that is intended to be delivered only to the person identified above. If you are not the intended recipient, or the person responsible for delivering this message to the intended recipient, ALLTEL requests that you immediately notify the sender and asks that you do not read the message or its attachments, and that you delete them without copying or sending them to anyone else. ~| 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:216471 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
Re: Quick Query Question...[hopefully]
Database? version? table structure? sample data? ... -- Eddie Awad. http://awads.net/ On 8/25/05, Che Vilnonis <[EMAIL PROTECTED]> wrote: > 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. > > HOURTOTALORDERSPERHOUR > 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:216430 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=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Quick Query Question...[hopefully]
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. HOURTOTALORDERSPERHOUR 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 ~| 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:216420 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=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
