cseink
asked on
coldfusion graphing help
I want to graph this data but am unfamiliar with cfml graphing. X-AXIS would be [ED DOOR DT] AND y-axis would be a integer. The line data is CT30,CT60,CT90,CT120,CT120 PLUS FOR EACH DAY.
code so far..
<!-- Get the raw data from the database. -->
<!-- cfset getdate1 = FORM.one -->
<!-- cfset getdate2 = FORM.two > -->
<cfquery name="GetSpace" datasource="EDSTATS">
SELECT (SELECT COUNT(*)
FROM edstats
WHERE [ED DOOR DT] <> '' AND [trans to flr dt] <> '' AND datediff(mi, [dbo].ConvertToDateTime([E D DOOR DT], [ED DOOR TM]),
[dbo].ConvertToDateTime([T RANS TO FLR DT], [TRANS TO FLR TM])) < 30) AS ct30,
(SELECT COUNT(*)
FROM edstats
WHERE [ED DOOR DT] <> '' AND [trans to flr dt] <> '' AND datediff(mi, [dbo].ConvertToDateTime([E D DOOR DT], [ED DOOR TM]),
[dbo].ConvertToDateTime([T RANS TO FLR DT], [TRANS TO FLR TM])) BETWEEN 30 AND 60) AS ct60,
(SELECT COUNT(*)
FROM edstats
WHERE [ED DOOR DT] <> '' AND [trans to flr dt] <> '' AND datediff(mi, [dbo].ConvertToDateTime([E D DOOR DT], [ED DOOR TM]),
[dbo].ConvertToDateTime([T RANS TO FLR DT], [TRANS TO FLR TM])) BETWEEN 60 AND 90) AS ct90,
(SELECT COUNT(*)
FROM edstats
WHERE [ED DOOR DT] <> '' AND [trans to flr dt] <> '' AND datediff(mi, [dbo].ConvertToDateTime([E D DOOR DT], [ED DOOR TM]),
[dbo].ConvertToDateTime([T RANS TO FLR DT], [TRANS TO FLR TM])) BETWEEN 90 AND 120) AS ct120,
(SELECT COUNT(*)
FROM edstats
WHERE [ED DOOR DT] <> '' AND [trans to flr dt] <> '' AND datediff(mi, [dbo].ConvertToDateTime([E D DOOR DT], [ED DOOR TM]),
[dbo].ConvertToDateTime([T RANS TO FLR DT], [TRANS TO FLR TM])) > 120) AS ct120plus
</cfquery>
<html>
<head>
<title>charts</title>
</head>
<body>
<h1>ed gRAPHS </h1>
<!--- Line chart--->
<cfset variables.colorList = "red,green,blue,yellow,bla ck,##ff990 0,##ccff00 ,##999999, ##FF00FF,# #CC99FF,## 666633,##9 9ccff,##F9 D031,##800 040,##3366 00"> <!---- list your colors ---->
<cfchart
xAxisTitle="Date"
yAxisTitle="#"
chartHeight = "500"
chartWidth = "500"
scaleto="100"
font="Arial"
gridlines="10"
showXGridlines="yes"
showYGridlines="yes"
showborder="no"
show3d="no"
showlegend="yes"
sortxaxis="yes"
showmarkers="yes"
format="jpg"
>
<cfoutput query="GETspace" group="[ed door dt]">
<cfset variables.thisColor = listFirst(variables.colorL ist)>
<cfset variables.colorList = listRest(variables.colorLi st)>
<cfchartseries
type="LINE"
seriesColor="#variables.th isColor#"
paintStyle="plain"
seriesLabel="#getspace.CT1 20#">
<cfoutput>
<cfchartdata item="" value="">
</cfoutput>
</cfchartseries>
</cfoutput>
</cfchart>
<br>
</body>
</html>
code so far..
<!-- Get the raw data from the database. -->
<!-- cfset getdate1 = FORM.one -->
<!-- cfset getdate2 = FORM.two > -->
<cfquery name="GetSpace" datasource="EDSTATS">
SELECT (SELECT COUNT(*)
FROM edstats
WHERE [ED DOOR DT] <> '' AND [trans to flr dt] <> '' AND datediff(mi, [dbo].ConvertToDateTime([E
[dbo].ConvertToDateTime([T
(SELECT COUNT(*)
FROM edstats
WHERE [ED DOOR DT] <> '' AND [trans to flr dt] <> '' AND datediff(mi, [dbo].ConvertToDateTime([E
[dbo].ConvertToDateTime([T
(SELECT COUNT(*)
FROM edstats
WHERE [ED DOOR DT] <> '' AND [trans to flr dt] <> '' AND datediff(mi, [dbo].ConvertToDateTime([E
[dbo].ConvertToDateTime([T
(SELECT COUNT(*)
FROM edstats
WHERE [ED DOOR DT] <> '' AND [trans to flr dt] <> '' AND datediff(mi, [dbo].ConvertToDateTime([E
[dbo].ConvertToDateTime([T
(SELECT COUNT(*)
FROM edstats
WHERE [ED DOOR DT] <> '' AND [trans to flr dt] <> '' AND datediff(mi, [dbo].ConvertToDateTime([E
[dbo].ConvertToDateTime([T
</cfquery>
<html>
<head>
<title>charts</title>
</head>
<body>
<h1>ed gRAPHS </h1>
<!--- Line chart--->
<cfset variables.colorList = "red,green,blue,yellow,bla
<cfchart
xAxisTitle="Date"
yAxisTitle="#"
chartHeight = "500"
chartWidth = "500"
scaleto="100"
font="Arial"
gridlines="10"
showXGridlines="yes"
showYGridlines="yes"
showborder="no"
show3d="no"
showlegend="yes"
sortxaxis="yes"
showmarkers="yes"
format="jpg"
>
<cfoutput query="GETspace" group="[ed door dt]">
<cfset variables.thisColor = listFirst(variables.colorL
<cfset variables.colorList = listRest(variables.colorLi
<cfchartseries
type="LINE"
seriesColor="#variables.th
paintStyle="plain"
seriesLabel="#getspace.CT1
<cfoutput>
<cfchartdata item="" value="">
</cfoutput>
</cfchartseries>
</cfoutput>
</cfchart>
<br>
</body>
</html>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
How can I get the date to show up on the bottom?? Right now it showing a number from 0 -60 for each day..
<cfquery name="GetSpace" datasource="EDSTATS">
SELECT [ED DOOR DT],
(SELECT COUNT(*)
FROM edstats
WHERE [ED DOOR DT] = e.[ED DOOR DT] AND [ED DOOR DT] <> '' AND [trans to flr dt] <> '' AND datediff(mi,
[dbo].ConvertToDateTime([E D DOOR DT], [ED DOOR TM]), [dbo].ConvertToDateTime([T RANS TO FLR DT], [TRANS TO FLR TM])) < 30)
AS ct30,
(SELECT COUNT(*)
FROM edstats
WHERE [ED DOOR DT] = e.[ED DOOR DT] AND [ED DOOR DT] <> '' AND [trans to flr dt] <> '' AND datediff(mi,
[dbo].ConvertToDateTime([E D DOOR DT], [ED DOOR TM]), [dbo].ConvertToDateTime([T RANS TO FLR DT], [TRANS TO FLR TM])) BETWEEN
30 AND 60) AS ct60,
(SELECT COUNT(*)
FROM edstats
WHERE [ED DOOR DT] = e.[ED DOOR DT] AND [ED DOOR DT] <> '' AND [trans to flr dt] <> '' AND datediff(mi,
[dbo].ConvertToDateTime([E D DOOR DT], [ED DOOR TM]), [dbo].ConvertToDateTime([T RANS TO FLR DT], [TRANS TO FLR TM])) BETWEEN
60 AND 90) AS ct90,
(SELECT COUNT(*)
FROM edstats
WHERE [ED DOOR DT] = e.[ED DOOR DT] AND [ED DOOR DT] <> '' AND [trans to flr dt] <> '' AND datediff(mi,
[dbo].ConvertToDateTime([E D DOOR DT], [ED DOOR TM]), [dbo].ConvertToDateTime([T RANS TO FLR DT], [TRANS TO FLR TM])) BETWEEN
90 AND 120) AS ct120,
(SELECT COUNT(*)
FROM edstats
WHERE [ED DOOR DT] = e.[ED DOOR DT] AND [ED DOOR DT] <> '' AND [trans to flr dt] <> '' AND datediff(mi,
[dbo].ConvertToDateTime([E D DOOR DT], [ED DOOR TM]), [dbo].ConvertToDateTime([T RANS TO FLR DT], [TRANS TO FLR TM])) > 120)
AS ct120plus
FROM EDSTATS e
WHERE ([ED DOOR DT] BETWEEN '05/01/2007' AND ' 06 / 30 / 2007 ')
GROUP BY [ED DOOR DT]
ORDER BY [ED DOOR DT]
</cfquery>
<cfset variables.colorList = "red,green,blue,yellow,bla ck,##ff990 0,##ccff00 ,##999999, ##FF00FF,# #CC99FF,## 666633,##9 9ccff,##F9 D031,##800 040,##3366 00"> <!---- list your colors ---->
<cfchart
xAxisTitle="Date"
yAxisTitle="Counts"
chartHeight = "800"
chartWidth = "800"
scaleto="100"
font="Arial"
gridlines="10"
showXGridlines="yes"
showYGridlines="yes"
showborder="no"
show3d="no"
sortxaxis="no"
showmarkers="yes"
format="jpg"
>
<cfchartseries
type="line"
seriesLabel=">120 "
query="getspace"
paintstyle="plain"
valueColumn="ct120plus"
itemColumn="[ed door dt]"
seriesColor="red"
/>
<cfchartseries
type="line"
seriesLabel="90-120"
query="getspace"
paintstyle="plain"
valueColumn="ct120"
itemColumn="[ed door dt]"
seriesColor="blue"
/>
<cfchartseries
type="line"
seriesLabel="60-90"
query="getspace"
paintstyle="plain"
valueColumn="ct90"
itemColumn="[ed door dt]"
seriesColor="green"
/>
<cfchartseries
type="line"
seriesLabel="30-60"
query="getspace"
paintstyle="plain"
valueColumn="ct60"
itemColumn="[ed door dt]"
seriesColor="black"
/>
<cfchartseries
type="line"
seriesLabel="0-30"
query="getspace"
paintstyle="plain"
valueColumn="ct30"
itemColumn="[ed door dt]"
seriesColor="yellow"
/>
</cfchart>
<cfquery name="GetSpace" datasource="EDSTATS">
SELECT [ED DOOR DT],
(SELECT COUNT(*)
FROM edstats
WHERE [ED DOOR DT] = e.[ED DOOR DT] AND [ED DOOR DT] <> '' AND [trans to flr dt] <> '' AND datediff(mi,
[dbo].ConvertToDateTime([E
AS ct30,
(SELECT COUNT(*)
FROM edstats
WHERE [ED DOOR DT] = e.[ED DOOR DT] AND [ED DOOR DT] <> '' AND [trans to flr dt] <> '' AND datediff(mi,
[dbo].ConvertToDateTime([E
30 AND 60) AS ct60,
(SELECT COUNT(*)
FROM edstats
WHERE [ED DOOR DT] = e.[ED DOOR DT] AND [ED DOOR DT] <> '' AND [trans to flr dt] <> '' AND datediff(mi,
[dbo].ConvertToDateTime([E
60 AND 90) AS ct90,
(SELECT COUNT(*)
FROM edstats
WHERE [ED DOOR DT] = e.[ED DOOR DT] AND [ED DOOR DT] <> '' AND [trans to flr dt] <> '' AND datediff(mi,
[dbo].ConvertToDateTime([E
90 AND 120) AS ct120,
(SELECT COUNT(*)
FROM edstats
WHERE [ED DOOR DT] = e.[ED DOOR DT] AND [ED DOOR DT] <> '' AND [trans to flr dt] <> '' AND datediff(mi,
[dbo].ConvertToDateTime([E
AS ct120plus
FROM EDSTATS e
WHERE ([ED DOOR DT] BETWEEN '05/01/2007' AND ' 06 / 30 / 2007 ')
GROUP BY [ED DOOR DT]
ORDER BY [ED DOOR DT]
</cfquery>
<cfset variables.colorList = "red,green,blue,yellow,bla
<cfchart
xAxisTitle="Date"
yAxisTitle="Counts"
chartHeight = "800"
chartWidth = "800"
scaleto="100"
font="Arial"
gridlines="10"
showXGridlines="yes"
showYGridlines="yes"
showborder="no"
show3d="no"
sortxaxis="no"
showmarkers="yes"
format="jpg"
>
<cfchartseries
type="line"
seriesLabel=">120 "
query="getspace"
paintstyle="plain"
valueColumn="ct120plus"
itemColumn="[ed door dt]"
seriesColor="red"
/>
<cfchartseries
type="line"
seriesLabel="90-120"
query="getspace"
paintstyle="plain"
valueColumn="ct120"
itemColumn="[ed door dt]"
seriesColor="blue"
/>
<cfchartseries
type="line"
seriesLabel="60-90"
query="getspace"
paintstyle="plain"
valueColumn="ct90"
itemColumn="[ed door dt]"
seriesColor="green"
/>
<cfchartseries
type="line"
seriesLabel="30-60"
query="getspace"
paintstyle="plain"
valueColumn="ct60"
itemColumn="[ed door dt]"
seriesColor="black"
/>
<cfchartseries
type="line"
seriesLabel="0-30"
query="getspace"
paintstyle="plain"
valueColumn="ct30"
itemColumn="[ed door dt]"
seriesColor="yellow"
/>
</cfchart>
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks so much for your help