I'm running the following query and after running the QoQ for SECONDCONN , I am not getting the desired output in my cfchart.
<!--- QoQ for FIRSTCONN --->
<!--- Master Query --->
<cfquery datasource = "XX.XX.X.XX" name="master1">
SELECT STR_TO_DATE(date_format(Timedetail,'%m-%d-%Y'),'%m-%d-%Y') as FIRSTCONN
, COUNT(Timedetail) as FIRSTOccurances
, EVENTS
FROM MyDatabase
WHERE EVENTS = "FIRST"
GROUP BY FIRSTCONN ;
</cfquery>
<!--- Detail Query --->
<!--- <cfdump var = "#master#"> --->
<cfquery dbtype="query" name="detail1">
SELECT *
FROM master1
WHERE FIRSTCONN >= <cfqueryparam value="#form.startdate#" cfsqltype="cf_sql_varchar">
AND FIRSTCONN < <cfqueryparam value="#dateAdd('d', 1,form.enddate)#" cfsqltype="cf_sql_varchar">;
</cfquery>
<!--- QoQ for SECONDCONN --->
<!--- Master Query --->
<cfquery datasource = "XX.XX.X.XX" name="master2">
SELECT STR_TO_DATE(date_format(Timedetail,'%m-%d-%Y'),'%m-%d-%Y') as SECONDCONN
, COUNT(Timedetail) as SECONDOccurances
, EVENTS
FROM MyDatabase
WHERE EVENTS = "SECOND"
GROUP BY SECONDCONN ;
</cfquery>
<!--- Detail Query --->
<!--- <cfdump var = "#master#"> --->
<cfquery dbtype="query" name="detail2">
SELECT *
FROM master2
WHERE SECONDCONN >= <cfqueryparam value="#form.startdate#" cfsqltype="cf_sql_varchar">
AND SECONDCONN < <cfqueryparam value="#dateAdd('d', 1,form.enddate)#" cfsqltype="cf_sql_varchar">;
</cfquery>
<cfchart format="flash" chartwidth="1000" chartheight="500" scalefrom="0" scaleto="50000" xAxisTitle="Dates" yaxistitle="Number of Connections">
<cfchartseries query="detail1" type="line" itemColumn="FIRSTCONN" valueColumn="FIRSTOccurances" >
<cfchartseries query="detail2" type="line" itemColumn="SECONDCONN" valueColumn="SECONDOccurances" >
</cfchartseries>
</cfchart>
On X Axis, the chart is displaying the correct startdate
but it stops displaying the dates after middle point somewhere and rest of the line chart is displayed without any dates mentioned on the x-axis. What could be the reason? The dates are displaying properly when I ran just one query, that is QoQ for FIRSTCONN
.
attached shows the output with only one query , that is QoQ for FIRSTCONN
is for the both and showing undesirable output.
Another point I noticed is that when I run the query for only SECONDCONN, I see an output without any dates mentioned on the x-axis. What could be th reason? I have attached below for the reference.there is no change in the query.
The cfchart code I'm using is as follows:
<cfchart format="flash"
chartwidth="1000"
chartheight="500"
scalefrom="0"
scaleto="50000"
xAxisTitle="Date"
yaxistitle="Number of Connections"
showLegend = "yes"
showMarkers = "yes"
sortXAxis= "yes"
tipStyle="MouseDown"
>
<cfchartseries query="detail2" type="line" itemColumn="SECONDCONN " valueColumn="SECONDOccurances" >
</cfchartseries>
</cfchart>
The cfdump of second query is as follows:
query
RESULTSET
query
SECONDCONN SECONDOCCURANCES EVENTS
1 {ts '2013-06-24 00:00:00'} 556 SECOND
2 {ts '2013-06-25 00:00:00'} 2710 SECOND
3 {ts '2013-06-26 00:00:00'} 2854 SECOND
4 {ts '2013-06-27 00:00:00'} 6348 SECOND
5 {ts '2013-06-28 00:00:00'} 4285 SECOND
6 {ts '2013-06-29 00:00:00'} 2843 SECOND
7 {ts '2013-06-30 00:00:00'} 875 SECOND
8 {ts '2013-07-01 00:00:00'} 4033 SECOND
9 {ts '2013-07-02 00:00:00'} 3211 SECOND
10 {ts '2013-07-03 00:00:00'} 2882 SECOND
11 {ts '2013-07-04 00:00:00'} 978 SECOND
12 {ts '2013-07-05 00:00:00'} 1727 SECOND
13 {ts '2013-07-06 00:00:00'} 811 SECOND
14 {ts '2013-07-07 00:00:00'} 522 SECOND
15 {ts '2013-07-08 00:00:00'} 2556 SECOND
16 {ts '2013-07-09 00:00:00'} 1160 SECOND
17 {ts '2013-07-10 00:00:00'} 8580 SECOND
18 {ts '2013-07-11 00:00:00'} 2630 SECOND
19 {ts '2013-07-16 00:00:00'} 12 SECOND
Please let me know if I can answer more questions.
<cfchart>
first, specifically the url attribute. – Leigh