Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Calculating Time Difference in CFM

Posted on 2004-08-10
21
Medium Priority
?
294 Views
Last Modified: 2013-12-24
I have an access dbase with a logintime and a logouttime and a date field.  I need to calculate the difference from the logouttime to the logintime (basically how long was the employee logged out of the phone).  The table might look something like this:

Employee - LoginTime - LogoutTime - ReasonCode - Date
Employee1 - 8:00 AM - 9:00 AM - 8 - 08/10/2004
Employee1 - 9:05 AM - 12:00 PM - 4 - 08/10/2004
Employee1 - 12:58 PM - 3:00 PM - 8 - 08/10/2004
Employee1 - 3:15 PM - 5:00 PM - 9 - 08/10/2004
or
Employee2 - 4:00 PM - 9:00 PM - 4 - 08/10/2004
Employee2 - 10:00 PM - 12:30 AM - 9 - 08/11/2004

Now, what it should like on the ColdFusion page should be this:

Employee - Login Time - Logout Time - Reason - Total Logged Out (h:m)
Employee1 - 8:00 AM - 9:00 AM - Personal - 0:05
Employee1 - 9:05 AM - 12:00 PM - Lunch - 0:58
Employee1 - 12:58 PM - 3:00 PM - Personal - 0:15
Employee1 - 3:15 PM - 5:00 PM - End of Shift -
or
Employee2 - 4:00 PM - 9:00 PM - Lunch - 1:00
Employee2 - 10:00 PM - 12:30 AM - End of Shift -

I used the second example because I didn't know if it made a differnce if the times went to the next day.

Is there any way to do this?  

0
Comment
Question by:Lee R Liddick Jr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 5
  • 4
21 Comments
 
LVL 35

Assisted Solution

by:mrichmon
mrichmon earned 1000 total points
ID: 11765321
I personally don't like the way you are displaying it since it seems that the 0:05 is associated with 8:00-9:00 when really it is the time between shifts.

But in any case what you are doing is a bit difficult since it is comparing records.

You could have CF do it.

<cfoutput query="yourquery">
#employee# - #logintime# - #logouttime# - #reason# - #eval(logointime[currentrow +1] - logouttime)#
</cfoutput>

Or you may need datediff instead of eval - it depends on the way you are stoing the data on how you can do the subtractions
0
 

Author Comment

by:Lee R Liddick Jr
ID: 11775292
I can't help how it looks because of the way it imports from the text file.  The login and logout data is on the same line so that is the way it is displayed.  If I could get it to look like this:

Employee - Logout Time - Login Time - Reason - Total Logged Out (h:m)
Employee1 -               - 8:00 AM - Personal - 0:05
Employee1 - 9:00 AM - 9:05 AM - Personal - 0:05
Employee1 - 12:00 PM - 12:58 PM - Lunch - 0:58
Employee1 - 3:00 PM - 3:15 PM - Personal - 0:15
Employee1 - 5:00 PM -             - End of Shift -

...then I would; because that makes much more sense (I'm with ya).

I tried your suggestion but that didn't work...it said that eval was undefined.  Probably because on the last line it is looking for a line after it but there is none because the employee was logged out and done for the day and didn't log back in until tomorrow morning.  I appreciate your thoughts though.  I am going to let posted a few more days to see if anyone else wants to add some words of wisdom.  Thanks mrichmon!
0
 

Accepted Solution

by:
brimcBAM earned 1000 total points
ID: 11776228
When you read this from a textfile, do you read line by line, dump it into a struct, or throw it into a database.  This will affect how you can report on it.  

I would write a function that would allow you to look ahead to the next record and see when the next login time was to calculate the time the employee has been logged out.  Once I know how your data is stored, I can help you out a little more with this.
0
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
LVL 35

Expert Comment

by:mrichmon
ID: 11776342
Too bad about the input, but you could adjust how it is displayed after it is read in so that it displays the way you indicated.

whoops eval is javascript.  It should be evaluate for CF.

Also you need a test to make sure that if it is the last record you don't try to access it.

<cfif currentRow NEQ recordcount>
#evaluate(logointime[currentrow +1] - logouttime)
<cfelse>
- End Of Shift -
</cfif>
0
 

Author Comment

by:Lee R Liddick Jr
ID: 11776429
The text file is line by line and looks like this:

"25003"      09:29      09:29      "8/10/2004"      3
"25003"      09:39      10:19      "8/10/2004"      4
"25003"      11:11      12:02      "8/10/2004"      1
"25003"      12:17      14:35      "8/10/2004"      2

This then gets imported into an Access dbase which then looks like this:

Record     Extension      LoginTime      LogoutTime      Date      ReasonCode
1              25003      9:29 AM      9:29 AM                8/10/2004      3
2              25003      9:39 AM      10:19 AM                8/10/2004      4
3              25003      11:11 AM      12:02 PM                8/10/2004      1
4              25003      12:17 PM      2:35 PM                8/10/2004      2

If the reason code is a 9, that means it was the end of their shift and they go home.
0
 

Author Comment

by:Lee R Liddick Jr
ID: 11776496
And this is the way the table is setup:

Field Name          Data Type
Extension            Text
LoginTime           Date/Time
LogoutTime         Date/Time
Date                   Date/Time
ReasonCode        Text
0
 

Expert Comment

by:brimcBAM
ID: 11776515
Ok, so I would write a function.

<cffunction name="getTimeDiff">
  <cfargument name="currentrecordid">
  <cfargument name="currentlogoutTime">

  <cfquery name ="nextrecord">
   select .... from ...
    where record = #currentrecordid + 1#
  </cfquery>

  Calculate difference: arguments.currentLogOutTime - nextrecord.logintime
  ... you may have to do a timeFormat too...

  <cfreturn time>
</cffunction>

I would call your record similar to this now ...

<cfoutput query="yourquery">
#employee# - #logintime# - #logouttime# - #reason# - <cfif reason IS NOT 9>#getTimeDiff(recordid,logouttime)#</cfif>
</cfoutput>
0
 

Author Comment

by:Lee R Liddick Jr
ID: 11776763
My query 'agtlogdata' is located at the top of the page and this is the way my table to display the results in cfm is:

<TABLE cellspacing="2" cellpadding="2" border="0">
     <TR bgcolor="333333" class="tinysilver">
          <TD bgcolor="FFFFFF"></td>
          <TD align="center"><b>Login Time</b></TD>
          <TD align="center"><b>Logout Time</b></TD>
          <TD align="center"><b>Logout Date</b></TD>
          <TD align="center"><b>Logout Reason</b></TD>
          <TD align="center"><b>Logout Duration</b></TD>                                    
     </TR>
<CFOUTPUT query="agtlogdata">
<CFIF Date EQ #LTrim(CreateODBCDate(dateformat(now(),'m/d/yyyy')))# OR Date IS "">
     <TR class="tiny">
          <TD></TD>
          <TD align="center">#TimeFormat(logintime, "hh:mm tt")#</TD>
          <TD align="center">#TimeFormat(logouttime, "hh:mm tt")#</TD>                              
          <TD align="center">#DateFormat(date, 'm/d')#</TD>
          <TD align="center">#reasoncodename#</TD>                        
          <TD align="center"><cfif reason IS NOT 9>#getTimeDiff(recordid,logouttime)#</cfif></TD>
     </TR>
<CFELSE>
</CFIF>
</CFOUTPUT>
</TABLE>

Where would I put the function and argument and new query statements?
0
 

Author Comment

by:Lee R Liddick Jr
ID: 11777148
It's telling me:

Variable RECORDID is undefined.
0
 

Expert Comment

by:brimcBAM
ID: 11777463
Record ID was my generic name for the unique id associated with each record.

The cffunction statement can go anywhere on the page.  It may be easier to follow if it is included at the top of your page.
0
 

Author Comment

by:Lee R Liddick Jr
ID: 11777825
This is what I have:

<TABLE cellspacing="2" cellpadding="2" border="0">
     <TR bgcolor="333333" class="tinysilver">
          <TD bgcolor="FFFFFF"></td>
          <TD align="center"><b>Login Time</b></TD>
          <TD align="center"><b>Logout Time</b></TD>
          <TD align="center"><b>Logout Date</b></TD>
          <TD align="center"><b>Logout Reason</b></TD>
          <TD align="center"><b>Logout Duration</b></TD>
     </TR>
<cffunction name="getTimeDiff">
  <cfargument name="currentrecordid">
  <cfargument name="currentlogoutTime">
  <cfquery datasource="harprod" name="nextrecord">
          SELECT *
          FROM qsel_CMSLogData_Info
          WHERE record = #currentrecordid + 1#
  </cfquery>
  arguments.currentLogOutTime - nextrecord.logintime
  <cfreturn time>
</cffunction>
<CFOUTPUT query="agtlogdata">
<CFIF Date EQ #LTrim(CreateODBCDate(dateformat(now(),'m/d/yyyy')))# OR Date IS "">
     <TR class="tiny">
          <TD></TD>
          <TD align="center">#TimeFormat(logintime, "hh:mm tt")#</TD>
          <TD align="center">#TimeFormat(logouttime, "hh:mm tt")#</TD>
          <TD align="center">#DateFormat(date, 'm/d')#</TD>
          <TD align="center">#reasoncodename#</TD>
          <TD align="center"><cfif reasoncode IS NOT 9>#getTimeDiff(record,logouttime)#</cfif>
          </TD>                                          
     </TR>
<CFELSE>
</CFIF>
</CFOUTPUT>
</TABLE>

And I'm getting this error now:

Variable time is undefined.

Now what am I doing wrong?
0
 

Expert Comment

by:brimcBAM
ID: 11777863
Time has not been set to anything.  I think you want:

<cfset time = arguments.currentLogOutTime - nextrecord.logintime>
  <cfreturn time>

I am not sure if that will give you the proper value, or if you will have to do a DateDifference on the two times.  Also, a TimeFormat(time,'mask') may be needed to put your variable 'time' into the correct format.
0
 

Author Comment

by:Lee R Liddick Jr
ID: 11778050
This is what I did...

  <cfset time = #DateDiff("N",arguments.currentLogOutTime,nextrecord.logintime)#>
  <cfreturn #timeformat(time, "h:m")#>

and this is what I get now:

The system has attempted to use an undefined value, which usually indicates a programming error, either in your code or some system code.
Null Pointers are another name for undefined values.
0
 

Author Comment

by:Lee R Liddick Jr
ID: 11778225
And if I let it like this:

  <cfset time = arguments.currentLogOutTime - nextrecord.logintime>
  <cfreturn #timeformat(time, "h:m")#>

I get:

The value "" cannot be converted to a number
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 11778441
time is a reserved word.  Additionally you do not need # inside a cfreturn statement
0
 

Author Comment

by:Lee R Liddick Jr
ID: 11779857
I changed the time to agtlogtimeout and removed the # inside the cfreturn statement and it still states the value "" cannot be converted to a number...
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 11784463
DId you try printing arguments.currentLogOUtTime and nextrecord.logintime before the cfset or the return?


What do you get
0
 

Author Comment

by:Lee R Liddick Jr
ID: 11792755
Anyone else before I tell them this can't be done???
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 11793667
What did you get when printing these variables to the screen ?
0
 

Author Comment

by:Lee R Liddick Jr
ID: 12071316
I actually forgot and gave up on it because it wasn't working and didnt' have time to mess with it.  I provided points to those who put work into helping.  Thank you and my apologies for not closing out the question...
0

Featured Post

Flexible connectivity for any environment

The KE6900 series can extend and deploy computers with high definition displays across multiple stations in a variety of applications that suit any environment. Expand computer use to stations across multiple rooms with dynamic access.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question