This is probably simple - but my query looks complex...
Essentially - I have a two tables (although I am grouping several here) - A
Project - And Messages. One Project/Work Ticket will have several Messages
posted in it. The Project ID (WTPID)is also in the Message (messageid and
WTPID. Simple enough.
What I am trying to do is output a list of projects, show hoe many messages are
saved to each project (This is working) and when the last message was posted.
(Not Working.) Basically - it's working fine - but it's not sorting the
messages by date - in the output it displays the FIRST message always - not the
most recent.
??? DO I need to group the output?
Query in its current form:
<cfquery name="qryGetProjectList" datasource="#Application.DSN#">
SELECT WT_Projects.*, WT_ProjectLinks.*, EmailSubscribers.FirstName,
EmailSubscribers.LastName, EmailSubscribers.ScreenName,
WT_StatusCodes.StatusName, WT_PriorityCodes.PriorityPercent,
WT_Messages.MessageDate, COUNT(WT_Messages.messageid) AS MessageCount
FROM WT_Projects, WT_ProjectLinks, EmailSubscribers, WT_StatusCodes,
WT_PriorityCodes, WT_Messages
Where WT_ProjectLinks.ClientID = <cfqueryparam cfsqltype="cf_sql_integer"
value="#ARGUMENTS.ForClient#">
AND WT_Projects.WTPID = WT_ProjectLinks.WTPID
AND EmailSubscribers.ESID = WT_Projects.ESID
AND WT_Projects.ProjectStatus = WT_StatusCodes.StatusID
AND WT_Projects.ProjectPriority = WT_PriorityCodes.PriorityName
AND WT_Messages.WTPID = WT_Projects.WTPID
<cfif IsDefined("ARGUMENTS.ProjectStatus") AND #ARGUMENTS.ProjectStatus# NEQ "">
AND WT_Projects.ProjectStatus IN (#ARGUMENTS.ProjectStatus#)
</cfif>
Group by WT_Projects.WTPID
ORDER BY WT_Projects.ProjectPriority DESC, WT_Messages.MessageDate DESC
</cfquery>
Output....
<cfloop query="qryGetProjectList">
<CFIF CurrentRow MOD 2 is 1>
<CFSET rowcolor="even">
<CFELSE>
<CFSET rowcolor="odd">
</CFIF>
<cfoutput>
<tr class="#rowcolor#" onMouseover="this.className='selected';"
onMouseout="this.className='#rowcolor#';">
<th scope="row" id="r#count#"><a
href="projects.cfm?ProjectID=#WTPID#&ProjectFunction=Detail">
<cfif #LSDateFormat(qryGetProjectList.MessageDate,
"mm-dd-yyyy")# GTE #LSDateFormat(NewMessageDate, "mm-dd-yyyy")#>
<img src="images/comment_new.gif" border="0" />
<cfelse>
<img src="images/comment.gif" border="0" />
</cfif>## #WTPID#</a></th>
<td><strong>#ProjectName#</strong><br
/>#ProjectDesc#</td>
<td><strong>#MessageCount#</td>
<td>DC: #LSDateFormat(DateCreated, "mm-dd-yyyy")#<br />
LU: #LSDateFormat(DateUpdated,
"mm-dd-yyyy")#<br />
MD: #LSDateFormat(MessageDate, "mm-dd-yyyy")#</td>
<td>#ScreenName#</td>
<td><input type="hidden" value="#ProjectPriority#"
name="JobPriority_#count#" />
<ul class="star-rating small-star">
<li class="current-rating #PriorityPercent#" > </li>
<li><a href="javascript: void();" title="1 star out of 5"
class="one-star"
onClick="document.form1.JobPriority_#count#.value='1';this.className='current-rating
StarWidth1'">1</a></li>
<li><a href="javascript: void();" title="2 stars out of 5"
class="two-stars"
onClick="document.form1.JobPriority_#count#.value='2';this.className='current-rating
StarWidth2'">2</a></li>
<li><a href="javascript: void();" title="3 stars out of 5"
class="three-stars"
onClick="document.form1.JobPriority_#count#.value='3';this.className='current-rating
StarWidth3'">3</a></li>
<li><a href="javascript: void();" title="4 stars out of 5"
class="four-stars"
onClick="document.form1.JobPriority_#count#.value='4';this.className='current-rating
StarWidth4'">4</a></li>
<li><a href="javascript: void();" title="5 stars out of 5"
class="five-stars"
onClick="document.form1.JobPriority_#count#.value='5';this.className='current-rating
StarWidth5'">5</a></li>
</ul>
</td>
<td>
<input type="hidden" name="id_#count#" value="1">
<input type="hidden" name="WTPID_#count#"
value="#qryGetProjectList.WTPID#">
<cfset ThisCodeID = #qryGetProjectList.ProjectStatus#>
<select name="StatusCode_#count#"
style="font-family:Verdana, Arial, Helvetica, sans-serif; font-size:9px ">
<cfloop query="qryGetStatusCodes">
<option value="#qryGetStatusCodes.StatusID#"
<cfif #ThisCodeID# EQ
#qryGetStatusCodes.StatusID#>selected</cfif>>#qryGetStatusCodes.StatusName#</option>
</cfloop>
</select></td>
</tr>
</cfoutput>
<cfset count = count+1>
</cfloop>
Thanks!
- Nick
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w
Archive:
http://www.houseoffusion.com/groups/CF-Newbie/message.cfm/messageid:3320
Subscription: http://www.houseoffusion.com/groups/CF-Newbie/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
