views:

44

answers:

3

I have a couple of queries which pull data for use in a graph.

<cfquery name='clusterPrivateReferrals'  dbtype="query">
SELECT organisationName, count(messageID)*1000/listSize as msgCount
FROM clusterReferrals
WHERE datecreated>#refRateStartDate#
AND refTypeID=3
GROUP BY organisationName, listSize
</cfquery>

<cfquery name='clusterNHSReferrals'  dbtype="query">
SELECT organisationName, count(messageID)*1000/listSize as msgCount
FROM clusterReferrals
WHERE datecreated>#refRateStartDate#
AND refTypeID<>3
GROUP BY organisationName, listSize
</cfquery>

The graph code is

<cfchart format="flash" title="Cluster referrals per 1000 patients from #dateformat(refRateStartDate, 'dd-mmm-yy')#" chartWidth="470" chartHeight="380" fontSize="12" style="chart.xml" seriesPlacement = "stacked" showLegend = "yes">
    <cfchartseries type="bar" seriescolor="##FFD800" seriesLabel="Private" query="clusterPrivateReferrals" valueColumn="msgCount" ItemColumn="organisationName">
    </cfchartseries>
    <cfchartseries type="bar" seriescolor="##F47D30" seriesLabel="NHS" query="clusterNHSReferrals" valueColumn="msgCount" ItemColumn="organisationName">
    </cfchartseries>
</cfchart>

this gives me the following graph

alt text

How do I get the data displayed sorted by the total of the stacked elements?

@ Ben

That got me on the right track, I didnt previously know QOQ could combine 2 completely different queries

<cfquery name='clusterPrivateReferrals'  dbtype="query">
SELECT organisationName, count(messageID)*1000/listSize as privateRate
FROM allReferrals
WHERE datecreated>#refRateStartDate#
AND refTypeID=3
GROUP BY organisationName, listSize
</cfquery>

<cfquery name='clusterNHSReferrals'  dbtype="query">
SELECT organisationName, count(messageID)*1000/listSize as nhsRate
FROM allReferrals
WHERE datecreated>#refRateStartDate#
AND refTypeID<>3
GROUP BY organisationName, listSize
</cfquery>

<cfquery name="stackOrder" dbtype="query">
    select clusterPrivateReferrals.privateRate,
        clusterNHSReferrals.nhsRate,
        clusterPrivateReferrals.organisationName,
        (clusterPrivateReferrals.privateRate + clusterNHSReferrals.nhsRate) as totalRate
    from clusterPrivateReferrals, clusterNHSReferrals
    WHERE clusterNHSReferrals.organisationName = clusterPrivateReferrals.organisationName
    order by totalRate desc
</cfquery> 
A: 

Maybe add intermediate QoQ with filtering only by date? Something like this (can not test, so it may need some fixes):

<cfquery name='clusterCombinedReferrals'  dbtype="query">
SELECT organisationName, messageID, listSize, count(messageID)*1000/listSize as totalMsgCount
FROM clusterReferrals
WHERE datecreated>#refRateStartDate#
GROUP BY organisationName, listSize
</cfquery>

After that update your existing queries to include select from clusterCombinedReferrals and order by totalMsgCount at first place, also drop the filtering by date as it already applied.

Sergii
A: 

I don't think you can without first outputting the queries to a structure and then sorting it and outputting the data from that with the cfchartdata tag inside each cfchartseries.

Something like this, maybe. I did this locally and it worked, but then I tried to convert the code to work with your queries and column names, so it might not work off a straight copy and paste. (But it might!) It also assumes that the length of the two queries will always be the same. If that isn't true, you might need to code around that.

<cfset data = {}>

<cfloop from="1" to="#clusterPrivateReferrals.recordCount#" index="x">

  <cfset structInsert(data, clusterPrivateReferrals["organisationName"][x], {})>

  <cfset data['#clusterPrivateReferrals["organisationName"][x]#'].private = clusterPrivateReferrals["msgCount"][x]>

  <cfset data['#clusterNHSReferrals["organisationName"][x]#'].nhs = clusterPrivateReferrals["msgCount"][x]>

  <cfset data['#clusterPrivateReferrals["organisationName"][x]#'].total = data['#clusterNHSReferrals["organisationName"][x]#'].private + data['#clusterNHSReferrals["organisationName"][x]#'].nhs>

</cfloop>

<cfset sorted = structSort(data, "numeric", "desc", "total")>

<cfchart format="flash" title="data" chartWidth="470" chartHeight="380" fontSize="12" seriesPlacement = "stacked" showLegend = "yes">

    <cfchartseries type="bar" seriescolor="##FFD800" seriesLabel="Private">
        <cfloop from="1" to="#arrayLen(datas)#" index="x">
            <cfchartdata item="#sorted[x]#" value="#data['#sorted[x]#'].private#">
        </cfloop>
    </cfchartseries>

    <cfchartseries type="bar" seriescolor="##F47D30" seriesLabel="NHS">
        <cfloop from="1" to="#arrayLen(datas)#" index="x">
            <cfchartdata item="#sorted[x]#" value="#data['#sorted[x]#'].nhs#">
        </cfloop>
    </cfchartseries>

</cfchart>
Jason
Yes, putting into arrays would work, but Ben's QOQ solution above is slicker
Saul
+1  A: 

The simplest way would be to use a QofQ:

<cfquery name="stackOrder" dbtype="query">
    select clusterPrivateReferrals.msgCount as privateReferrals,
        clusterNHSReferrals.msgCount as NHSReferrals,
        clusterPrivateReferrals.organizationName
    from clusterPrivateReferrals
    join clusterNHSReferrals on clusterNHSReferrals.organizationName = clusterPrivateReferrals.organizationName
    order by (privateReferrals+privateReferrals) desc
</cfquery>

I've not tested this, so you may need to tweak it a little.

Now, you should be able to use the two Referrals columns as the data columns for the graph.

Ben Doom
I wasn't aware you could do that sort of thing in a QoQ but now that I see it, I don't see why it wouldn't work. For some reason I had it in my head that you could do a QoQ with a single originating query.
Jason
I've used QofQ to join data from across datasources before. It's handier than people tend to give it credit for.
Ben Doom
That nearly does it Ben, and got me onto the right track. See above for working code.
Saul