• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 434
  • Last Modified:

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.
<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>

Open in new window

0
jdines
Asked:
jdines
  • 2
1 Solution
 
duncancummingCommented:
What is #mywkend#?  Your code doesn't show us where you set that or what to.  You sure you don't want to use wkends instead (i.e. your loop counter)?

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.
0
 
jdinesAuthor Commented:
Hello,
  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>&nbsp;</td>
	<cfset wkenddate ='#dateformat(dateAdd('d', nmbr_to_add, date_var), 'mm/dd/yy')#'>
	
	
   
    <td>&nbsp;</td>
    <td>&nbsp;</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>&nbsp;</td></tr>
</table>
</td></tr>
</table>

</body>
</html>

Open in new window

0
 
jdinesAuthor Commented:
I had to rearrange the <CFloop> tags and that resolved the issue with the same data repeating for each week.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now