John,
cfloop applies to the query result, so it is going to loop once for each row
in your result set.
What I would do is get the 'week' in your query something like this (not
sure what DB you're using):
<cfquery datasource="..." name="qStaff">
SELECT s.StaffId,
s.staffName,
t.transactionDate,
week(t.transactionDate) as week_num, (don't know what function you
need to use here)
SUM(t.NumberOfTransactions) AS Trans
FROM Staff s INNER JOIN Transactions t ON s.StaffId = t.StaffId
GROUP BY s.StaffId,s.staffName
</cfquery>
<cfoutput query="qStaff" group="week_num">
...here you have output that shows once for each week...
<cfoutput>
...here you have output that shows for each row with the
same week_num...
</cfoutput>
</cfoutput>
That would do it.
Dave
-----Original Message-----
From: John Ahlen [mailto:[email protected]]
Sent: Sunday, December 21, 2008 12:22 PM
To: cf-newbie
Subject: looping over dates question
I've looked through various options in using <cfloop> to loop over dates,
but I'm having trouble figuring out how to apply it to a query -- or at
least my query.
The tables store data on the activity of staff members. One table for
staff, another table for the transaction. So the initial query looks
roughly like this:
SELECT s.StaffId,s.staffName,t.transactionDate,SUM(t.NumberOfTransactions)
AS Trans
FROM Staff s INNER JOIN Transactions t ON s.StaffId = t.StaffId
GROUP BY s.StaffId,s.staffName
I would like to loop over the results and create a weekly activity output
with the rows as weeks and the staffmembers in the columns.
Where does the cfloop fit into all of this? does it loop the query so that
it executes 1 time for each week? or does the loop apply to the query
result?
Thanks in advance,
John
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-newbie/message.cfm/messageid:4246
Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
