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

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.