The following code is from a menu on a site that shows the monthly/quarterly/yearly goal and sales the goal part is working fine but since switching from access to mysql the currently month/quarter/year sales isn't being calulated.
<!--- RIGHT MENU --->
<td>
<table class="rightMenu">
<tr>
<td class="rightMenu"
style="text-align: left;"><br><br>
<cfset theMonth = datePart("m",
now())>
<cfset theYear =
datePart("yyyy", now())>
<cfset theDay = datePart("d",
now())>
<cfquery
name="getMonthlyPurchase" datasource="#DS#"
cachedwithin="#CreateTimeSpan(0,4,35,0)#">
SELECT
Sum(tblItemPurchase.itemPurchasePrice) AS totalMonthlyPurchase
FROM tblItemPurchase
WHERE
tblItemPurchase.itemPurchaseDate >= '#theMonth#/#1#/#theYear#'
</cfquery>
<cfset theGoal = 175000>
<cfif
getMonthlyPurchase.recordCount neq 0 and
getMonthlyPurchase.recordCount neq "" and
getMonthlyPurchase.totalMonthlyPurchase neq "">
<cfset amountLeft =
theGoal - getMonthlyPurchase.totalMonthlyPurchase>
<cfelse>
<cfset amountLeft =
theGoal>
</cfif>
<p><table width="100%"><tr><td
bgcolor="#CCCCCC" style="font-size:
18px; font-weight: bold; text-align: center; padding: 2 3 2 3;
border-right: black 1px solid; border-bottom:black 1px
solid;">GOALS</td></tr></table>
<table
style="width:100%; "><tr><td style="border: dashed gray 1px;
padding: 2 3 2 3; font-size: 12px; width:100%;">
<strong>THIS
MONTH:</strong>
<table
bgcolor="#B9E1F4" width="100%">
<tr>
<td style="width:100%; ">
<span
class="voTitle">GOAL:<cfoutput>#DollarFormat(theGoal)#</cfoutput>.</span><span
style="font-size: 2px;"><br><br></span>
<span style="font-size: 10px; font-weight: bold;"><strong>SO
FAR:<cfoutput>#DollarFormat(getMonthlyPurchase.totalMonthlyPurchase)#</cfoutput></strong></span><span
style="font-size: 2px;"><br><br></span>
<span class="adminTitle">TO
GO:<cfoutput>#DollarFormat(amountLeft)#</cfoutput></span>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td style="font-size:
2px;"> </td>
</tr>
</table>
<cfset theMonth = datePart("m",
now())>
<cfset theYear =
datePart("yyyy", now())>
<cfswitch
expression="#theMonth#">
<cfcase value="1,2,3">
<cfset
startMonth = 1>
<cfset endMonth
= 3>
<cfset endDay =
31>
</cfcase>
<cfcase value="4,5,6">
<cfset
startMonth = 4>
<cfset endMonth
= 6>
<cfset endDay =
30>
</cfcase>
<cfcase value="7,8,9">
<cfset
startMonth = 7>
<cfset endMonth
= 9>
<cfset endDay =
30>
</cfcase>
<cfcase
value="10,11,12">
<cfset
startMonth = 10>
<cfset endMonth
= 12>
<cfset endDay =
31>
</cfcase>
</cfswitch>
<cfquery
name="getQuarterlyPurchase" datasource="#DS#"
cachedwithin="#CreateTimeSpan(0,4,35,0)#">
SELECT
Sum(tblItemPurchase.itemPurchasePrice) AS totalMonthlyPurchase
FROM tblItemPurchase
WHERE
tblItemPurchase.itemPurchaseDate >= '#startMonth#/1/#theYear#'
AND
tblItemPurchase.itemPurchaseDate <= '#endMonth#/#endDay#/#theYear#'
</cfquery>
<cfset theGoal = 500000>
<cfif
getQuarterlyPurchase.recordCount neq 0 and
getQuarterlyPurchase.recordCount neq "" and
getQuarterlyPurchase.totalMonthlyPurchase neq "">
<cfset amountLeft =
theGoal - getQuarterlyPurchase.totalMonthlyPurchase>
<cfelse>
<cfset amountLeft =
theGoal>
</cfif>
<table width="100%"><tr><td
style="border: dashed gray 1px; padding:
2 3 2 3; font-size: 12px;">
<strong>THIS
QUARTER:</strong>
<table
bgcolor="#9AD1EF" width="100%">
<tr>
<td>
<span
class="voTitle">GOAL:<cfoutput>#DollarFormat(theGoal)#</cfoutput>.</span><span
style="font-size: 2px;"><br><br></span>
<span style="font-size: 10px; font-weight:
bold;"><strong>SO FAR:<cfoutput>#DollarFormat(getQuarterlyPurchase.totalMonthlyPurchase)#</cfoutput></strong></span><span
style="font-size: 2px;"><br><br></span>
<span class="adminTitle">TO
GO:<cfoutput>#DollarFormat(amountLeft)#</cfoutput></span>
</td>
</tr>
</table>
</td>
</tr>
</table>
<cfset theYear =
datePart("yyyy", now())>
<cfquery
name="getYearlyPurchase" datasource="#DS#"
cachedwithin="#CreateTimeSpan(0,4,35,0)#">
SELECT
Sum(tblItemPurchase.itemPurchasePrice) AS totalMonthlyPurchase
FROM tblItemPurchase
WHERE
tblItemPurchase.itemPurchaseDate >= '1/1/#theYear#'
AND
tblItemPurchase.itemPurchaseDate <= '12/31/#theYear#'
</cfquery>
<cfset theGoal = 2000000>
<cfif
getYearlyPurchase.recordCount neq 0 and
getYearlyPurchase.recordCount neq "" and
getYearlyPurchase.totalMonthlyPurchase neq "">
<cfset amountLeft =
theGoal - getYearlyPurchase.totalMonthlyPurchase>
<cfelse>
<cfset amountLeft =
theGoal>
</cfif>
<table width="100%"><tr><td
style="border: dashed gray 1px; padding:
2 3 2 3; font-size: 12px;">
<strong>THIS
YEAR:</strong>
<table
bgcolor="#57B4E6" width="100%">
<tr>
<td>
<span
class="voTitle">GOAL:<cfoutput>#DollarFormat(theGoal)#</cfoutput>.</span><span
style="font-size: 2px;"><br><br></span>
<span style="font-size: 10px; font-weight:
bold;"><strong>SO FAR:<cfoutput>#DollarFormat(getYearlyPurchase.totalMonthlyPurchase)#</cfoutput></strong></span><span
style="font-size: 2px;"><br><br></span>
<span class="adminTitle">TO
GO:<cfoutput>#DollarFormat(amountLeft)#</cfoutput></span>
</td>
</tr>
</table>
</td>
</tr>
</table>
<cfquery name="getAllTime"
datasource="#DS#"
cachedwithin="#CreateTimeSpan(0,4,35,0)#">
SELECT
Sum(tblItemPurchase.itemPurchasePrice) AS totalMonthlyPurchase
FROM tblItemPurchase
</cfquery>
<table width="100%"><tr><td
style="border: dashed gray 1px; padding:
2 3 2 3; font-size: 12px;">
<strong>ALL
TIME:</strong>
<table
bgcolor="#2195D3" width="100%">
<tr>
<td>
<span style="font-size: 10px; font-weight: bold;
color:#FFFFFF;"><strong>SALES:
<cfoutput>#DollarFormat(getAllTime.totalMonthlyPurchase)#</cfoutput></strong></span><span
style="font-size: 2px;"><br><br></span>
</td>
</tr>
</table>
</td>
</tr>
</table>
<!--- <table style="width:100%; ">
<tr>
<td style="font-size:
2px;"> </td>
</tr>
<cfset theGoal =
dateDiff('d', now(), '19/04/2004')>
<tr>
<td style="border:
dashed gray 1px; padding: 2 3 2 3; font-size: 12px;">
<strong>SIX
MONTH</strong>
<table
bgcolor="#FFCC33" style="border: solid #FF9933 2px;"
width="100%">
<tr>
<td>
<span class="voTitle">GOAL: $250,750.00</span><span
style="font-size: 2px;"><br><br></span>
<span style="font-size: 10px; font-weight: bold;"><strong>DUE:
04/19/04.</strong></span><span style="font-size: 2px;"><br><br></span>
<span class="adminTitle">DAYS LEFT:
<cfoutput>#theGoal#</cfoutput>.</span>
</td>
</tr>
</table>
</td></tr></table>
--->
</p>
</td>
</tr>
</table>
</td>
<!--- END RIGHT MENU --->
Anyone that might be familiar with what needs to be changed to make this
query work in mysql not just access?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Special thanks to the CF Community Suite Silver Sponsor - New Atlanta
http://www.newatlanta.com
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:188549
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
