I have a request from the boss to make a report that will require me to
display how many hours something has been in a particular state. (As in
status). Hard to explain. Anyway, I need to go to the table in the db, find
all rows for a particular trouble ticket, find when they were put into one
of our 7 statuses, when they were put into another status (this info is
already in those rows), then determine how many hours they spent there in
each status. So a ticket report will say that ticket 1000 spent 4 hours in
customer research, 5 hrs in our company coding. 3 in our company testing, 4
in customer testing, etc.
The thing is, It has to assume an 8-5 workday, and leave out any hours which
don't fall in between 8 am to 5 pm, and also rule out any weekends.
I think that there is probably some sample code out there, because somebody
surely has had to do something similar to this before. If anyone knows of
any, please direct me. 4 2035
<%
Set HC = New HourCalc
dblHours = HC.GetWorkHours("10/28/03 8:00:00 AM","11/1/03 12:00:00 PM")
Set HC = Nothing
Response.Write dblHours
Class HourCalc
Private mdblTotalHours
Private mintClockStart
Private mintClockEnd
Private mdtmStartDate
Private mdtmEndDate
Private Sub Class_Initialize()
mdblTotalHours = 0
mintClockStart = 8
mintClockEnd = 17
End Sub
Private Sub Class_Terminate()
End Sub
Public Function GetWorkHours(dtmStart,dtmEnd)
If IsDate(dtmStart) And IsDate(dtmEnd) Then
If CDate(dtmStart) > CDate(dtmEnd) Then
dtmTemp = dtmStart
dtmStart = dtmEnd
dtmEnd = dtmTemp
End If
mdtmStartDate = DateSerial(Year(dtmStart),Month(dtmStart),Day(dtmS tart))
mdtmEndDate = DateSerial(Year(dtmEnd),Month(dtmEnd),Day(dtmEnd))
If mdtmStartDate = mdtmEndDate Then
If IsWorkDay(mdtmStartDate) Then
mdblTotalHours = mdblTotalHours + DateDiff("n",dtmStart,dtmEnd) / 60
End If
Else
If IsWorkDay(mdtmStartDate) Then
mdblTotalHours = mdblTotalHours + GetHours(dtmStart)
End If
If IsWorkDay(mdtmEndDate) Then
mdblTotalHours = mdblTotalHours + GetHours(dtmEnd)
End If
intDayDiff = DateDiff("d",mdtmStartDate,mdtmEndDate)
If intDayDiff > 1 Then
For i = 1 To (intDayDiff - 1)
If IsWorkDay(DateAdd("d",i,mintClockStart)) Then
mdblTotalHours = mdblTotalHours + (mintClockEnd - mintClockStart)
End If
Next
End If
End If
End If
GetWorkHours = mdblTotalHours
mdblTotalHours = 0
End Function
Private Function GetHours(dtmItem)
intHour = Hour(dtmItem)
If intHour < mintClockStart Then
intHour = mintClockStart
End If
GetHours = mintClockEnd - intHour
If GetHours < 0 Then
GetHours = 0
End IF
End Function
Private Function IsWorkDay(dtmDate)
IsWorkDay = False
Select Case DatePart("w",dtmDate)
Case 2,3,4,5,6
IsWorkDay = True
End Select
End Function
End Class
%>
-dlbjr
Discerning resolutions for the alms
WOW! Did you already have this? Or are you a whiz who can do this stuff on
the fly?
Thanks!
"dlbjr" <do******@do.u> wrote in message
news:89Erb.174$Qy4.13989@typhoon01... <% Set HC = New HourCalc dblHours = HC.GetWorkHours("10/28/03 8:00:00 AM","11/1/03 12:00:00 PM") Set HC = Nothing Response.Write dblHours
Class HourCalc Private mdblTotalHours Private mintClockStart Private mintClockEnd Private mdtmStartDate Private mdtmEndDate
Private Sub Class_Initialize() mdblTotalHours = 0 mintClockStart = 8 mintClockEnd = 17 End Sub
Private Sub Class_Terminate()
End Sub
Public Function GetWorkHours(dtmStart,dtmEnd) If IsDate(dtmStart) And IsDate(dtmEnd) Then If CDate(dtmStart) > CDate(dtmEnd) Then dtmTemp = dtmStart dtmStart = dtmEnd dtmEnd = dtmTemp End If mdtmStartDate =
DateSerial(Year(dtmStart),Month(dtmStart),Day(dtmS tart)) mdtmEndDate = DateSerial(Year(dtmEnd),Month(dtmEnd),Day(dtmEnd)) If mdtmStartDate = mdtmEndDate Then If IsWorkDay(mdtmStartDate) Then mdblTotalHours = mdblTotalHours + DateDiff("n",dtmStart,dtmEnd) / 60 End If Else If IsWorkDay(mdtmStartDate) Then mdblTotalHours = mdblTotalHours + GetHours(dtmStart) End If If IsWorkDay(mdtmEndDate) Then mdblTotalHours = mdblTotalHours + GetHours(dtmEnd) End If intDayDiff = DateDiff("d",mdtmStartDate,mdtmEndDate) If intDayDiff > 1 Then For i = 1 To (intDayDiff - 1) If IsWorkDay(DateAdd("d",i,mintClockStart)) Then mdblTotalHours = mdblTotalHours + (mintClockEnd - mintClockStart) End If Next End If End If End If GetWorkHours = mdblTotalHours mdblTotalHours = 0 End Function
Private Function GetHours(dtmItem) intHour = Hour(dtmItem) If intHour < mintClockStart Then intHour = mintClockStart End If GetHours = mintClockEnd - intHour If GetHours < 0 Then GetHours = 0 End IF End Function
Private Function IsWorkDay(dtmDate) IsWorkDay = False Select Case DatePart("w",dtmDate) Case 2,3,4,5,6 IsWorkDay = True End Select End Function End Class %>
-dlbjr
Discerning resolutions for the alms
knocked it out watching the practice last night.
Notice you can adjust the clock start and end in the class.
-dlbjr
Discerning resolutions for the alms
Did it work to your specifications?
-dlbjr
Discerning resolutions for the alms This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: mitchchristensen |
last post by:
I have a transaction log that tracks issues from a call center. Each
time an issue is assigned to someone else, closed, etc. I get a time
stamp. I have these time stamps for the beginning of an...
|
by: Privacy Advocate |
last post by:
//crossposted to: comp.lang.javascript, alt.comp.lang.javascript in an
effort to get factual answers from JavaScript experts//
Simply put; Is it possible to obtain the real (actual) IP address of...
|
by: __jakal__ |
last post by:
Hello,
I need to find out the time difference between UTC and local time.
I am doing it the following way
#include <sys/timeb.h>
#include <stdio.h>
int main()
{
struct timeb tp;
ftime(&tp);
|
by: amirmira |
last post by:
Hi,
I have an application where I monitor the status of a machine using it's IP
address. I would like to log an error when the machine goes down - but I want
to add the name of the machine along...
|
by: Dave |
last post by:
Hi all,
My question is: How do you obtain a complete list of all the users
that are registered on the local machine? I've been trying for hours
now and can't find anything that will help on...
|
by: zion_zii |
last post by:
Im working on an embedded project and i have to obtain individual
digits from a byte thats the output of a device. Im trying to output
this info to an LCD and need to convert it to ASCII first. I...
|
by: seannakasone |
last post by:
Is there a way to get the callstack level in c++? for example, take
the following code:
void call3() {
//callstack level would be 3
}
void call2() {
//callstack level would be 2
call3();
|
by: Stephen Poley |
last post by:
I have been trying to do something which should be quite simple: write a
cross-browser script which, as part of its function, involves finding
what the displayed width of a table cell is.
After...
|
by: =?Utf-8?B?Sm9obiBTdGFnZ3M=?= |
last post by:
Hello,
Please read this all before giving an answer :) I'm doing some
troubleshooting on a web application that my company wrote. It's written in
asp.net 1.1. The error that the Event viewer...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |