Solved

# Calculating working hours

Posted on 2013-06-12
347 Views
Hi all,

I have a question. I use Delphi XE2 with Absolute Database. What would be the best way to accomplish the following?

I need to calculate the total hours worked for each day. I have attached a screenshot of the data that I have. I need the total hours for each day as well as total hours

0
Question by:DigitalNam
• 7
• 4
• 3
• +3

LVL 25

Expert Comment

ID: 39242717
In my example I use WorkLog as table name. You should set yours. Example contains two parts. One is for getting start and end date/time and second part is for calculate hours between.

SQL code:
SELECT WorkTimes.EmployeeID,WorkTimes.DateOut+WorkTimes.TimeOut as  EndDate,
(select Max(WorkStartTimes.DateIn+WorkStartTimes.TimeIn) from WorkLog as WorkStartTimes where
WorkStartTimes.EmployeeID= WorkTimes.EmployeeID and WorkStartTimes.Status='In' and WorkStartTimes.ID<WorkTimes.ID) as StartDate
FROM WorkLog as WorkTimes where Status='Out'

...second, because didn't find any useful function for manipulating dates, I suggest to add calculated field in delphi:
http://www.componentace.com/calculated-field-delphi.htm
... where you put similar code:
...
var
dt_start, dt_end: TDateTime;
iHours: Integer;
begin
dt_start := dbtbl.FieldByName('StartDate').AsDateTime;
dt_end := dbtbl.FieldByName('EndDate').AsDateTime;
iHours := HoursBetween(dt_end, dt_start);
dbtbl.FieldByName('HoursBetween').AsInteger := iHours;
...
.. total sum of hours ... just make while..not ..eof do...next loop and sum hours in code.
0

LVL 24

Expert Comment

ID: 39243814
Hi DigitalNam,
Absolute DB is coming up short in terms of SQL date functions, however you can make it up by using calculated fields, as suggested by Sinisav.

Use this query to fetch the Employee records:
Select
Ein.EmployeeID as EmpID, Eout.DateOut AS EmpDate, Eout.TimeOut AS TimeOut, Ein.TimeIn AS TimeIn
from EmpTrack Ein
join EmpTrack Eout on Eout.DateOut = Ein.DateIn;

Assuming your table name is "EmpTrack".

...
private
{ Private declarations }
TotalTime: Extended;
public
{ Public declarations }
end;
...
uses DateUtils;
..

procedure TForm1.Button1Click(Sender: TObject);
begin
TotalTime := 0;
AQDB.Open;
end;

procedure TForm1.AQDBCalcFields(DataSet: TDataSet);
var
Dt1, Dt2 : TDateTime;
Tmin:Extended;
hrs, mnts:Integer;
begin
Dt1 := DataSet.FieldByName('TimeIn').AsDateTime;
Dt2:= DataSet.FieldByName('TimeOut').AsDateTime;
Tmin := MinuteSpan(Dt2, Dt1); // Time difference in Minutes for the current record. you could use SecondSpan() if you want the difference in Seconds

// set TotalTime = 0 before opening the AQDB DataSet
TotalTime := TotalTime + Tmin;  //At the end TotalTime will hold the total time in seconds of all the times differences. If you need to show this time in HH:MM then the hours are calculated by using "HH := round(TotalTime) div 60;" and the minutes by using "MM := round(TotalTime) mod 60;"

hrs := round(Tmin) div 60;      // Total Hours for the current record
mnts := round(Tmin) mod 60;  // Remaining Minutes for the current record
//NewFieldTimeDiff is a New Field (Data) created at design time in the fields editor of the AQDB of the datatype String which will hold the result of the MinuteSpan
DataSet.FieldByName('NewFieldTimeDiff').AsString := Format('%d:%d',[hrs, mnts]);
end;
0

LVL 1

Author Comment

ID: 39243998
Hi JimyX,

Seems like this code could work for what I trying to do. The only problem is that I am struggling with the query. I can't seem to get the alias to work in the SQL.
0

LVL 1

Author Comment

ID: 39244008
My table name is Attendance with the following fields:

ID - AutoInc
EmployeeID - Integer
Status - String
DateIn - Date
TimeIn - Time
DateOut - Date
TimeOut - Time
0

LVL 25

Expert Comment

ID: 39244018
did you try my sql example?
0

LVL 24

Expert Comment

ID: 39244154
The aliases are not required:
Select
Ein.EmployeeID, Eout.DateOut, Eout.TimeOut, Ein.TimeIn
from EmpTrack Ein
join EmpTrack Eout on Eout.DateOut = Ein.DateIn;

Did you try the query in the DBManager?
0

LVL 100

Expert Comment

ID: 39244368
Will the date out for a day always be the same date as the date in?

Does ay one ever work through midnight?

mlmcc
0

LVL 1

Author Comment

ID: 39245595
ml,

No one works through midnight. There are 2 shifts working. One starting at 6 in the morning that finishes at 15:00 or 16:00 where the other shift starts at 8:00 and ends at about 18:30.

JimyX,
I tried it with dbmanager but it does not want to return results. Only an error.

sinisav,

I tried yours as well without any luck.
0

LVL 25

Expert Comment

ID: 39245896
any chance to post near-empty database file here?
0

LVL 21

Expert Comment

ID: 39246276
Is it me, or does the data look... badly formed?

Every row has half the fields for date and time empty.  This just screams poor fit...

If you had the following fields:
ID
EmployeeID
Status
TargetDate
TargetTime

Then each record would at least be able to use all of the fields.  You would still have one record for in and one record for out.

If you want to keep all the date and time fields then I would suggest something like this:

ID
EmployeeID
InDate
InTime
OutDate
OutTime

When an employee arrives and punches in, they get a new record that has the time in.  When the clock out (for break, lunch, or the end of the day) the record has the Out date and time filled in.  This method would at least leave you with matching records where each record would show a chunk of time the employee gets paid for.  Each record would be full unless the employee forgot to clock out.

I notice that you have stated that there will not be any person working past midnight so you should only need one date field, not two.

Each completed record would then be easy to calculate time for.  Add up the calculated times and you have your hours worked for a day.
0

LVL 45

Expert Comment

ID: 39252684
Since you don't have to compensate for date differences, this should give you the times for each employee for each date they worked.  I would expect the WorkedTime values to be percentages of a day (units).
Select I.EmployeeID, (O.TimeOut - I.TimeIn) As WorkedTime
From TimeTable As I INNER JOIN TimeTable As O
On I.EmployeeID = O.EmployeeID
Where I.Status = 'In' And O.Status = 'Out'
And ToDate(ToString(I.DateIn, 'mm/dd/yyyy'), 'mm/dd/yyyy') = ToDate(ToString(O.DateOut, 'mm/dd/yyyy'), 'mm/dd/yyyy')
0

LVL 24

Accepted Solution

jimyX earned 500 total points
ID: 39274353
I have updated the query and here is a complete example:
Dfm:
object Form1: TForm1
Left = 0
Top = 0
BorderStyle = bsSingle
Caption = 'Form1'
ClientHeight = 304
ClientWidth = 610
Color = clBtnFace
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'Tahoma'
Font.Style = []
OldCreateOrder = False
OnClose = FormClose
PixelsPerInch = 96
TextHeight = 13
object Label1: TLabel
Left = 8
Top = 246
Width = 68
Height = 13
Caption = 'Per Hour Rate'
end
object Label2: TLabel
Left = 8
Top = 271
Width = 59
Height = 13
Caption = 'Due Amount'
end
object BtnOpenDB: TButton
Left = 8
Top = 10
Width = 75
Height = 25
Caption = 'Open'
TabOrder = 0
OnClick = BtnOpenDBClick
end
object DBGTimeTrack: TDBGrid
Left = 265
Top = 41
Width = 336
Height = 249
DataSource = DSTimeTrack
Options = [dgTitles, dgIndicator, dgColumnResize, dgColLines, dgRowLines, dgTabs, dgRowSelect, dgConfirmDelete, dgCancelOnExit, dgTitleClick, dgTitleHotTrack]
TabOrder = 1
TitleFont.Charset = DEFAULT_CHARSET
TitleFont.Color = clWindowText
TitleFont.Height = -11
TitleFont.Name = 'Tahoma'
TitleFont.Style = []
Columns = <
item
Alignment = taCenter
Expanded = False
FieldName = 'DateOut'
Title.Alignment = taCenter
Title.Caption = 'Date'
Width = 73
Visible = True
end
item
Alignment = taCenter
Expanded = False
FieldName = 'TimeIn'
Title.Alignment = taCenter
Width = 65
Visible = True
end
item
Alignment = taCenter
Expanded = False
FieldName = 'TimeOut'
Title.Alignment = taCenter
Width = 62
Visible = True
end
item
Alignment = taCenter
Expanded = False
FieldName = 'NFWorkedHours'
Title.Alignment = taCenter
Title.Caption = 'Worked Hours'
Width = 88
Visible = True
end>
end
object BtnTest: TButton
Left = 520
Top = 8
Width = 75
Height = 25
Caption = 'test'
TabOrder = 2
OnClick = BtnTestClick
end
object DBGEmp: TDBGrid
Left = 8
Top = 41
Width = 251
Height = 196
DataSource = DSEmployee
Options = [dgTitles, dgIndicator, dgColumnResize, dgColLines, dgRowLines, dgTabs, dgRowSelect, dgConfirmDelete, dgCancelOnExit, dgTitleClick, dgTitleHotTrack]
TabOrder = 3
TitleFont.Charset = DEFAULT_CHARSET
TitleFont.Color = clWindowText
TitleFont.Height = -11
TitleFont.Name = 'Tahoma'
TitleFont.Style = []
OnCellClick = DBGEmpCellClick
Columns = <
item
Alignment = taCenter
Expanded = False
FieldName = 'EmpID'
Title.Alignment = taCenter
Title.Caption = 'Emp ID'
Width = 105
Visible = True
end
item
Expanded = False
FieldName = 'TMin'
Title.Alignment = taCenter
Title.Caption = 'Total Minutes'
Width = 108
Visible = True
end>
end
object EdHourRate: TEdit
Left = 80
Top = 243
Width = 179
Height = 21
TabOrder = 4
Text = '7'
end
object EdDueAmount: TEdit
Left = 80
Top = 268
Width = 179
Height = 21
TabOrder = 5
end
object ABSDBConn: TABSDatabase
CurrentVersion = '7.05 '
DatabaseFileName = 'ETT.ABS'
DatabaseName = 'ETT'
Exclusive = False
MaxConnections = 500
MultiUser = False
SessionName = 'Default'
Left = 264
end
object AQTimeTrack: TABSQuery
CurrentVersion = '7.05 '
DatabaseName = 'ETT'
InMemory = False
SQL.Strings = (

'Select distinct Ein.EmpID, Eout.DateOut, Eout.TimeOut, Ein.TimeI' +
'n,'
''
'cast(ToString(Eout.TimeOut, '#39'hh24'#39') as Integer) AS HTOut,'
'cast(ToString(Ein.TimeIn, '#39'hh24'#39') as Integer) AS HTIn,'
'cast(ToString(Eout.TimeOut, '#39'nn'#39') as Integer) AS MTOut,'
'cast(ToString(Ein.TimeIn, '#39'nn'#39') as Integer) AS MTIn,'
''
''

'(((HTOut-HTIn)-1)+(((MTOut-MTIn)+60)/60)) +'#39':'#39'+ (((MTOut-MTIn)+6' +
'0)-(60*((((MTOut-MTIn)+60)/60)))) AS NFWorkedHours'
''
''
'from Emp Ein'

'join Emp Eout on Eout.DateOut = Ein.DateIn and Eout.EmpID = :Emp' +
'ID'
'where Ein.EmpID = :EmpID;')
Left = 336
Top = 136
ParamData = <
item
DataType = ftUnknown
Name = 'EmpID'
ParamType = ptUnknown
end
item
DataType = ftUnknown
Name = 'EmpID'
ParamType = ptUnknown
end>
object AQTimeTrackEmpID: TIntegerField
FieldName = 'EmpID'
end
object AQTimeTrackDateOut: TDateField
FieldName = 'DateOut'
end
object AQTimeTrackTimeOut: TTimeField
FieldName = 'TimeOut'
end
object AQTimeTrackTimeIn: TTimeField
FieldName = 'TimeIn'
end
object AQTimeTrackHTOut: TIntegerField
FieldName = 'HTOut'
end
object AQTimeTrackHTIn: TIntegerField
FieldName = 'HTIn'
end
object AQTimeTrackMTOut: TIntegerField
FieldName = 'MTOut'
end
object AQTimeTrackMTIn: TIntegerField
FieldName = 'MTIn'
end
object AQTimeTrackNFWorkedHours: TStringField
FieldName = 'NFWorkedHours'
Size = 23
end
end
object DSTimeTrack: TDataSource
DataSet = AQTimeTrack
Left = 400
Top = 136
end
object AQEmployee: TABSQuery
CurrentVersion = '7.05 '
DatabaseName = 'ETT'
InMemory = False
SQL.Strings = (
'Select TblEmp.EmpID, sum(TblEmp.WorkedMinutes) AS TMin from '

'(Select distinct Ein.EmpID, Eout.DateOut, Eout.TimeOut, Ein.Time' +
'In,'
''
'cast(ToString(Eout.TimeOut, '#39'hh24'#39') as Integer) AS HTOut,'
'cast(ToString(Ein.TimeIn, '#39'hh24'#39') as Integer) AS HTIn,'
'cast(ToString(Eout.TimeOut, '#39'nn'#39') as Integer) AS MTOut,'
'cast(ToString(Ein.TimeIn, '#39'nn'#39') as Integer) AS MTIn,'
''
''

'(((HTOut-HTIn)-1)+(((MTOut-MTIn)+60)/60)) +'#39':'#39'+ (((MTOut-MTIn)+6' +
'0)-(60*((((MTOut-MTIn)+60)/60)))) AS WorkedHours,'
''
'(60 * ((HTOut-HTIn)-1) + ((MTOut-MTIn)+60)) AS WorkedMinutes'
''
'from Emp Ein'

'join Emp Eout on Eout.DateOut = Ein.DateIn and Eout.EmpID =  Ein' +
'.EmpID'
'order by Ein.EmpID) AS TblEmp'
'group by TblEmp.EmpID'
'order by TblEmp.EmpID;')
Left = 72
Top = 88
object AQEmployeeEmpID: TIntegerField
FieldName = 'EmpID'
end
object AQEmployeeTMin: TLargeintField
FieldName = 'TMin'
end
end
object DSEmployee: TDataSource
DataSet = AQEmployee
Left = 144
Top = 88
end
end

Pas:
unit Unit1;

interface

uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, ABSMain, DB, StdCtrls, Grids, DBGrids;

type
TForm1 = class(TForm)
ABSDBConn: TABSDatabase;
AQTimeTrack: TABSQuery;
BtnOpenDB: TButton;
DBGTimeTrack: TDBGrid;
DSTimeTrack: TDataSource;
BtnTest: TButton;
DBGEmp: TDBGrid;
AQEmployee: TABSQuery;
DSEmployee: TDataSource;
AQEmployeeEmpID: TIntegerField;
AQEmployeeTMin: TLargeintField;
AQTimeTrackEmpID: TIntegerField;
AQTimeTrackDateOut: TDateField;
AQTimeTrackTimeOut: TTimeField;
AQTimeTrackTimeIn: TTimeField;
AQTimeTrackHTOut: TIntegerField;
AQTimeTrackHTIn: TIntegerField;
AQTimeTrackMTOut: TIntegerField;
AQTimeTrackMTIn: TIntegerField;
AQTimeTrackNFWorkedHours: TStringField;
EdHourRate: TEdit;
Label1: TLabel;
EdDueAmount: TEdit;
Label2: TLabel;
procedure BtnOpenDBClick(Sender: TObject);
procedure BtnTestClick(Sender: TObject);
procedure FormClose(Sender: TObject; var Action: TCloseAction);
procedure DBGEmpCellClick(Column: TColumn);
private
{ Private declarations }
public
{ Public declarations }
end;

var
Form1: TForm1;

implementation

uses DateUtils;

{\$R *.dfm}

procedure TForm1.BtnOpenDBClick(Sender: TObject);
begin
AQEmployee.Open;
end;

procedure TForm1.BtnTestClick(Sender: TObject);
var
Dt1, Dt2 : TDateTime;
Tmin:Extended;
hrs, mnts:Integer;
begin
Dt1 := StrtoDateTime('5:53');
Dt2:= StrToDateTime('14:18');
Tmin := 498;//MinuteSpan(Dt2, Dt1);

hrs := round(Tmin) div 60;
mnts := round(Tmin) mod 60;
showmessage(Format('%0.2d:%0.2d',[hrs, mnts]));
end;

procedure TForm1.DBGEmpCellClick(Column: TColumn);
var
Mints:Integer;
Cst:Extended;
begin
if DBGEmp.DataSource.DataSet.IsEmpty then Exit;

AQTimeTrack.Close;
AQTimeTrack.ParamByName('EmpID').AsInteger := DBGEmp.DataSource.DataSet.FieldByName('EmpID').AsInteger;
AQTimeTrack.Open;

// get the total minutes
Mints := DBGEmp.DataSource.DataSet.FieldByName('TMin').AsInteger;

// Convert minutes to hours and multiply by the hourly rate, also the remaining minutes
cst := (Mints div 60) * StrToFloat(EdHourRate.Text);
cst := cst + (Mints mod 60) * (StrToFloat(EdHourRate.Text) / 60);

EdDueAmount.Text := Format('%0.2n',[cst]);
end;

procedure TForm1.FormClose(Sender: TObject; var Action: TCloseAction);
begin
AQTimeTrack.Close;
AQEmployee.Close;
end;

end.
0

LVL 1

Author Comment

ID: 39275138
Jimyx:

Thank you for the reply and the code. I recreated the table and ran the sample but when I open I get "ON condition for JOIN is not applicable - Natve error 20270".
0

LVL 24

Expert Comment

ID: 39277095
Which version of AbsoluteDb you are using?
It executes successfully with mine.
0

LVL 1

Author Comment

ID: 39289473
JimyX: I am using version 7.11.
0

LVL 100

Expert Comment

ID: 39475481
I've requested that this question be deleted for the following reason:

The question has either no comments or not enough useful information to be called an "answer".
0

LVL 1

Author Comment

ID: 39475467
Thanks Jimyx. Your solution is working. I am attaching the code.
0

LVL 1

Author Closing Comment

ID: 39478689
Great job
0

## Featured Post

Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi databaseâ€¦
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGridâ€¦
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filledâ€¦
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: â€¦

#### Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!