Do you have an example of what you mean? Maybe I've been staring at it
too
long but i just don't see how to populate non existent data.
======================================
Yeah, here's a VERY quick example. It doesn't involve a pivot table,
but it shows what I mean by getting around missing values. The more I
look at it, the more I hate it-- but like I said-- quick and dirty...
The cfloop will loop once for every day regardless of whether or not
there was data collected for that day. In other words, don't control
your output based on the actual data, but rather on the set of known
dates in the range.
This code runs for me on CF7.
<cfset report_start_date = "1/1/2007">
<cfset report_end_date = "1/31/2007">
<cfset report_date = "">
<cfset qry_sales = querynew("date_of_sale","date")>
<cfset queryaddrow(qry_sales,10)>
<cfset querysetcell(qry_sales,"date_of_sale","1/1/2007",1)>
<cfset querysetcell(qry_sales,"date_of_sale","1/1/2007",2)>
<cfset querysetcell(qry_sales,"date_of_sale","1/1/2007",3)>
<cfset querysetcell(qry_sales,"date_of_sale","1/5/2007",4)>
<cfset querysetcell(qry_sales,"date_of_sale","1/4/2007",5)>
<cfset querysetcell(qry_sales,"date_of_sale","1/6/2007",6)>
<cfset querysetcell(qry_sales,"date_of_sale","1/10/2007",7)>
<cfset querysetcell(qry_sales,"date_of_sale","1/20/2007",8)>
<cfset querysetcell(qry_sales,"date_of_sale","1/20/2007",9)>
<cfset querysetcell(qry_sales,"date_of_sale","1/20/2007",10)>
<cfquery name="qry_grouped_sales" dbtype="query">
SELECT date_of_sale, count(1) as num_orders
FROM qry_sales
WHERE date_of_sale between '#report_start_date#' and
'#report_end_date#'
GROUP BY date_of_sale
ORDER BY date_of_sale
</cfquery>
<cfdump var="#qry_grouped_sales#">
<cfset report_date = report_start_date>
<table border="1">
<tr>
<td>Day</td>
<td>Number Of Sales</td>
</tr>
<cfloop condition="report_date lt report_end_date">
<cfoutput>
<cfquery name="qry_this_days_sales"
dbtype="query">
SELECT *
FROM qry_grouped_sales
WHERE date_of_sale = #createodbcdatetime(report_date)#
</cfquery>
<tr>
<td>#dateformat(report_date)#</td>
<td>#val(qry_this_days_sales.num_orders)#</td>
</tr>
</cfoutput>
<cfset report_date = dateadd("d",1,report_date)>
</cfloop>
</table>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Get the answers you are looking for on the ColdFusion Labs
Forum direct from active programmers and developers.
http://www.adobe.com/cfusion/webforums/forum/categories.cfm?forumid-72&catid=648
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:288599
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
