Utilice los datos de entrada / salida de reloj para analizar las horas trabajadas por hora y cuarto de hora

[Syndicate_Admin]

Hola

Estoy buscando usar la inteligencia de tiempo para analizar la tabla de tarjetas de tiempo que tiene valores de entrada y salida de reloj. No solo me gustaría ver los cálculos por día, sino también por hora y cuarto de hora.

Tabla TimeCardData de ejemplo

Empleado	ClockInTime	ClockOutTime
A	08:55:00	12:30:00
A	13:00:00	16:50:00
B	08:20:00	16:30:00
C	7:30:00	11:20:00
C	12:00:00	16:30:00

Me gustaría conectar esto a una tabla de dimensión de tiempo que tenga los campos de Tiempo (h:m), Hora (h) y Cuarto de hora. El cuarto de hora se puede formatear como 1º, 2º, 3º, 4º o 7.1, 7.2, 7.3, 7.4,.... Estoy abierto a otras sugerencias para esto.

Necesito una medida que calcule el total de minutos trabajados. Por ejemplo, con los datos anteriores de 7 AM a 9:59 AM:

Hora	7 AM (7-7:59 AM Hora)	8 AM (8-8:59 AM hora)	9 AM (9-9:59 AM Hora)
Minutos trabajados	30	105	180

Donde como referencia, en la hora de las 7 horas, el empleado C trabajó 30 minutos, en la hora de las 8 horas el empleado C trabajó 60 minutos, el empleado B trabajó 40 minutos y el empleado A trabajó 5 minutos, y así sucesivamente.

Me dijeron que DATEDIFF usando MINUTES sería el camino a seguir, pero no tengo idea de cómo construir la relación con la tabla de dimensiones de tiempo con tener dos veces que estoy usando en la tabla de hechos TimeCardData.

¡Cualquier ayuda sería muy apreciada!

[Syndicate_Admin]

Hay @rlussky ,

Se sugiere utilizar las columnas calculadas.

Crear las siguientes columnas

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

Saludos

Esteban Tao

Si esta publicación ayuda,entonces considere Aceptarla como la solución para ayudar a los otros miembros a encontrarla más rápidamente.

[Syndicate_Admin · Accepted solution]

Gracias @v-stephen-msft. Si bien su solución funciona, el resultado final no es tan dinámico como estaba buscando.

Terminé creando una tabla de tiempos en sql con incrementos de un cuarto de hora. Creé una vista en SSMS con lógica para calcular las horas trabajadas en función de los tiempos de entrada / salida de reloj por incremento de cuarto de hora. Esperaba que una medida pudiera hacer todo esto por mí, pero esta vista SQL resolvió mis obstáculos.