CF Charting between dates

I am having a problem creating a list that I can chart for an employees work week. All I am trying to do is chart the hours he has put in for the week and NOT going past 7 days. Here is what I have so far.
<cfset YourDate = #DATEFORMAT(NOW(), 'mm-dd-yyyy')#>
<cfset mytoday = "#DayofWeekAsString(DayOfWeek(yourDate))#">
 <cfif #mrtoday# eq "Sunday">
     <cfset YourDate = #DATEFORMAT(NOW(), 'mm-dd-yyyy')#>
     <cfset EndDate = #DATEFORMAT(NOW()-7, 'mm-dd-yyyy')#>
 <cfelseif #mrtoday# eq "Saturday">
     <cfset YourDate = #DATEFORMAT(NOW()+1, 'mm-dd-yyyy')#>
     <cfset EndDate = #DATEFORMAT(NOW()-7, 'mm-dd-yyyy')#>
</cfif>
<cfquery name="gettime" datasource="DB">
select emp_time from timesheet where emp_id = #emp_id# and emp_time <> '' and mydate BETWEEN #createodbcdate(Yourdate)# and #createodbcdate(EndDate)#
 </cfquery>
<cfset myweek = "">
<cfloop query="gettime">
   <cfset myweek = "#gettime.emp_time#">
</cfloop>
<cfoutput>#myweek#</cfoutput>(test)
<cf_activechart
      width="200"
      title="Employee Weekly Activity"
      values="#myweek#"
      legend="Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday"
      urls="http://www.somesite.net">

All I am wanting is to graph the field on what ever day for the week. Employee enters various times throughout the day and week. He maybe mailed something and charges 1 hour on monday, and later charges 2 hours for copying. Tuesday he works 8 hours as one entry and so on. Can this be done?
dudeatworkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gdemariaCommented:

What is this line supposed to do?

<cfloop query="gettime">
   <cfset myweek = "#gettime.emp_time#">
</cfloop>
<cfoutput>#myweek#</cfoutput>(test)

Looks like you loop over every time entry and OVERWRITE the myweek variable with the emp_time.   At the end, the my_week variable will contain the LAST record's value.

Did you mean to add them up?  

Or perhaps the </CFLOOP>  should be Below the <cf_activeChart ?

0
dudeatworkAuthor Commented:
I know it's over writting the loop but I can't seem to figure out how to add the hours per eveyday of the week for one employee. That loop is crap, a vain last effort before bed. I just want to add the value per day and chart the week on a graph or chart. Also, that particular chart sucks, is there a better looking one you know of?
0
gdemariaCommented:

You need two values in your query;  one for the number of hours worked, and one for the date/time of when the person did the work.  I think, in your case, these are emp_time (number of hours) and myDate (the date worked).
 
You can add the time and group by date so the query does the work for you...  the result is the total number of hours worked on each date...

select  sum(emp_time) as DaysTotal
        ,  convert(myDate, 101) as myDate
from timesheet
where emp_id = #emp_id#
and emp_time <> ''
and mydate BETWEEN #createodbcdate(Yourdate)# and #createodbcdate(EndDate)#
group by  convert(myDate, 101)


0
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

dudeatworkAuthor Commented:
It's the organization of all the code I am having problems with. Thats helps, but it doesn't work because of the logic.  Thoughts?
0
gdemariaCommented:

Sure, just use the query with CFCHART..

http://livedocs.adobe.com/coldfusion/6.1/htmldocs/tags-a13.htm#wp2741830

<cfchart format="png" scalefrom="-50" scaleTo="100" gridlines="7">
          <cfchartseries type="line" query="getTime"
              itemColumn="DaysTotal"   valueColumn="myDate">
</cfchart>
0
dudeatworkAuthor Commented:
None worked and I am too new at cfcharting. Argh!!
0
gdemariaCommented:

 ok, well, why don't you post your updated code and we'll try to see what's happening
0
dudeatworkAuthor Commented:
<cfset YourDate = #DATEFORMAT(NOW(), 'mm-dd-yyyy')#>
<cfset mytoday = "#DayofWeekAsString(DayOfWeek(yourDate))#">
 <cfif #mrtoday# eq "Sunday">
     <cfset YourDate = #DATEFORMAT(NOW(), 'mm-dd-yyyy')#>
     <cfset EndDate = #DATEFORMAT(NOW()-7, 'mm-dd-yyyy')#>
 <cfelseif #mrtoday# eq "Saturday">
     <cfset YourDate = #DATEFORMAT(NOW()+1, 'mm-dd-yyyy')#>
     <cfset EndDate = #DATEFORMAT(NOW()-7, 'mm-dd-yyyy')#>
</cfif>
<cfquery name="gettime" datasource="DB">
select  sum(emp_time) as DaysTotal
        ,  convert(myDate, 101) as myDate
from timesheet
where emp_id = #emp_id#
and emp_time <> ''
and mydate BETWEEN #createodbcdate(Yourdate)# and #createodbcdate(EndDate)#
group by  convert(myDate, 101)
 </cfquery>
<cfset myweek = "">
<cfloop query="gettime">
   <cfset myweek = "#gettime.emp_time#">
</cfloop>
<cfoutput>#myweek#</cfoutput>(test)
<cfchart format="png" scalefrom="-50" scaleTo="100" gridlines="7">
          <cfchartseries type="line" query="getTime"
              itemColumn="DaysTotal"   valueColumn="myDate">
</cfchart>
I just want to show what they have worked/hours up till "today" from the begining of the week. I am confused but really would like to see a charting of their time.
0
gdemariaCommented:

 Ok, thanks.  Let's go step by step, if that's Ok.   What do the results of that query look like?  If possible, can you post that as well?    ( to do so, you can either pull the query from the CF dubug information at the bottom of the screen OR  you can  copy the SELECT and wrap it in CFOUTPUT to it shows on the screen; then paste it into your database and see what comes of it )

Once we are sure the query is pulling the right data and it looks good, we will look at the next part, the graph...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.