Forum Discussion

rlussky's avatar
rlussky
Helper I
4 years ago
Solved

Use Clock-In / Clock-Out Data to Analyze Hours Worked by Hour & Quarter Hour

Hello,

I am looking to use time intelligence to analyze timecard table that has clock in and clock out values. I'd not only like to see calculations by day, but by hour and quarter hour as well. 

 

Example TimeCardData Table

EmployeeClockInTimeClockOutTime
A8:55:00 AM12:30:00 PM
A1:00:00 PM4:50:00 PM
B8:20:00 AM4:30:00 PM
C7:30:00 AM11:20:00 AM
C12:00:00 PM4:30:00 PM

 

I'd like to connect this to a Time Dimension table that has the fields of Time (h:m), Hour (h), and Quarter Hour. Quarter hour can either be formatted as 1st, 2nd, 3rd, 4th or 7.1, 7.2, 7.3, 7.4,.... I'm open to other suggestions for this.

 

I need a measure that calculates the total minutes worked. For example, with the above data for 7 AM through 9:59 AM: 

Hour7 AM (7-7:59 AM Hour)8 AM (8-8:59 AM Hour)9 AM (9-9:59 AM Hour)
Minutes worked30105180

Where for reference, in the 7 o clock hour, Employee C worked 30 minutes, in the 8 o clock hour Employee C worked 60 minutes, employee B worked 40 minutes, and employee A worked 5 minutes, and so on.

 

I was told that DATEDIFF using MINUTES would be the way to go, but I have no idea how to build the relationship to the Time dimension table with having two times I am using in the TimeCardData fact table.

 

Any help would be greatly appreciated!

Need Help
  • rlussky's avatar
    rlussky
    4 years ago

    Thanks Anonymous. While your solution works, the end result is not as dynamic as I was looking for.

     

    I ended up creating a time table in sql with quarter hour increments. I created a view in SSMS with logic to calculate hours worked based on clock in / clock out times per quarter hour increment. I was hoping a measure would be able to do this all for me, but this SQL view resolved my roadblocks.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi rlussky ,

     

    It is suggested to use the calculated columns.

    Create the following columns

    7 AM (7-7:59 AM Hour) = 
VAR _in = [ClockInTime]
VAR _out = [ClockOutTime]
VAR _min =
    TIME ( 7, 0, 0 )
VAR _max =
    TIME ( 8, 0, 0 )
RETURN
    IF (
        _in < _min
            && _out > _min
            && _out < _max,
        HOUR ( _out - _min ) * 60
            + MINUTE ( _out - _min ),
        IF (
            _in > _min
                && _out < _max,
            HOUR ( _out - _in ) * 60
                + MINUTE ( _out - _in ),
            IF (
                _in > _min
                    && _out > _max
                    && _in < _max,
                HOUR ( _max - _in ) * 60
                    + MINUTE ( _max - _in ),
                IF (
                    _in < _min
                        && _out > _max,
                    HOUR ( _max - _min ) * 60
                        + MINUTE ( _max - _min )
                )
            )
        )
    )

    8 AM (8-8:59 AM Hour) = 
VAR _in = [ClockInTime]
VAR _out = [ClockOutTime]
VAR _min =
    TIME ( 8, 0, 0 )
VAR _max =
    TIME ( 9, 0, 0 )
RETURN
    IF (
        _in < _min
            && _out > _min
            && _out < _max,
        HOUR ( _out - _min ) * 60
            + MINUTE ( _out - _min ),
        IF (
            _in > _min
                && _out < _max,
            HOUR ( _out - _in ) * 60
                + MINUTE ( _out - _in ),
            IF (
                _in > _min
                    && _out > _max
                    && _in < _max,
                HOUR ( _max - _in ) * 60
                    + MINUTE ( _max - _in ),
                IF (
                    _in < _min
                        && _out > _max,
                    HOUR ( _max - _min ) * 60
                        + MINUTE ( _max - _min )
                )
            )
        )
    )

    9 AM (9-9:59 AM Hour) = 
VAR _in = [ClockInTime]
VAR _out = [ClockOutTime]
VAR _min =
    TIME ( 9, 0, 0 )
VAR _max =
    TIME ( 10, 0, 0 )
RETURN
    IF (
        _in < _min
            && _out > _min
            && _out < _max,
        HOUR ( _out - _min ) * 60
            + MINUTE ( _out - _min ),
        IF (
            _in > _min
                && _out < _max,
            HOUR ( _out - _in ) * 60
                + MINUTE ( _out - _in ),
            IF (
                _in > _min
                    && _out > _max
                    && _in < _max,
                HOUR ( _max - _in ) * 60
                    + MINUTE ( _max - _in ),
                IF (
                    _in < _min
                        && _out > _max,
                    HOUR ( _max - _min ) * 60
                        + MINUTE ( _max - _min )
                )
            )
        )
    )

     

     

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • rlussky's avatar
      rlussky
      Helper I
      4 years ago

      Thanks Anonymous. While your solution works, the end result is not as dynamic as I was looking for.

       

      I ended up creating a time table in sql with quarter hour increments. I created a view in SSMS with logic to calculate hours worked based on clock in / clock out times per quarter hour increment. I was hoping a measure would be able to do this all for me, but this SQL view resolved my roadblocks.