Форум программистов [Powered by Invision Power Board]

Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > Firebird, Interbase > IBExpert. Вычисление среднего значения за час

Автор: neweraser 27.3.2018, 10:23

Добрый день!
Подскажите пожалуйста, как в IBExpert сделать процедуру в базе, которая возвращает среднее значение работы сотрудника за час?
Имеется таблица вида:

USER_ID T_START T_STOP
Сотрудник 1    26.03.2018 11:24:11    26.03.2018 11:27:48
Сотрудник 1    26.03.2018 11:27:53    26.03.2018 11:31:54
Сотрудник 1    26.03.2018 11:31:54    26.03.2018 11:42:50
Сотрудник 1    26.03.2018 12:59:00    26.03.2018 13:24:00

Где T_START - начало работы, T_STOP соответственно конец.

В итоге нужно получить таблицу следующего вида:

USER_ID 10:00-11:00 11:00-12:00 12:00-13:00 13:00-14:00 и так до 18:00
Сотрудник1 0 ~18/60 1/60 24/60

Автор: Akella 27.3.2018, 11:33

используй группировку и AVG

Автор: neweraser 27.3.2018, 11:51

Цитата(Akella @ 27.3.2018, 11:33

)

используй группировку и AVG

Гугл тоже так говорит smile

Нашел 1 пример, но там немного другая задача - запутался еще больше.
Я понимаю, что надо сидеть и читать мануалы и разбираться, но может есть какое-то готовое решение?

Автор: Akina 27.3.2018, 12:20

Цитата(neweraser @ 27.3.2018, 12:51

)

может есть какое-то готовое решение?

Поспрошай тех студентов, кто сдавал это задание в прошлом году...

Автор: neweraser 27.3.2018, 12:55

Найти бы еще этих студентов smile

Код


    CASE WHEN EXTRACT(HOUR FROM T_START)<=12 and EXTRACT(HOUR FROM T_STOP)>=12
         THEN CASE WHEN EXTRACT(HOUR FROM T_START)<12 and EXTRACT(HOUR FROM T_STOP)>12 THEN CAST('13:00:00' AS TIME)-CAST('12:00:00' AS TIME)
                   WHEN EXTRACT(HOUR FROM T_START)<12 THEN CAST(T_STOP AS TIME)-CAST('12:00:00' AS TIME)
                   WHEN EXTRACT(HOUR FROM T_STOP)>12 THEN CAST('13:00:00' AS TIME)-CAST(T_START AS TIME)
                   ELSE CAST(T_STOP AS TIME)-CAST(T_START AS TIME) END
         ELSE 0 END AS Between12and13,

вот этот код не работает, нули выдает

Автор: Akella 27.3.2018, 15:08

Цитата(neweraser @ 27.3.2018, 10:23

)

как в IBExpert сделать процедуру в базе

Пока не совсем понятно: ты не знаешь, какие кнопки нажать в IBExpert или ты не можешь SQL запрос составить? Это в корне разные вещи.

Автор: neweraser 27.3.2018, 15:30

Akella, мне нужно сделать процедуру в IBExpert, на входе дата и ид сотрудника, на выходе почасовая статистика

Автор: Akella 27.3.2018, 15:46

Сперва составь SQL запрос, а потом уже обернёшь его в процедуру.

Добавлено через 1 минуту и 55 секунд
я не пойму пока, зачем тебе

Цитата(neweraser @ 27.3.2018, 12:55

)

CASE

и зачем тебе

Цитата(neweraser @ 27.3.2018, 12:55

)

EXTRACT

тебе же надо вычитать?
т.е. математические операции с датами
http://firebirdsql.su/doku.php?id=matematicheskie_operacii_s_datoj

Автор: neweraser 28.3.2018, 12:41

У меня получилось пока только так

Код


avg(iif(cast(time_begin as time) >= cast('10:00:00' as time) and cast(time_end as time) < cast('11:00:00' as time), datediff(second, time_begin, time_end), null))/60 as "10:00 - 11:00",

но считает не совсем верно. Мне нужно считать время в час, даже если Time_begin было с 8 утра, т.е. сотрудники ходят на перерывы - соответственно меняют статус, а в данном случае запрос выбирает значения Time_begin и Time_end в пределах одного часа, если нет совпадений, то выводит null...

Автор: neweraser 29.3.2018, 08:52

Нет никаких идей? Неделю бьюсь уже smile

Автор: neweraser 30.3.2018, 08:44

На текущий момент имею такое решение:

Код


SUM((CASE
        WHEN EXTRACT(HOUR FROM T1.T_START)<=9 and EXTRACT(HOUR FROM T1.T_STOP)>=9
        THEN CASE WHEN EXTRACT(HOUR FROM T1.T_START)<9 and EXTRACT(HOUR FROM T1.T_STOP)>9 THEN CAST('10:00:00' AS TIME)-CAST('09:00:00' AS TIME)
        WHEN EXTRACT(HOUR FROM T1.T_START)<9 THEN CAST(T1.T_STOP AS TIME)-CAST('09:00:00' AS TIME)
        WHEN EXTRACT(HOUR FROM T1.T_STOP)>9 THEN CAST('10:00:00' AS TIME)-CAST(T1.T_START AS TIME)
        ELSE CAST(T1.T_STOP AS TIME)-CAST(T1.T_START AS TIME) END
        ELSE 0
    END)/60)/60 AS Between09and10,

  from TABLE1 T1 wt

    where (T1.state_id = 1) and (
           cast(T1.T_START as timestamp) < DATEADD(8 HOUR TO DATEADD(1 DAY TO :D)) and
           cast(T1.T_STOP as timestamp) > DATEADD(8 HOUR TO :D)

Все считает, но за текущие сутки, а мне нужно с 8 утра выбранного дня (входная переменная :Д) до 8 утра следующего дня. Условие WHERE прописал, но все равно как надо не работает. Нужно вычитать не часы TIME, а даты TIMESTAMP. Попробовал заменить код

Код


CAST(T1.T_STOP AS TIME)-CAST('08:00:00' AS TIME)

на

Код


CAST(DATEADD(HOUR, -8, T1.T_STOP) AS TIMESTAMP)

Не запускается процедура, выдает ошибку:

Код


Strings can not be devided in dialect 3

Странно, я ж не пытаюсь разделить строку.