Time:
Scotland: Fri, 8:40 pm
Rhode Island: Fri, 3:40 pm
Florida: Fri, 3:40 pm
New Mexico: Fri, 1:40 pm
California: Fri, 12:40 pm

Click here to visit Livelife365.com

Click here to visit nmdarts.com



Buy this Ad Space.

180px wide.

Please get in touch with KH@ if you are interested and make an offer.

CLICK HERE TO GET AUCTION BAR NOW
US$10 per year - Save $100s!
The Fabulously Unfair
WebX Auction Bar. For Ebay etc.
Ro-Sham-Bo the opposition. Laugh like Eric Cartman when you win! CLICK HERE NOW!

More information and sign-up.

WebXpertz Hosting.
Custom fit from $5pm. PHP/MySQL
You'll save money, we'll save money. Seems fair to me. Interested? If so Please PM me here and tell me what you need. Thanks!


Please click here for more information
PDA

For continued disscussion on this topic : max(week) function help

mindytruitt
07-08-2004, 11:15 PM
I hope this makes since because it seems really confusing in my head. I am pulling data from Chargeability database which has the column week, and for each person they have different numbers of week. I am trying to write an if-statement that if the maximum week number for each person is equal to the maximum of all entrys.

<!--This gets the maximum for all entries-->
<cfquery name="getMaxWeek" datasource="literature">
SELECT
max(week) AS max_week
FROM
Chargeability
</cfquery>

<CFQUERY NAME="Charge" DATASOURCE="Literature">
SELECT *
FROM Users, Chargeability, Region
WHERE Users.UserId = Chargeability.Employee AND Region.RegionID = Users.Region AND Region.regionid <> 4 AND Users.Sales >= 3
ORDER BY Users.Region, Users.UserName
</CFQUERY>


</head>
<body>

<cfset weekNum = #getMaxWeek.max_week#>
<cfset startDate = "3/27/04">
<cfset dfmt ="mm/dd/yy">
<cfset numDays = (weekNum - 1) * 7 + 6>
<cfset finalDate = #DateFormat(dateadd("d", numDays, startDate),dfmt)#>

<font face="Arial, Helvetica, sans-serif" size="2"><b><cfoutput>Week #WeekNum# Ending: #finalDate#</cfoutput></b></font></td>



<cfoutput query="ChargeUsers" group="RegionID">

<tr>
<td width="120"> </td>
<td width="75"> </td>
<td width="75"> </td>
<td width="75"> </td>
<td width="75"> </td>
<td width="75"> </td>
<td width="75"> </td>
<td width="50"> </td>
</tr>
<tr>
<td width="120"><font face="Arial, Helvetica, sans-serif" size="2"><b>#RegionName#</b></font></td>
<td width="75" Align="Center"><font face="Arial, Helvetica, sans-serif" size="1">Charge</font></td>
<td width="75" Align="Center"><font face="Arial, Helvetica, sans-serif" size="1"><b>Internal Charge</b></font></td>
<td width="75" Align="Center"><font face="Arial, Helvetica, sans-serif" size="1"><b>Vacation/Holiday/</b></font></td>
<td width="75" Align="Center"><font face="Arial, Helvetica, sans-serif" size="1"><b>Unassigned</b></font></td>
<td width="75" Align="Center"><font face="Arial, Helvetica, sans-serif" size="1"><b>Available</b></font></td>
<td width="75" Align="Center"><font face="Arial, Helvetica, sans-serif" size="1"><b>Percent</b></font></td>
<td width="50"></td>
</tr>

<tr>
<td width="120" Align="Center"><font face="Arial, Helvetica, sans-serif" size="1"><b><u> </u></b></font></td>
<td width="75" Align="Center"><font face="Arial, Helvetica, sans-serif" size="1"><b><u>Hours</u></b></font></td>
<td width="75" Align="Center"><font face="Arial, Helvetica, sans-serif" size="1"><b><u>Hours</u></b></font></td>
<td width="75" Align="Center"><font face="Arial, Helvetica, sans-serif" size="1"><b><u>Leave Hours</u></b></font></td>
<td width="75" Align="Center"><font face="Arial, Helvetica, sans-serif" size="1"><b><u>Hours</u></b></font></td>
<td width="75" Align="Center"><font face="Arial, Helvetica, sans-serif" size="1"><b><u>Hours</u></b></font></td>
<td width="75" Align="Center"><font face="Arial, Helvetica, sans-serif" size="1"><b><u>Charge</u></b></font></td>
<td width="50"></td>
</tr>


<form name=AllEditWeek action=chargeabilityreportsubmit3.cfm method=post>


<cfoutput group="fullname">
<!--This is where I need the if-statement that if the maximum week number for each person is equal to the overall maximum for each entry-->
<CFIF max(Week) EQ weekNum>

<tr>
<td width="120" bordercolor="blue"><font size="1"><b>#ChargeUsers.fullname#</b></font></td>
<td width="40" Align="Right"><font face="Arial, Helvetica, sans-serif" size="1">#NumberFormat(ChargeHours, "__._")#</font></td>
<td width="50" Align="Right"><font face="Arial, Helvetica, sans-serif" size="1">#NumberFOrmat(InternalHours, "__._")#</font></td>
<td width="50" Align="Right"><font face="Arial, Helvetica, sans-serif" size="1">#NumberFOrmat(VacationHours, "__._")#</font></td>
<td width="45" Align="Right"><font face="Arial, Helvetica, sans-serif" size="1">#NumberFormat(NassignHours, "__._")#</font></td>
<td width="50" Align="Right"><font face="Arial, Helvetica, sans-serif" size="1">#NumberFormat(AvailableHours, "__._")#</font></td>
<td width="50" Align="Right"><font face="Arial, Helvetica, sans-serif" size="1">#DecimalFormat(ChargeHours/AvailableHours*100)# %</font></td>
</tr>

<CFELSE>

<input type="hidden" name="Employee" value="#ChargeUsers.UserID#">
<input type="hidden" name="week" value="#Week#">

<tr>
<td width="120" bordercolor="blue"><font size="1"><b>#ChargeUsers.fullname#</b></font></td>
<td width="75" Align="Center"><input type="text" name="ChargeHours" size=3 ></font></td>
<td width="75" Align="Center"><input type="text" name="InternalHours" size=3></font></td>
<td width="75" Align="center"><input type="text" name="VacationHours" size=3></font></td>
<td width="75" Align="Center"><input type="text" name="NassignHours" size=3></font></td>
<td width="75" Align="Center"><input type="text" name="AvailableHours" size=3></font></td>
<td width="75" Align="center"> % </font></td>
<td width="50"><input type="submit" name="Submit" value="OK"> </td>
</tr>

</CFIF>
</cfoutput>


</form>
fizzled
07-21-2004, 03:19 AM
A few things I notice.

1. You do <cfset weekNum = #getMaxWeek.week_max#>. In this instance you could simply leave out the pound signs: <cfset weekNum = getMaxWeek.week_max>.

2. You have <cfoutput query="ChargeUsers" group="RegionID">, but your query is named "Charge" not "ChargeUsers". Also I'm not sure group="RegionID" will work since your SQL has ORDER BY Users.Region (not RegionID). The same for the later <cfoutput group="FullName"> (your SQL has ORDER BY Users.Region, Users.UserName, not FullName). Look here for more info on the cfoutput tag: http://livedocs.macromedia.com/coldfusion/6/CFML_Reference/Tags-pt211.htm#1101659

3. When you use <cfif Max(Week) eq weekNum> you are calling the CF Max() function, which is NOT the same as the MAX() aggregate function in SQL. Info on the CF Max() function here: http://livedocs.macromedia.com/coldfusion/6/CFML_Reference/functions-pt240.htm#1109700