I am trying to aggregate data for multiple communities(employment numbers by
sector). I'm using code which someone else wrote which uses a lot of arrays.
Some data is blocked for confidentiality purposes and is a null in the
database. Wherever there is a null value for a year/sector in ANY community I
need to output a "C" for the total. If all communities have values I output
the sum.
This works fine for single communities. For multiple communities if the value
for the first community passed is a null the code correctly outputs a C for
that year, but if the first community has a value and the second is a null the
code outputs the value of the first community... I've tried a huge variety of
things but I can't get the code to check each community's value for the
year/sector before moving on to the next piece of the if statement. Any ideas
would be appreciated, I need to get this project done!
Thanks so much,
Christina
<!--- Create recordset from database... --->
<Cfquery Name="rstEmpEst" >
SELECT SECTOR, EMP2002, EMP2005, semmcd
FROM employment_dba.emp_est
WHERE semmcd IN (#local_semmcd#)
ORDER BY sector
</Cfquery>
<Cfset arrEmpEst = ArrayNew(2)>
<Cfset arrEmpEst[1][1] = "Natural Resources & Mining" >
<Cfset arrEmpEst[2][1] = "Manufacturing" >
<Cfset arrEmpEst[3][1] = "Wholesale Trade" >
<Cfset arrEmpEst[4][1] = "Retail Trade" >
<Cfset arrEmpEst[5][1] = "Transportation & Warehousing" >
<Cfset arrEmpEst[6][1] = "Utilities" >
<Cfset arrEmpEst[7][1] = "Information" >
<Cfset arrEmpEst[8][1] = "Financial Activities" >
<Cfset arrEmpEst[9][1] = "Professional, Scientific, & Technical Services" >
<Cfset arrEmpEst[10][1] = "Management of Companies & Enterprises" >
<Cfset arrEmpEst[11][1] = "Administrative, Support, & Waste Services" >
<Cfset arrEmpEst[12][1] = "Education Services" >
<Cfset arrEmpEst[13][1] = "Health Care & Social Assistance" >
<Cfset arrEmpEst[14][1] = "Leisure & Hospitality" >
<Cfset arrEmpEst[15][1] = "Other Services" >
<Cfset arrEmpEst[16][1] = "Public Administration" >
<Cfset arrEmpEst[17][1] = "Total" >
<!--- change blank values to store C --->
<Cfloop index="z" from=1 to=17>
<cfquery name="qdEmp" dbtype="query">
SELECT SECTOR, EMP2002, EMP2005 FROM rstEmpEst WHERE SECTOR = #z#
</cfquery>
<cfquery name="qdEmpSum" dbtype="query">
SELECT SECTOR, SUM(EMP2002) as emp2002, SUM(EMP2005) as emp2005
FROM rstEmpEst
WHERE SECTOR = #z#
GROUP BY sector
</cfquery>
<cfloop query="qdEmp">
<cfoutput>
<Cfif qdEmp.emp2002 Is "">
<cfset arrEmpEst[z][2] = "C" >
<cfelse>
<Cfset arrEmpEst[z][2] = qdEmpSum.emp2002 >
</Cfif>
<Cfif qdEmp.emp2005 Is "">
<cfset arrEmpEst[z][3] = "C" >
<cfelse>
<Cfset arrEmpEst[z][3] = qdEmpSum.emp2005 >
</Cfif>
</cfoutput>
</cfloop>
</Cfloop>
<Cfloop index="w" from=1 to=16>
<td class="item">#arrEmpEst[w][1]#</td>
<Cfif arrEmpEst[w][2] Is "C" >
<td class="data">#arrEmpEst[w][2]#</td>
<Cfelse>
<td class="data">#LsNumberFormat(arrEmpEst[w][2], "999,999")#</td>
</Cfif>
<Cfif arrEmpEst[w][3] Is "C" >
<td class="data">#arrEmpEst[w][3]#</td>
<Cfelse>
<td class="data">#LsNumberFormat(arrEmpEst[w][3], "999,999")#</td>
</Cfif>
<Cfif arrEmpEst[w][2] Is "C" or arrEmpEst[w][3] Is "C" >
<td class="data">C</td>
<Cfelse>
<td class="data">#LsNumberFormat(arrEmpEst[w][3]-arrEmpEst[w][2],
"999,999")#</td>
</Cfif>
</tr>
</Cfloop>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:4344
Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
