You might look at the "week" value of the DATEPART function. Lots of places will create a calendar table.
The table would have columns in it such as: fullDate day dayOfWeek dayOfWeekAsName Month MonthAsName Year Quarter Week fiscalYearQuarter fiscalYearDayOfYear etc... Then, use a script to load a few thousand dates for several years into the future. You should then be able to do just about any join you need. M!ke -----Original Message----- From: Robert Rawlins - Think Blue [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 08, 2007 9:10 AM To: CF-Talk Subject: RE: Table Data Thanks for the help on that Mike, I'll toy around with simplifying it later today, I like to keep things as lightweight as possible. On a slightly different concept I've been trying to adapt that query to group my data into days (that's the easy part) and then display them as a week, and I want the query to always return 7 results, if there aren't any records for that day then just leave it at 0, whereas without any of this fancy LEFT OUTER stuff it'll just leave a gap. I've posted about it on the SQL list but not had any bites yet, perhaps you could pass your eye over it and give me your thoughts. http://www.houseoffusion.com/groups/sql/thread.cfm/threadid:776 It's the same query as below but gives a bit more of an explanation of what I'm trying to do with it. Just for the record I'd also like to -THEN- make a version of the query that has the same resulting effect but for 12 months of the year :-D Thanks again pal, Rob ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade & see new features. http://www.adobe.com/products/coldfusion?sdid=RVJR Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:277365 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
