Code to get week_ending data for an entire month Using ColdFusion
Hello,
I have been working on a report that is supposed to display data based on the "week_ending" dates of the month, however; the same results are displaying for every week. I am not sure what I have done wrong here. I used a similar report as a guide but for some reason it is not functioning properly. Below is the code that I used to designate the week ending parameter. Do I need to do more in order to get the proper week's data to report? Thanks.
I have been working on a report that is supposed to display data based on the "week_ending" dates of the month, however; the same results are displaying for every week. I am not sure what I have done wrong here. I used a similar report as a guide but for some reason it is not functioning properly. Below is the code that I used to designate the week ending parameter. Do I need to do more in order to get the proper week's data to report? Thanks.
<cfloop index="wkends" from=1 to="#count#" step=1>
<cfquery name="getCalcOUGoal" datasource="reo">
UPDATE sales_volume
SET sales_volume.OU_Goal=[openhouses]-[oh_goal]
WHERE week_ending='#mywkend#'
</cfquery>
<cfquery name="getOUGoal" datasource="reo">
SELECT OU_GOAL
FROM sales_volume
WHERE week_ending='#mywkend#'
</cfquery>
<td align="center">#getWKEnding.openhouses#</td>
<td align="center">#getWKEnding.OU_Goal#</td>
ASKER
Hello,
Thank you for your response. I have attached all of the code below. Any assistance would be greatly appreciated.
Thank you for your response. I have attached all of the code below. Any assistance would be greatly appreciated.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>Open House Goals</title>
</head>
<body leftmargin="0" marginheight="0" marginwidth="0" bottommargin="0" rightmargin="0" topmargin="0"
alink="993399" link="993399" vlink="black">
<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tr><td>
<table align="center">
<tr>
<form action="open_house_goals.cfm" method="post" name="excelReport">
<td align="right" colspan="2"><input type="submit" name="Excel" value="Report in Excel">
<!--- <input type="hidden" name="search_month" value="#form.search_month#">
<input type="hidden" name="search_year" value="#form.search_year#"> --->
</td>
</form>
</tr>
<!--- report in excel spreadsheet --->
<!--- <cfif parameterExists(Excel)> --->
<cfheader name="Content-Disposition" value="attachment;filename=Openhouse.xls" >
<cfheader name="cache-control" value="cache">
<cfcontent type="application/msexcel" >
<cfset date_var='#form.search_month#/01/#form.search_year#'>
<tr><td align="center" colspan="20"><h3>Open House Goals
<cfoutput>#DateFormat(date_var, 'mmmm')#, #DateFormat(date_var, 'yyyy')#</cfoutput></h3></td></tr>
<cfset nmbr_to_add = (8-(DayOfWeek(date_var))) MOD 7>
<!---<cfset nmbr_to_add = 8-(DayOfWeek(date_var) MOD 7)>--->
<!--- <cfoutput>Number to add to #date_var# to get to Sunday is: #nmbr_to_add#</cfoutput> --->
<cfset wkend='#dateformat(dateAdd('d', nmbr_to_add, date_var), 'mm/dd/yy')#'>
<!--- get number of weekends for this month --->
<cfset count= 0>
<cfloop index="countwks" from=0 to=5 step=1>
<cfif dateformat(wkend, 'm') is dateformat(date_var, 'm')>
<cfset count= count +1>
</cfif>
<cfset wkend = '#dateformat(dateAdd('d', '7', wkend), 'mm/dd/yy')#'>
</cfloop>
<!--- <cfoutput>#count#</cfoutput> --->
<table width="350" align="center" border="1">
<tr><td> </td>
<cfset wkenddate ='#dateformat(dateAdd('d', nmbr_to_add, date_var), 'mm/dd/yy')#'>
<td> </td>
<td> </td>
<cfloop index="wkenddates" from=1 to="#count#" step=1>
<td colspan="2" align="center" ><cfoutput><b>#wkenddate#</b></cfoutput></td>
<cfset wkenddate = '#dateformat(dateAdd('d', '7', wkenddate), 'mm/dd/yy')#'>
</cfloop>
<td colspan="3" align="center"><b>Total</b></td>
</tr>
<tr bgcolor="808080">
<td align="center"><b>Office</b></td>
<td align="center"><b>Licenses</b></td>
<td align="center"><b>Goal</b></td>
<cfloop index="num" from="1" to="#count#">
<td align="center"><b>O.H</b></td>
<td align="center"><b>Over/Under</b></td>
</cfloop>
<td align="center"><b>Total <br>O.H.</b></td>
<td align="center"><b>Total <br>Licenses</b></td>
<td align="center"><b>Total <br>Over/Under</b></td>
</tr>
<cfquery name="getOffice" datasource="reo">
SELECT distinct(office_ID)
FROM sales_volume
ORDER BY office_ID
</cfquery>
<cfset totalAllMTD_OH = 0>
<cfset totalAllMTD_Licenses = 0>
<cfset totalAllMTD_OUGoal = 0>
<cfset totalDETMTD_OH = 0>
<cfset totalDETMTD_Licenses = 0>
<cfset totalDETMTD_OUGoal = 0>
<cfoutput query="getoffice">
<tr>
<td align="center"><b>#getoffice.office_ID#</b></td>
<cfset mywkend='#dateformat(dateAdd('d', nmbr_to_add, date_var), 'mm/dd/yy')#'>
<cfset total_OH = 0>
<cfset total_Licenses =0>
<cfset total_OUGoal = 0>
<!---<cfset total_Int = 0> --->
<cfset mywkend='#dateformat(dateAdd('d', nmbr_to_add, date_var), 'mm/dd/yy')#'>
<cfloop index="wkends" from=1 to="#count#" step=1>
<cfset mywkend='#dateformat(dateAdd('d', nmbr_to_add, date_var), 'mm/dd/yy')#'>
<cfquery name="getWkEnding" datasource="reo">
SELECT *
FROM sales_volume
WHERE week_ending='#mywkend#'
AND office_ID='#getoffice.office_ID#'
</cfquery>
<cfquery name="getCalcOHGoal" datasource="reo">
UPDATE sales_volume
SET sales_volume.oh_goal=[LicenseCount] * .25
WHERE week_ending='#mywkend#'
</cfquery>
<cfset mywkend = '#dateformat(dateAdd('d', '7', mywkend), 'mm/dd/yy')#'>
<!--- <cfif #wkend# MOD '2' NEQ '0'><cfset col="FFFFA6"><cfelse> <cfset col="White"></cfif>--->
</cfloop>
<td align="center" >#getWKEnding.LicenseCount#</td>
<td align="center">#getWKEnding.OH_Goal#</td>
<cfloop index="wkends" from=1 to="#count#" step=1>
<cfquery name="getCalcOUGoal" datasource="reo">
UPDATE sales_volume
SET sales_volume.OU_Goal=[openhouses]-[oh_goal]
WHERE week_ending='#mywkend#'
</cfquery>
<cfquery name="getOUGoal" datasource="reo">
SELECT OU_GOAL
FROM sales_volume
WHERE week_ending='#mywkend#'
</cfquery>
<td align="center">#getWKEnding.openhouses#</td>
<td align="center">#getWKEnding.OU_Goal#</td>
<cfset mywkend = '#dateformat(dateAdd('d', '7', mywkend), 'mm/dd/yy')#'>
<!--- get Accrued MTD Total for OH, OHTraffic, Interview --->
<cfif getWKEnding.openhouses NEQ ''><cfset total_OH = total_OH + '#getWKEnding.openhouses#'></cfif>
<cfif getWkEnding.LicenseCount NEQ''><cfset total_Licenses ='#getWkEnding.LicenseCount#'></cfif>
<cfif getWKEnding.OU_Goal NEQ ''><cfset total_OUGoal = total_OUGoal + '#getWkEnding.OU_goal#'></cfif>
</cfloop>
<td align="center" bgcolor="COCOCO">#NumberFormat(Total_OH)#</td>
<td align="center" bgcolor="COCOCO">#NumberFormat(Total_Licenses)#</td>
<td align="center" bgcolor="COCOCO">#NumberFormat(total_OUGoal)#</td>
</tr>
<cfset totalAllMTD_OH = totalAllMTD_OH + #Total_OH#>
<cfset totalAllMTD_Licenses = totalAllMTD_Licenses + #Total_Licenses#>
<cfset totalAllMTD_OUGOal = totalAllMTD_OUGOal + #Total_OUGoal#>
<cfif (office_ID NEQ '63' AND office_ID NEQ '64' AND office_ID NEQ '65' AND office_ID NEQ '67' AND office_ID NEQ '68' AND office_ID NEQ '69' )>
<cfset totalDETMTD_OH = totalDETMTD_OH + #Total_OH#>
<cfset totalDETMTD_Licenses = totalDETMTD_Licenses + #Total_Licenses#>
<cfset totalDETMTD_OUGoal = totalDETMTD_OUGoal + #Total_OUGoal#>
</cfif>
</cfoutput>
<tr><td><b>Detroit offices</b></td>
<!--- get totals for each week for Detroit Off --->
<cfset mywkend='#dateformat(dateAdd('d', nmbr_to_add, date_var), 'mm/dd/yy')#'>
<cfloop index="wkends" from=1 to="#count#" step=1>
<cfquery name="getDetTotals" datasource="reo">
SELECT SUM(openhouses) as totalDetOH,
SUM(OU_Goal) as totalDetOUGoal,
SUM(LicenseCount) as totalDetLicenses,
SUM(OH_Goal) as totalDetOHGoal
FROM Sales_volume
WHERE week_ending = '#mywkend#'
AND (office_ID <> 63 AND office_ID <> '64' AND office_ID <> 65 AND office_ID <> 67 AND office_ID <> 68 AND office_ID <> 69)
</cfquery>
</cfloop>
<cfoutput>
<td align="center"><b>#NumberFormat(getDetTotals.totalDetLicenses)#</b></td>
<td align="center"><b>#NumberFormat(getDetTotals.totalDetOHGoal)#</b></td>
</cfoutput>
<cfloop index="wkends" from="1" to="#count#" step="1">
<cfoutput>
<td align="center"><b>#NumberFormat(getDetTotals.totalDETOH)#</b></td>
<td align="center"><b>#NumberFormat(getDetTotals.totalDETOUGoal)#</b></td>
<cfset mywkend = '#dateformat(dateAdd('d', '7', mywkend), 'mm/dd/yy')#'>
</cfoutput>
</cfloop>
<cfoutput>
<!--- Output total MTD for Detroit offices --->
<td align="center"><b>#NumberFormat(totalDETMTD_OH)#</b></td>
<td align="center"><b>#NumberFormat(totalDETMTD_Licenses)#</b></td>
<td align="center"><b>#NumberFormat(totalDETMTD_OUGoal)#</b></td>
</cfoutput>
</tr>
<tr><td><b>All Company</b></td>
<!--- get totals for each week for ALL COMPANY --->
<cfset mywkend='#dateformat(dateAdd('d', nmbr_to_add, date_var), 'mm/dd/yy')#'>
<cfloop index="wkends" from=1 to="#count#" step=1>
<cfquery name="getTotals" datasource="reo">
SELECT SUM(openhouses) as totalOH,
SUM(OU_Goal) as totalOUGoal,
SUM(LicenseCount) as totalLicenses,
SUM(OH_Goal) as totalOHGoal
FROM Sales_volume
WHERE week_ending = '#mywkend#'
</cfquery>
</cfloop>
<cfoutput>
<td align="center"><b>#NumberFormat(getTotals.totalLicenses)#</b></td>
<td align="center"><b>#NumberFormat(getTotals.totalOHGoal)#</b></td>
</cfoutput>
<cfloop index="wkends" from="1" to="#count#" step="1">
<cfoutput>
<td align="center"><b>#NumberFormat(getTotals.totalOH)#</b></td>
<td align="center"><b>#NumberFormat(getTotals.totalOUGoal)#</b></td>
<cfset mywkend = '#dateformat(dateAdd('d', '7', mywkend), 'mm/dd/yy')#'>
</cfoutput>
</cfloop>
<cfoutput>
<!--- Output total MTD for all offices --->
<td align="center"><b>#NumberFormat(totalAllMTD_OH)#</b></td>
<td align="center"><b>#NumberFormat(totalAllMTD_Licenses)#</b></td>
<td align="center"><b>#NumberFormat(totalAllMTD_OUGOal)#</b></td>
</cfoutput>
</tr>
</table>
</td></tr>
<tr><td> </td></tr>
</table>
</td></tr>
</table>
</body>
</html>ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Your code has two queries, getCalcOUGoal and getOUGoal. But then in your display you refer to getWKEnding. No idea what that query's doing, as it's not in the code snippet.