This can be accomplished by using a Tally table. A "tally table" is
simply a table that contains one column, ID, that increments up to
several thousand (or several million, depending on your application).
For this application, you would use it like this:
Tally
--------------
ID int
MyData
--------------
Amount int
Date datetime
SELECT t.ID,
m.Amount,
m.date
FROM Tally as t
LEFT OUTER JOIN MyData m on Hour(m.date) = t.ID
WHERE t.id <= 24
HTH,
Chris
> -----Original Message-----
> From: Robert Rawlins - Think Blue
> [mailto:[EMAIL PROTECTED]
> Sent: Friday, May 04, 2007 2:33 AM
> To: CF-Talk
> Subject: Table Data
>
> Morning Guys,
>
>
>
> I'm a little stumped on how to resolve this problem, hopefully someone
> will
> have a bright idea on how to achieve it. I have a bunch of table data
> which
> gets displayed hour on hour for a certain time period. This works
> absolutely
> perfectly at the moment, however the little annoyance occurs when the
user
> is looking at today's information. If they look at any past dates then
> they
> get a nice full length table with 24 rows (one for each hour) but if
they
> look at today's date then it'll only display the records that have
been
> published into persistence up until that moment, so if they check it
first
> thing in the morning they only get 6 or 7 rows of data.
>
>
>
> This isn't really a big deal, but it looks a little untidy for
> presentation
> purposes, and what I'd like to do if have a full set of 24 rows there,
but
> only populate the ones that have information, then if for some reason
the
> system went down for a couple of hours on a particular date it
wouldn't
> end
> up with a funny 22 row table, it would just say 'no data' in the rows
that
> it didn't have data for.
>
>
>
> Does that make sense? What's the best way to achieve this? I'm
thinking
> perhaps a cfloop from 1 to 24 or something like that.
>
>
>
> Thanks for any ideas,
>
>
>
> Rob
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
ColdFusion MX7 by Adobe®
Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7.
Free Trial. http://www.adobe.com/products/coldfusion?sdid=RVJV
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:277054
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
