Heya
I've run into a problem with using an order by and group by and it's
throwing off the generation of a cfchart.
What works... to a point...
Query
SELECT it.issuetype, it.issuetypeid, TRUNC(DATE_SUBMITTED) AS
DATE_SUBMITTED, COUNT(*) AS tkts, ROUND(MAX(COUNT(*)) OVER () * 1.20)
AS maxscale
FROM MAIN m, ISSUES i, ISSUETYPES it
WHERE m.formid = i.formid
AND it.ISSUETYPEID = i.issuetypeid
AND TRUNC(m.DATE_SUBMITTED) BETWEEN ADD_MONTHS(TRUNC(SYSDATE),-1) AND
TRUNC(SYSDATE)
GROUP BY it.issuetype, it.issuetypeid,TRUNC(DATE_SUBMITTED)
CF code:
<cfchart
format="flash"
scalefrom="0"
scaleto="#qIssues.maxscale#"
gridlines="10"
chartWidth="1000"
chartHeight="320"
foregroundcolor="444444"
databackgroundcolor="FFFFFF"
fontsize="12"
>
<cfoutput query="qIssues" group="issuetype">
<cfif qIssues.issuetypeid eq 1>
<cfset color = "224466">
<cfelseif qIssues.issuetypeid eq 2>
<cfset color="002244">
<cfelseif qIssues.issuetypeid eq 3>
<cfset color = "557799">
<cfelseif qIssues.issuetypeid eq 4>
<cfset color = "99BBDD">
<cfelse>
<cfset color="cccccc">
</cfif>
<cfchartseries type="line" seriescolor="#color#"
serieslabel="#qIssues.issuetype#" paintstyle="plain"
markerstyle="none">
<cfoutput>
<cfchartdata
item="#dateformat(qIssues.date_submitted,'medium')#"
value="#qIssues.tkts#">
</cfoutput>
</cfchartseries>
</cfoutput>
</cfchart>
This displays fine except for the last column...
The display starts at Sept 27th, goes through October 25 and then
finishes with Sept 26th.
I've changed the order of the group by, added an order by and all this
does is funkify the chart..
adding a whole lot of chartseries that just repeat..
Anyways I've tried rearranging the group by, adding an order by and
nothing seems to work.
Anyone have any other ideas I can try
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Sams Teach Yourself Regular Expressions in 10 Minutes by Ben Forta
http://www.houseoffusion.com/banners/view.cfm?bannerid=40
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:182647
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=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
