Help with Coldfusion / SQL query
Question:
Ok so what im trying to do is query a database for all sales transactions for a specific user for a specific month. I can write the sql joins and what not fine and the query returns everything in the associated tables for all the transactions. I need the users dashboard to add up the activityCnt field for the prodID and display one line per product for the current month or other months as needed. Here are the list of tables and my current code….
Tables
Company
Users
Products
activityTransactions
best
forecast
And the attaced code returns something like this…
New Activations 11 avg 11 1 0 12/14/2009 Update
New Activations 11 avg 11 2 0 12/14/2009 Update
New Activations 11 avg 11 2 0 12/14/2009 Update
New Activations 11 avg 11 1 0 12/14/2009 Update
New Activations 11 avg 11 1 0 12/14/2009 Update
New Activations 11 avg 11 1 0 12/14/2009 Update
New Activations 11 avg 11 1 0 12/14/2009 Update
New Activations 11 avg 11 1 0 12/14/2009 Update
New Activations 11 avg 11 1 0 12/15/2009
New Activations 11 avg 11 0 0 12/15/2009
New Activations 11 avg 11 2 0 12/15/2009
New Activations 11 avg 11 2 0 12/15/2009
Upgrades 15 avg 7 5 0 12/14/2009 Update
Upgrades 15 avg 7 2 0 12/14/2009 Update
Upgrades 15 avg 7 2 0 12/15/2009
Upgrades 15 avg 7 1 0 12/15/2009
H-Tier Plan 11 avg 9 0 0 12/14/2009 Update
H-Tier Plan 11 avg 9 3 0 12/14/2009 Update
H-Tier Plan 11 avg 9 1 0 12/14/2009 Update
H-Tier Plan 11 avg 9 3 0 12/14/2009 Update
H-Tier Plan 11 avg 9 1 0 12/15/2009
I need this to be one line for each product with the total activity in the activity field as well as the most recent entry for each product t act as the update date so the user can see when the last update was made.
Thanks in advance
Code Snippet:
<cfquery name=”getActivityRelate”>
SELECT
dbo.company.companyName,
dbo.products.prodName,
dbo.products.prodID,
dbo.users.fName,
dbo.users.lName,
dbo.activityTransactions.activityCnt,
dbo.activityTransactions.updateDate,
dbo.activityTransactions.activeMM,
dbo.activityTransactions.activeDD,
dbo.activityTransactions.activeyyyy,
dbo.best.best,
dbo.forecast.forecast
FROM
dbo.products
INNER JOIN dbo.company ON (dbo.products.compID=dbo.company.compID)
INNER JOIN dbo.users ON (dbo.users.compID=dbo.company.compID)
AND (dbo.products.userID=dbo.users.userID)
INNER JOIN dbo.activityTransactions ON (dbo.company.compID=dbo.activityTransactions.compID)
AND (dbo.users.userID=dbo.activityTransactions.userID)
AND (dbo.products.prodID=dbo.activityTransactions.prodID)
AND (dbo.users.compID=dbo.activityTransactions.compID)
AND (dbo.products.userID=dbo.activityTransactions.userID)
AND (dbo.products.compID=dbo.activityTransactions.compID)
INNER JOIN dbo.forecast ON (dbo.forecast.prodID=dbo.activityTransactions.prodID)
AND (dbo.forecast.userID=dbo.activityTransactions.userID)
AND (dbo.forecast.compID=dbo.activityTransactions.compID)
AND (dbo.forecast.compID=dbo.company.compID)
AND (dbo.forecast.prodID=dbo.products.prodID)
AND (dbo.forecast.userID=dbo.users.userID)
INNER JOIN dbo.best ON (dbo.best.userID=dbo.activityTransactions.userID)
AND (dbo.best.prodID=dbo.activityTransactions.prodID)
WHERE
users.userID = #session.userID# AND
(dbo.activityTransactions.activeMM = 12) AND
(dbo.activityTransactions.activeYYYY = 2009)
</cfquery>
<table width=”641″ border=”0″>
<tr>
<td width=”207″ height=”19″ align=”center”> </td>
<td width=”38″ align=”center”><strong>Best</strong></td>
<td width=”48″ align=”center”><strong>Average</strong></td>
<td width=”60″ align=”center”><strong>Forecast</strong></td>
<td width=”69″ align=”center”><strong>Activity</strong></td>
<td width=”33″ align=”center”><strong>Trend</strong></td>
<td width=”80″ align=”center”><strong>Updated</strong></td>
<td width=”72″ align=”center”></td>
</tr>
<cfoutput query=”getActivityRelate”>
<cfset oldDate=dateAdd(“D”,”-2″,now())>
<tr>
<td><b>#prodName#</b></td>
<td align=”center”>#best#</td>
<td align=”center”>avg</td>
<td align=”center”>#forecast#</td>
<td align=”center”>#activityCnt#</td>
<td align=”center”>0</td>
<td align=”center”>
<cfif #DateFormat(updateDate, “mm/dd/yyyy”)# GTE “#oldDate#”>
#DateFormat(updateDate, “mm/dd/yyyy”)#
<cfelse>
<font color=red>#DateFormat(updateDate, “mm/dd/yyyy”)#</font>
</cfif>
</td>
<td align=”center”>
<cfif #DateFormat(updateDate, “mm/dd/yyyy”)# GTE “#oldDate#”>
<cfelse>
<font color=red>Update</font>
</cfif>
</td>
</tr>
</cfoutput>
</table>
Solution:
try the attached query instead of yours
Azadi
<cfquery name=”getActivityRelate”>
SELECT
dbo.company.companyName,
dbo.products.prodName,
dbo.products.prodID,
dbo.users.fName,
dbo.users.lName,
SUM(dbo.activityTransactions.activityCnt) AS activityCnt,
MAX(dbo.activityTransactions.updateDate) AS updateDate,
dbo.best.best,
dbo.forecast.forecast
FROM
dbo.products
INNER JOIN dbo.company ON (dbo.products.compID=dbo.company.compID)
INNER JOIN dbo.users ON (dbo.users.compID=dbo.company.compID)
AND (dbo.products.userID=dbo.users.userID)
INNER JOIN dbo.activityTransactions ON (dbo.company.compID=dbo.activityTransactions.compID)
AND (dbo.users.userID=dbo.activityTransactions.userID)
AND (dbo.products.prodID=dbo.activityTransactions.prodID)
AND (dbo.users.compID=dbo.activityTransactions.compID)
AND (dbo.products.userID=dbo.activityTransactions.userID)
AND (dbo.products.compID=dbo.activityTransactions.compID)
INNER JOIN dbo.forecast ON (dbo.forecast.prodID=dbo.activityTransactions.prodID)
AND (dbo.forecast.userID=dbo.activityTransactions.userID)
AND (dbo.forecast.compID=dbo.activityTransactions.compID)
AND (dbo.forecast.compID=dbo.company.compID)
AND (dbo.forecast.prodID=dbo.products.prodID)
AND (dbo.forecast.userID=dbo.users.userID)
INNER JOIN dbo.best ON (dbo.best.userID=dbo.activityTransactions.userID)
AND (dbo.best.prodID=dbo.activityTransactions.prodID)
WHERE
users.userID = #session.userID# AND
(dbo.activityTransactions.activeMM = 12) AND
(dbo.activityTransactions.activeYYYY = 2009)
GROUP BY dbo.company.companyName, dbo.products.prodName, dbo.products.prodID, dbo.users.fName, dbo.users.lName, dbo.best.best, dbo.forecast.forecast
</cfquery>
