cfdeveloper
Programmer
Hello everybody, I really need someone to help me here. I'm new to charting. I'm developing a graphical interface to report the no of jobs logged in a period. The x axis displays the period (days) and the y-axis displays the number of jobs logged in a given day.
The data is pulled from the jobs table. The job logged date value is stored in the logdateTime column. I've written a stored procedure to get the count of jobs logged in a given day. Here is the code.
<cfquery name="qJobsLogged" datasource="dbname">
EXEC countJobsLogged
@startdatedate = '2005/02/01',
@stopdate = '2005/02/28'
</cfquery>
<!-- sp code starts here -->
CREATE proc countJobsLogged @startdate datetime, @stopdate datetime
as
set nocount on
declare @x datetime,
@count int
create table #temp_report(
dtlogged varchar(15),
jobsCount int
)
select @x = @startdate
while @x <= @stopdate
begin
select @count = isnull(count(1),0) from call
where logdatetime >= @x + '00:00:00' and logdatetime <= @x + '23:59:59'
insert into #temp_report
values(convert(varchar(15),@x,106), @count)
select @x = dateadd(d, 1, @x)
end
select * from #temp_report
drop table #temp_report
set nocount off
GO
<!-- sp code ends here -->
The above sp loops from the user selected start date to the user selected end date and returns a resultset showing the no of jobs logged. Ex
JOBSCOUNT DTLOGGED
10 01 Feb 2005
20 02 Feb 2005
30 03 Feb 2005
Here is how I'm building the chart
<cfset scTo=evaluate(ArrayMax(ListToArray(valuelist(qJobsLogged.jobsCount)))+1)>
<cfchart show3d="yes" gridlines="#evaluate(scTo+1)#" labelformat="number" scalefrom="0" scaleto="#scTo#" format="jpg" xAxisTitle="log date" yAxisTitle="jobs logged" chartheight="300" chartwidth="450" showxgridlines="yes" showygridlines="yes" seriesplacement="default">
<cfchartseries type="bar" serieslabel="Jobs Logged">
<cfloop query="qJobsLogged">
<cfchartdata item="#qJobsLogged.DTLOGGED#" value="#qJobsLogged.jobsCount#">
</cfloop>
</cfchartseries>
</cfchart>
The above code builds the graph with the data returned by the query. So far, so good. I want each data point in the bar chart to give me a break-down of urgent jobs, critical jobs, normal jobs and jobs fixed on time.
I guess what I'm trying to say is take the results returned from the sp above and instead of just displaying a bar chart showing the count of jobs
logged in day, I want the chart to show me the urgent, critical, normal jobs for a particular day. I really hope this is making sense. The job priority information is in the jobPriority column in the job table.
Job Priorities
Urgent - 5
Critical - 4
Normal - 3
Ex:
Jobs logged on 01 Feb 2005 - 10.
Urgent jobs - 4, Critical jobs - 4, Normal Jobs - 2
Can somebody show me the sql to get this information and the code to build the chart
I would really appreciate your help
Best regards
cfcoder
The data is pulled from the jobs table. The job logged date value is stored in the logdateTime column. I've written a stored procedure to get the count of jobs logged in a given day. Here is the code.
<cfquery name="qJobsLogged" datasource="dbname">
EXEC countJobsLogged
@startdatedate = '2005/02/01',
@stopdate = '2005/02/28'
</cfquery>
<!-- sp code starts here -->
CREATE proc countJobsLogged @startdate datetime, @stopdate datetime
as
set nocount on
declare @x datetime,
@count int
create table #temp_report(
dtlogged varchar(15),
jobsCount int
)
select @x = @startdate
while @x <= @stopdate
begin
select @count = isnull(count(1),0) from call
where logdatetime >= @x + '00:00:00' and logdatetime <= @x + '23:59:59'
insert into #temp_report
values(convert(varchar(15),@x,106), @count)
select @x = dateadd(d, 1, @x)
end
select * from #temp_report
drop table #temp_report
set nocount off
GO
<!-- sp code ends here -->
The above sp loops from the user selected start date to the user selected end date and returns a resultset showing the no of jobs logged. Ex
JOBSCOUNT DTLOGGED
10 01 Feb 2005
20 02 Feb 2005
30 03 Feb 2005
Here is how I'm building the chart
<cfset scTo=evaluate(ArrayMax(ListToArray(valuelist(qJobsLogged.jobsCount)))+1)>
<cfchart show3d="yes" gridlines="#evaluate(scTo+1)#" labelformat="number" scalefrom="0" scaleto="#scTo#" format="jpg" xAxisTitle="log date" yAxisTitle="jobs logged" chartheight="300" chartwidth="450" showxgridlines="yes" showygridlines="yes" seriesplacement="default">
<cfchartseries type="bar" serieslabel="Jobs Logged">
<cfloop query="qJobsLogged">
<cfchartdata item="#qJobsLogged.DTLOGGED#" value="#qJobsLogged.jobsCount#">
</cfloop>
</cfchartseries>
</cfchart>
The above code builds the graph with the data returned by the query. So far, so good. I want each data point in the bar chart to give me a break-down of urgent jobs, critical jobs, normal jobs and jobs fixed on time.
I guess what I'm trying to say is take the results returned from the sp above and instead of just displaying a bar chart showing the count of jobs
logged in day, I want the chart to show me the urgent, critical, normal jobs for a particular day. I really hope this is making sense. The job priority information is in the jobPriority column in the job table.
Job Priorities
Urgent - 5
Critical - 4
Normal - 3
Ex:
Jobs logged on 01 Feb 2005 - 10.
Urgent jobs - 4, Critical jobs - 4, Normal Jobs - 2
Can somebody show me the sql to get this information and the code to build the chart
I would really appreciate your help
Best regards
cfcoder
