Xpoint
   [напомнить пароль]

Разница времени с учетом нерабочего времени и выходных

Метки: [без меток]
2009-10-29 14:03:20 [обр] ddd(0/36)[досье]

здравствуйте!

возник следующий вопрос: есть таблица со структурой:
id, задача, когда_возникла, когда_сделана

нужно получить время, которое затрачено на решение задачи, то есть поле когда_сделана минус поле
когда_возникла

но проблема в том, что если задача возникает в нерабочее время, то из времени потраченного на решение задачи нужно вычесть время до начала рабочего дня (рабочий день начинается в 9:00). то есть:
1, подмести пол, 10:00, 12:00 - результат 2 часа
2, вынести мусор, 03:00, 10:00 - результат 1 час (так как начал в девять)

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

СУБД MSSQL 2005

спасибо!

спустя 42 минуты [обр] Прокаев2(0/35)[досье]
а когда вы заносите "когда_сделана", можно ли вычислять (в приложении) новое поле "когда_начата" ?
спустя 14 минут [обр] ddd(0/36)[досье]
не очень понял вашу мысль..
единственное, что могу сказать, что база не моя, и приложение тоже не моё.
я хочу сделать отчет по времени выполнения задач, а вставляет записи стороннее приложение
спустя 46 минут [обр] Прокаев2(0/35)[досье]
тогда для себя табличку завести и синхронизировать
с ручным рассчетом "когда_начата" ;)
спустя 1 минуту [обр] ddd(0/36)[досье]
т.е. не средствами sql, а какими-нибудь другими?
спустя 19 минут [обр] Прокаев2(0/35)[досье]

google://mssql sql calculate date difference non working days
принес
http://www.norimek.com/blog/post/2008/04/Calculating-Work-Days.aspx

но функцию придется писать самому

спустя 14 минут [обр] ddd(0/36)[досье]
не совсем то, о чем мечтается.
в принципе, мне кажется, что если обойти записи в курсоре, то можно желаемое получить..
спустя 22 минуты [обр] Thirteensmay(0/157)[досье]

Раз 30 обойдете с разных сторон, тогда мож че приблизительно похожее и начнете получать :)

Давеча делал расчет рабочего времени чела по его проходам через проходные, задача схожа, могу вам сказать что одним SQL вы всеравно не обойдетесь, вопервых могут быть различные графики рабочего времени, ктото будет работать с 8 а ктото с 9, а ктото посуточно и т.п., во вторых как вы заметили выходные и праздники, а еще сокращенные/продленные дни, переносы, вечерние/ночные, увольнительные и т.д. Тут много чего всплывает, задача не так проста как это может показаться, и решение получается весьма жирное. Есть у меня системка учета задач, там я разделяю понятия когда задача поставлена и когда она начата - отдельные поля в таблице, дата начала проставляется исполнителем. Пытаться "угадывать" момент начала исполнения не разумно, тут также есть много моментов, ну разве что сделать совсем "тупенько", обратите внимание и на этот факт.

спустя 16 часов [обр] Дмитрий Кучкин(2/236)[досье]

Допустим, таблица заданий имеет такую структуру: tasks (id serial, name varchar, time_begin timestamp, time_end timestamp)
Можно создать таблицу worktime (time_begin timestamp,time_end timestamp), куда вписать все промежутки рабочего времени:

...
insert into worktime values ('2009-10-19 09:00:00','2009-10-19 18:00:00');
insert into worktime values ('2009-10-20 09:00:00','2009-10-20 18:00:00');
insert into worktime values ('2009-10-21 09:00:00','2009-10-21 18:00:00');
insert into worktime values ('2009-10-22 09:00:00','2009-10-22 18:00:00');
insert into worktime values ('2009-10-23 09:00:00','2009-10-23 18:00:00');

insert into worktime values ('2009-10-26 09:00:00','2009-10-26 18:00:00');
insert into worktime values ('2009-10-27 09:00:00','2009-10-27 18:00:00');
insert into worktime values ('2009-10-28 09:00:00','2009-10-28 18:00:00');
insert into worktime values ('2009-10-29 09:00:00','2009-10-29 18:00:00');
insert into worktime values ('2009-10-30 09:00:00','2009-10-30 18:00:00');

insert into worktime values ('2009-11-02 09:00:00','2009-11-02 18:00:00');
... и т.д.

Вот такой запрос выведет все задания, где начальное время будет заменено ближайшим началом рабочего промежутка, если начальное время не попадает ни в один рабочий промежуток:

select tasks.id, tasks.name,
    case when worktime.time_begin is not null then tasks.time_begin else workbegin.time_begin end as time_begin_actual,
    tasks.time_end
from tasks
left join worktime
  on tasks.time_begin between worktime.time_begin and worktime.time_end
inner join (select id,min(worktime.time_begin) as time_begin from tasks
            left join worktime on tasks.time_begin<worktime.time_begin
            group by tasks.id, tasks.name) workbegin
  on workbegin.id=tasks.id
order by tasks.id;

Если создать индексы, то запрос должен выполняться довольно быстро

p.s. Проверял в postgresql

спустя 12 минут [обр] Дмитрий Кучкин(2/236)[досье]
Если дополнить таблицу worktime полем, куда занести количество нерабочего времени между окончанием предыдущего промежутка и началом текущего, можно будет составить запрос, учитывающий это нерабочее время при расчете времени решения задачи.
спустя 3 часа 32 минуты [обр] Дмитрий Кучкин(2/236)[досье]
Кстати, запрос очень сильно упростится (исчезнет подзапрос), если во вспомогательную таблицу заносить промежутки НЕрабочего времени вместо рабочего.
Плюс к этому становится необязательным дополнительное поле длительности нерабочего времени, потому что ее будет просто вычислить.
спустя 56 минут [обр] Дмитрий Кучкин(2/236)[досье]
Нравятся мне такие задачки :)
Если нужна продолжительность выполнения задачи только в рабочих часах, без вывода начала и окончания, то проще считать с первым вариантом таблицы рабочего времени.
select tasks.id, tasks.name,
    sum (case when worktime.time_end<tasks.time_end then worktime.time_end else tasks.time_end end -
         case when worktime.time_begin>tasks.time_begin then worktime.time_begin else tasks.time_begin end)
         as worktime
from tasks
left join worktime
  on worktime.time_begin<tasks.time_end and worktime.time_end>tasks.time_begin
group by tasks.id, tasks.name
order by tasks.id;
спустя 1 час 45 минут [обр] ddd(0/36)[досье]

я попозже отпишусь, сейчас в раздумьях.
уже сто лет к sql не подходил, да и сто лет назад это было не по профилю.

еще ситуации(алерт = задача):
1 Алерт пришел в рабочее время и был закрыт в рабочее
2 Пришел в рабочее, но был закрыт в нерабочее
3 Пришел в нерабочее (ночью, выходные) и был закрыт в рабочее
4 Пришел в нерабочее и был закрыт в нерабочее (например, автоматически системой или сотрудником из дома)

вопрос что как считать спихнули на менеджера, ждем :)

спустя 3 минуты [обр] ddd(0/36)[досье]
кстати, по ходу дела задача усложнилась разбивкой типов задач на отделы, а у отделов свои графики и часовые пояса =) и допустимое время реакции у все разное =)
в общем, таймаут до постановки задачи.
спустя 1 час 59 минут [обр] Thirteensmay(0/157)[досье]
В принципе полноценного расчета рабочего времени вам не надо, всяких отпусков по беременности и донорских дней не будет, значит чуть проще, но тем не менее... Графики постоянные/скользящие с перерывами и переходом через сутки, праздники, сокращенные/продленные дни, возможно сверхурочные, отработки/переносы, интервальные вычисления типа описанного вами выше в четырех пунктах. Все это слишком для одного SQL, у меня получился приличный пакет хранимых процедур. По опыту могу посоветовать сразу закладываться на объяснение расчета, т.е. чтобы можно было посмотреть почему и как получена та или иная цифра, ибо таких вопросов в последствии будет много, да и самому вам отлаживать будет проще.
спустя 2 дня 17 часов [обр] ddd(0/36)[досье]

Дмитрий Кучкин[досье]

а если я имея дату возникновения и дату завершения задачи хочу получить продолжительность задачи в минутах, исходя их таблицы с нерабочими промежутками для каждой даты (для выходных это весь день) как мне проще вести таблицу с этими промежутками и какой должен быть запрос, значит? я немного запутался в ваших постах..

Thirteensmay[досье]
да, я уже попрощался с идеей сделать это одним запросом.. написал курсор, и в нем уже пытаюсь для каждой записи вытянуть промежуток рабочего времени..

спустя 1 час 49 минут [обр] Thirteensmay(0/157)[досье]
Задачу поставили ? что там вылезло ? если более менее серьезно, типа того что я описал, то с наскоку не получится, не с курсора надо начинать, а пожалуй с графиков. Или требования "простенькие" ?
спустя 14 минут [обр] ddd(0/36)[досье]

На данный момент задача поставлена так, что все отделы работают по одному графику, будет заполнятся таблица, где для каждой даты указаны промежутки, которые вычитаются из времени потраченного на задачу. промежутков для дня может быть несколько (обычно три, с 00:00 до 09:00, с 13:00 до 14:00 и с 18:00 до 24:00).
Иными словами в таблице будут забиты все выходные, праздники, сокращенные дня и т.д. Больничные и отгулы не учитываем, так как задача ставится не на человека, а на отдел.

Вопрос по структуре этой таблички и по запросу выборки из нее :)

спустя 20 часов [обр] ddd(0/36)[досье]

Что-то я в конец запутался :(
Есть таблица с нерабочим временем

recid   time_begin   time_end
3   2009-11-02 00:00:00.000   2009-11-02 09:00:00.000
4   2009-11-02 14:00:00.000   2009-11-02 15:00:00.000
5   2009-11-02 18:00:00.000   2009-11-02 23:59:59.990

Есть таблица с заданием:

id   time_begin   time_end   name
1   2009-11-02 12:30:00.000   2009-11-02 15:30:00.000   мыл полы

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

1 мыл полы 120

Или, если проще, можно в таблице со временем указать рабочие промежутки.

спустя 1 час 22 минуты [обр] Thirteensmay(0/157)[досье]
Очевидно что
  1. По таблице с заданием (t) вычисляем "сырое" время t.time_end - t.time_begin
  2. Из таблицы перерывов (b) извлекаем все которые пересекаются с нашим заданием, (входят в него), или полностью его накрывают, чтото типа:
b.time_begin between t.time_begin and t.time_end
or b.time_end between t.time_begin and t.time_end
or (t.time_begin between b.time_begin and b.time_end
    and t.time_end between b.time_begin and b.time_end)
  1. Подрезаем извлеченные перерывы, отсекаем у них то что торчит за пределы задания, в цикле, типа:
if b.time_begin < t.time_begin then b.time_begin := t.time_begin;
if b.time_end > t.time_end then b.time_end := t.time_end;
  1. Вычисляем результирующее время каждого перерыва b.time_end - b.time_begin, складываем полученные значения, из "сырого" времени задания вычитаем полученную сумму перерывов.
спустя 13 часов [обр] Дмитрий Кучкин(2/236)[досье]

ddd[досье]

а если я имея дату возникновения и дату завершения задачи хочу получить продолжительность задачи в минутах, исходя их таблицы с нерабочими промежутками для каждой даты (для выходных это весь день) как мне проще вести таблицу с этими промежутками и какой должен быть запрос, значит? я немного запутался в ваших постах

Проще будет работать с таблицей, в которую занесены все рабочие промежутки:

...
insert into worktime values ('2009-10-29 09:00:00','2009-10-29 14:00:00');
insert into worktime values ('2009-10-29 15:00:00','2009-10-29 18:00:00');
insert into worktime values ('2009-10-30 09:00:00','2009-10-30 14:00:00');
insert into worktime values ('2009-10-30 15:00:00','2009-10-30 18:00:00');
insert into worktime values ('2009-11-02 09:00:00','2009-11-02 14:00:00');
insert into worktime values ('2009-11-02 15:00:00','2009-11-02 18:00:00');
... и т.д.

Выбрать продолжительность задания, учитывая только рабочие промежутки, - запрос из последнего поста:

select tasks.id, tasks.name,
    sum (case when worktime.time_end < tasks.time_end then worktime.time_end else tasks.time_end end -
         case when worktime.time_begin > tasks.time_begin then worktime.time_begin else tasks.time_begin end)
         as worktime
from tasks
left join worktime
  on worktime.time_begin < tasks.time_end and worktime.time_end > tasks.time_begin
group by tasks.id, tasks.name
order by tasks.id;

Запрос выводит id, имя задачи и ее продолжительность, суммируя только рабочие промежутки из таблицы worktime, попадающие по времени между началом и окончанием задачи.
Это все работает в postgresql. Насчет mssql не знаю, умеет ли он вычислять разность дат, и работает ли там именно такой условный синтаксис CASE WHEN ... THEN ... ELSE ... END

спустя 1 день 9 часов [обр] ddd(0/36)[досье]

Дмитрий Кучкин[досье]

Кажется, взлетело.
В синтаксисе MSSQL ваш запрос выглядит как

select tasks.id, tasks.name,
sum (datediff(minute,case when worktime.time_begin > tasks.time_begin then worktime.time_begin else tasks.time_begin end,
     case when worktime.time_end < tasks.time_end then worktime.time_end else tasks.time_end end))
        as worktime
from tasks
left join worktime
  on worktime.time_begin < tasks.time_end and worktime.time_end > tasks.time_begin
group by tasks.id, tasks.name
order by tasks.id;

В первом приближении все ок.

Спасибо огромное!

спустя 5 дней [обр] ddd(0/36)[досье]
эх.. если время начала и время конца задачи вне интервалов таблицы worktime (например, возникло в 01.00, а закрыто в 02.00), то запрос выдает сумму в этом задании "сырую", не учитывает, что время не рабочее.. возвращал бы 0 или нул...
спустя 14 часов [обр] Дмитрий Кучкин(2/236)[досье]
Надо просто добавить в case условия, когда worktime.time_begin и worktime.time_end IS NULL.
В этом случае можно просто подставлять одну и ту же дату. Разность даст 0.
Для postgresql:
select tasks.id, tasks.name,
    sum (case when worktime.time_end is null then '1900-01-01 00:00:00'::timestamp
              when worktime.time_end < tasks.time_end then worktime.time_end
              else tasks.time_end end -
         case when worktime.time_begin is null then '1900-01-01 00:00:00'::timestamp
              when worktime.time_begin > tasks.time_begin then worktime.time_begin
              else tasks.time_begin end)
         as worktime
from tasks
left join worktime
  on worktime.time_begin < tasks.time_end and worktime.time_end > tasks.time_begin
group by tasks.id, tasks.name
order by tasks.id;
спустя 5 минут [обр] Дмитрий Кучкин(2/236)[досье]
Или так. Выдает null
select tasks.id, tasks.name,
    sum (case when worktime.time_end < tasks.time_end or worktime.time_end is null then worktime.time_end else tasks.time_end end -
         case when worktime.time_begin > tasks.time_begin or worktime.time_begin is null then worktime.time_begin else tasks.time_begin end)
         as worktime
from tasks
left join worktime
  on worktime.time_begin < tasks.time_end and worktime.time_end > tasks.time_begin
group by tasks.id, tasks.name
order by tasks.id;
спустя 14 минут [обр] Дмитрий Кучкин(2/236)[досье]
C выводом null можно сделать совсем красиво - просто поменять условие
select tasks.id, tasks.name,
    sum (case when worktime.time_end > tasks.time_end then tasks.time_end else worktime.time_end end -
         case when worktime.time_begin < tasks.time_begin then tasks.time_begin else worktime.time_begin end)
         as worktime
from tasks
left join worktime
  on worktime.time_begin < tasks.time_end and worktime.time_end > tasks.time_begin
group by tasks.id, tasks.name
order by tasks.id;
спустя 8 часов [обр] ddd(0/36)[досье]
Дмитрий Кучкин[досье]
Снимаю шляпу.
Powered by POEM™ Engine Copyright © 2002-2005