Solved

Calculating working hours

Posted on 2013-06-12
20
347 Views
Last Modified: 2013-09-09
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


Data layout
0
Comment
Question by:DigitalNam
  • 7
  • 4
  • 3
  • +3
20 Comments
 
LVL 25

Expert Comment

by:Sinisa Vuk
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'

Open in new window


...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;
...

Open in new window

.. total sum of hours ... just make while..not ..eof do...next loop and sum hours in code.
0
 
LVL 24

Expert Comment

by:jimyX
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;

Open in new window

0
 
LVL 1

Author Comment

by:DigitalNam
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

by:DigitalNam
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

by:Sinisa Vuk
ID: 39244018
did you try my sql example?
0
 
LVL 24

Expert Comment

by:jimyX
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;

Open in new window


Did you try the query in the DBManager?
0
 
LVL 100

Expert Comment

by:mlmcc
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

by:DigitalNam
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

by:Sinisa Vuk
ID: 39245896
any chance to post near-empty database file here?
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 21

Expert Comment

by:developmentguru
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

by:aikimark
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')

Open in new window

0
 
LVL 24

Accepted Solution

by:
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
  BorderIcons = [biSystemMenu, biMinimize]
  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
    ReadOnly = True
    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
    ReadOnly = True
    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

Open in new window


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.

Open in new window

0
 
LVL 1

Author Comment

by:DigitalNam
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

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

Author Comment

by:DigitalNam
ID: 39289473
JimyX: I am using version 7.11.
0
 
LVL 100

Expert Comment

by:mlmcc
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

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

Author Closing Comment

by:DigitalNam
ID: 39478689
Great job
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

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: …

762 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now