Блог

У меня есть 2 таблицы, AvailabilitiesDB и AvailabilityTemplateDB.

Таблица AvailabilitiesDB выглядит следующим образом

 IsAvailable        StartTime                   FinishTime           Employee_ID    Week    Day Of the Week
                                                                                            
   1            25-09-2020 16:00:00      25-09-2020 19:00:00           101          39          5
   0            27-08-2020 14:00:00      27-08-2020 17:00:00           13           35          4
   1            25-11-2020 09:00:00      25-11-2020 18:00:00           66           48          3
 

Таблица AvailabilityTemplateDB выглядит следующим образом:

 Day Of The Week       StartTime                FinishTime           Employee_ID
   3                  18:00:00                 21:00:00                101
   2                  11:00:00                 17:00:00                13
   6                  06:00:00                 20:00:00                66
 

Вот в чем проблема:
Я хочу рассчитать доступность сотрудников, то есть их доступные минуты (то есть от времени начала до времени окончания).

Однако дело в том, что у всех сотрудников есть шаблон (AvailabilityTemplateDB), который является их рабочим графиком, поэтому, если в AvailabilitiesDB нет никаких наблюдений, это означает, что они используют свой шаблон. Но если в базе данных AvailabilitiesDB есть наблюдение за конкретным сотрудником, это означает, что шаблон был перезаписан для определенного дня. Шаблоны повторяются каждую неделю, поэтому у них нет конкретных дат, а просто «День недели».

Итак, я хочу вычислить что-то вроде: вычислить DatesBetween (минуты), где идентификатор сотрудника = идентификатор сотрудника, и если в базе данных availabilitiesDB есть данные, они должны использовать это число, поэтому для первого наблюдения это будет 180 минут, но если там нет даты, это должно занять время из таблицы шаблонов. Мне это сложно, потому что все, что у меня есть, это «День недели» в таблице шаблонов, и я не знаю, как я бы «конвертировал» его в даты.

Это было то, что я пробовал (не близкий конечный результат), и, очевидно, он не должен подсчитывать строки, я просто пытался приблизиться к ответу. Дата относится к моей таблице дат, которая содержит уникальные даты для всех дат из таблицы AvailabiltiesDB.

 Try = 
VAR dates =
    DATESBETWEEN (
        'Date'[Date].[Date],
        MIN ( 'Date'[Date].[Date] ),
        MAX ( 'Date'[Date].[Date] )
    )
VAR Employee_ID = AvailabilitiesDB[Employee_ID]
VAR Weekdays = AvailabilitiesDB[Day Of the Week]
VAR weekdaysAndDateMatch = 
VAR COUNTZ =
    CALCULATE (
        COUNTROWS ( AvailabilitiesDB ),
        FILTER (
            AvailabilityTemplateDB,
            Weekdays = AvailabilityTemplateDB[Day of the week]
                amp;amp; Employee_ID = AvailabilityTemplateDB[Employee_ID]))
RETURN IF ( COUNTZ = 0, "Yes", "No" )
 

Моим желаемым результатом был бы столбец в таблице AvailabilitiesDB, который суммировал доступность в минутах в неделю для каждого employee_ID.

Я надеюсь, что вы сможете мне помочь, заранее благодарю вас.

События с определенной продолжительностью упрощаются за счет реализации таблицы моментальных снимков.

Еще одна хорошая практика при работе со временем — разделить часть даты и часть времени на два разных столбца

Поэтому я создал небольшую модель с измерением Даты, сотрудников и времени

Для этого примера нам просто нужны месяцы с августа по ноябрь. Чтобы сопоставить День недели с данными выборки, мне пришлось вычесть 1.

 Date =
ADDCOLUMNS (
    CALENDAR ( "2020-08-01", "2020-11-30" ),
    "WeekDay", WEEKDAY ( [Date] ) - 1
)
 

Измерение сотрудников — это

 Employees =
ADDCOLUMNS (
    DISTINCT ( AvailabilitiesTemplateDB[Employee_ID] ),
    "Name", FORMAT ( [Employee_ID], "000" )
)
 

Для измерения времени я создал меру в качестве параметра с детализацией по времени, которая будет использоваться также для таблицы моментальных снимков. Это может быть изменено, чтобы адаптировать модель к требованиям.

Чтобы сохранить небольшое количество строк для этого примера, я использовал интервал в 1 час

 TimeInterval = VALUE( "01:00:00" )
 

Таким образом, расписание становится

 Time =
SELECTCOLUMNS (
    GENERATESERIES ( VALUE ( "00:00:00" ), VALUE ( "23:59:59" ), [TimeInterval] ),
    "Time", [Value]
)
 

Таблица моментальных снимков — это таблица, содержащая строку для каждого временного интервала, в течение которого доступен сотрудник. Для построения этой таблицы мы можем использовать набор функций UNION и EXCEPT . Таблица моментальных снимков содержит всего три столбца: Employee_ID, дата и время

Этот код сопоставляет дату с днем недели, используя таблицу дат, которую мы создали ранее

 AvailabilitiesSnapshot = 
VAR TimeGranularity = [TimeInterval]
VAR AvailableFromTemplateDB =
    SELECTCOLUMNS(
        GENERATE(
            AvailabilitiesTemplateDB,
            VAR DayOfWeek = AvailabilitiesTemplateDB[Day Of The Week]
            VAR TimesTable =
                SELECTCOLUMNS(
                    GENERATESERIES(
                        AvailabilitiesTemplateDB[StartTime],
                        AvailabilitiesTemplateDB[FinishTime] - TimeGranularity,
                        TimeGranularity
                    ),
                    "Time", [Value]
                )
            RETURN
                GENERATE(
                    FILTER( ALLNOBLANKROW( 'Date' ), 'Date'[WeekDay] = DayOfWeek ),
                    TimesTable
                )
        ),
        "Employee_ID", [Employee_ID],
        "Date", [Date],
        "Time", [Time]
    )
VAR AvailableFromDB =
    SELECTCOLUMNS(
        GENERATE(
            CALCULATETABLE( AvailabilitiesDB, AvailabilitiesDB[IsAvailable] = 1 ),
            VAR TimesTable =
                SELECTCOLUMNS(
                    GENERATESERIES(
                        AvailabilitiesDB[StartTimeOnly],
                        AvailabilitiesDB[FinishTimeOnly] - TimeGranularity,
                        TimeGranularity
                    ),
                    "Time", [Value]
                )
            RETURN
                TimesTable
        ),
        "Employee_ID", [Employee_ID],
        "Date", [Date],
        "Time", [Time]
    )
VAR UnavailableFromDB =
    SELECTCOLUMNS(
        GENERATE(
            CALCULATETABLE( AvailabilitiesDB, AvailabilitiesDB[IsAvailable] = 0 ),
            VAR TimesTable =
                SELECTCOLUMNS(
                    GENERATESERIES(
                        AvailabilitiesDB[StartTimeOnly],
                        AvailabilitiesDB[FinishTimeOnly] - TimeGranularity,
                        TimeGranularity
                    ),
                    "Time", [Value]
                )
            RETURN
                TimesTable
        ),
        "Employee_ID", [Employee_ID],
        "Date", [Date],
        "Time", [Time]
    )
RETURN
    EXCEPT(
        DISTINCT( UNION( AvailableFromTemplateDB, AvailableFromDB ) ),
        UnavailableFromDB
    )
 

В этом коде сначала мы добавляем все доступные интервалы, затем удаляем интервалы, помеченные как недоступные, с AvailabilitiesDB[isAvailable] = 0

Затем мы создаем связи между этой новой таблицей моментальных снимков и размерами, чтобы получить модель

в этой модели написание меры для вычисления доступных минут является прямым

 AvailableTime = COUNTROWS( AvailabilitiesSnapshot ) * [TimeInterval] * 24 * 60 
 

Параметр timeInterval можно изменить, чтобы повысить уровень детализации. Конечно, это увеличит количество строк таблицы моментальных снимков, а также зависит от размера входных таблиц.