Lee R Liddick Jr
asked on
Calculating Time Difference in CFM
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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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[curre ntrow +1] - logouttime)
<cfelse>
- End Of Shift -
</cfif>
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[curre
<cfelse>
- End Of Shift -
</cfif>
ASKER
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.
"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.
ASKER
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
Field Name Data Type
Extension Text
LoginTime Date/Time
LogoutTime Date/Time
Date Date/Time
ReasonCode Text
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.currentLogOutTim e - 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,lo gouttime)# </cfif>
</cfoutput>
<cffunction name="getTimeDiff">
<cfargument name="currentrecordid">
<cfargument name="currentlogoutTime">
<cfquery name ="nextrecord">
select .... from ...
where record = #currentrecordid + 1#
</cfquery>
Calculate difference: arguments.currentLogOutTim
... 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,lo
</cfoutput>
ASKER
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(date format(now (),'m/d/yy yy')))# OR Date IS "">
<TR class="tiny">
<TD></TD>
<TD align="center">#TimeFormat (logintime , "hh:mm tt")#</TD>
<TD align="center">#TimeFormat (logouttim e, "hh:mm tt")#</TD>
<TD align="center">#DateFormat (date, 'm/d')#</TD>
<TD align="center">#reasoncode name#</TD>
<TD align="center"><cfif reason IS NOT 9>#getTimeDiff(recordid,lo gouttime)# </cfif></T D>
</TR>
<CFELSE>
</CFIF>
</CFOUTPUT>
</TABLE>
Where would I put the function and argument and new query statements?
<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(date
<TR class="tiny">
<TD></TD>
<TD align="center">#TimeFormat
<TD align="center">#TimeFormat
<TD align="center">#DateFormat
<TD align="center">#reasoncode
<TD align="center"><cfif reason IS NOT 9>#getTimeDiff(recordid,lo
</TR>
<CFELSE>
</CFIF>
</CFOUTPUT>
</TABLE>
Where would I put the function and argument and new query statements?
ASKER
It's telling me:
Variable RECORDID is undefined.
Variable RECORDID is undefined.
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.
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.
ASKER
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.currentLogOutTim e - nextrecord.logintime
<cfreturn time>
</cffunction>
<CFOUTPUT query="agtlogdata">
<CFIF Date EQ #LTrim(CreateODBCDate(date format(now (),'m/d/yy yy')))# OR Date IS "">
<TR class="tiny">
<TD></TD>
<TD align="center">#TimeFormat (logintime , "hh:mm tt")#</TD>
<TD align="center">#TimeFormat (logouttim e, "hh:mm tt")#</TD>
<TD align="center">#DateFormat (date, 'm/d')#</TD>
<TD align="center">#reasoncode name#</TD>
<TD align="center"><cfif reasoncode IS NOT 9>#getTimeDiff(record,logo uttime)#</ cfif>
</TD>
</TR>
<CFELSE>
</CFIF>
</CFOUTPUT>
</TABLE>
And I'm getting this error now:
Variable time is undefined.
Now what am I doing wrong?
<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.currentLogOutTim
<cfreturn time>
</cffunction>
<CFOUTPUT query="agtlogdata">
<CFIF Date EQ #LTrim(CreateODBCDate(date
<TR class="tiny">
<TD></TD>
<TD align="center">#TimeFormat
<TD align="center">#TimeFormat
<TD align="center">#DateFormat
<TD align="center">#reasoncode
<TD align="center"><cfif reasoncode IS NOT 9>#getTimeDiff(record,logo
</TD>
</TR>
<CFELSE>
</CFIF>
</CFOUTPUT>
</TABLE>
And I'm getting this error now:
Variable time is undefined.
Now what am I doing wrong?
Time has not been set to anything. I think you want:
<cfset time = arguments.currentLogOutTim e - 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.
<cfset time = arguments.currentLogOutTim
<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.
ASKER
This is what I did...
<cfset time = #DateDiff("N",arguments.cu rrentLogOu tTime,next record.log intime)#>
<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.
<cfset time = #DateDiff("N",arguments.cu
<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.
ASKER
And if I let it like this:
<cfset time = arguments.currentLogOutTim e - nextrecord.logintime>
<cfreturn #timeformat(time, "h:m")#>
I get:
The value "" cannot be converted to a number
<cfset time = arguments.currentLogOutTim
<cfreturn #timeformat(time, "h:m")#>
I get:
The value "" cannot be converted to a number
time is a reserved word. Additionally you do not need # inside a cfreturn statement
ASKER
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...
DId you try printing arguments.currentLogOUtTim e and nextrecord.logintime before the cfset or the return?
What do you get
What do you get
ASKER
Anyone else before I tell them this can't be done???
What did you get when printing these variables to the screen ?
ASKER
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...
ASKER
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!