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

[rlussky]

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

Employee	ClockInTime	ClockOutTime
A	8:55:00 AM	12:30:00 PM
A	1:00:00 PM	4:50:00 PM
B	8:20:00 AM	4:30:00 PM
C	7:30:00 AM	11:20:00 AM
C	12:00:00 PM	4: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: 

Hour	7 AM (7-7:59 AM Hour)	8 AM (8-8:59 AM Hour)	9 AM (9-9:59 AM Hour)
Minutes worked	30	105	180

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!

[v-stephen-msft]

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 · Accepted solution]

Thanks @v-stephen-msft. 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.