Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Как вычислить рабочее время для каждого дня в диапазоне дат?  [new]
MinistrBob
Member

Откуда: Москва
Сообщений: 278
Есть выборка которая возвращает дату начала работы над задачей и дату конца работы над задачей одного человека. Нужно посчитать сколько времени (в секундах) работал человек над этой задачей с учётом рабочего времени с 9:00 до 18:00 (выходные дни можно считать за рабочие - это облегчает задачу).

Есть такое:
SecondsIdRevRevisedDateChangedDateProjectName
2786994572017-11-30 16:02:58.0772017-11-27 10:37:59.640SPIvanov


А должно получиться что-то типа такого:
Name27.11.201728.11.201729.11.201730.11.2017
Ivanov587928800288007142

здесь 27.11 - 5879 секунд это разница между 10:37:59 и 09:00
а 30.11 - 7142 секунд это разница между 16:02:58 и 18:00
а 28000 - это 8 часов рабочего времени.

Куда хоть копать? ума не приложу как эту фиговину вытащить.
10 янв 18, 14:27    [21093483]     Ответить | Цитировать Сообщить модератору
 Re: Как вычислить рабочее время для каждого дня в диапазоне дат?  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20535
Создай (возможно, динамически) таблицу рабочего времени по дням, заведомо перекрывающую весь диапазон задачи - и всё станет легко и просто.
10 янв 18, 14:32    [21093499]     Ответить | Цитировать Сообщить модератору
 Re: Как вычислить рабочее время для каждого дня в диапазоне дат?  [new]
MinistrBob
Member

Откуда: Москва
Сообщений: 278
Akina,

А можно поподробнее, я не совсем понимаю. Это типа такой?

date1date2
......
2017-11-28 09:00:00.0002017-11-28 18:00:00.000
2017-11-29 09:00:00.0002017-11-29 18:00:00.000
2017-11-30 09:00:00.0002017-11-30 18:00:00.000
......
10 янв 18, 14:38    [21093522]     Ответить | Цитировать Сообщить модератору
 Re: Как вычислить рабочее время для каждого дня в диапазоне дат?  [new]
petre
Member

Откуда: Кривой Рог
Сообщений: 42
MinistrBob,
Попробуйте сначала правильно расставить временные интервалы:
Наверное работы были начаты 27.11 а закончены 30.11.
Из этого и получаем, что 27-го необходимо находить длительность период с 10:37:59 до 18:00, а 30.11 - с 9:00 до 16:02:58 .
Ну а дальше календарь рабочих дней поможет.
10 янв 18, 14:43    [21093531]     Ответить | Цитировать Сообщить модератору
 Re: Как вычислить рабочее время для каждого дня в диапазоне дат?  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20535
MinistrBob
Это типа такой?
Подойдёт...
10 янв 18, 14:46    [21093544]     Ответить | Цитировать Сообщить модератору
 Re: Как вычислить рабочее время для каждого дня в диапазоне дат?  [new]
MinistrBob
Member

Откуда: Москва
Сообщений: 278
petre,

На да при описании ошибся, вы правильно указали временные интервалы. А дальше то что делать? Откуда взять календарь рабочих дней и как он мне поможет?
10 янв 18, 14:59    [21093598]     Ответить | Цитировать Сообщить модератору
 Re: Как вычислить рабочее время для каждого дня в диапазоне дат?  [new]
MinistrBob
Member

Откуда: Москва
Сообщений: 278
Akina,

А как их объединить, я чего-то не догоняю?
10 янв 18, 14:59    [21093600]     Ответить | Цитировать Сообщить модератору
 Re: Как вычислить рабочее время для каждого дня в диапазоне дат?  [new]
petre
Member

Откуда: Кривой Рог
Сообщений: 42
MinistrBob,
Сочинил, думаю правильно:
select case when tt.dateBegin>tc.date1 then tt.dateBegin else tc.date1 as BeginDate
       ,case when tt.dateEnd<tc.date2 then tt.dateend else tc.date2 as EndDate
 from tabletask as tt, tableCal as tc
 where tc.date1 between tt.dateBegin and tt.dateEnd or tc.date2 between tt.dateBegin and tt.dateEnd

Календарь можно сгенерировать по разному, неоднократно описывалось на форуме. Для теста можете вручную создать.
10 янв 18, 15:06    [21093641]     Ответить | Цитировать Сообщить модератору
 Re: Как вычислить рабочее время для каждого дня в диапазоне дат?  [new]
MinistrBob
Member

Откуда: Москва
Сообщений: 278
petre,

Ооо, спасибо, буду пробовать.
10 янв 18, 15:10    [21093674]     Ответить | Цитировать Сообщить модератору
 Re: Как вычислить рабочее время для каждого дня в диапазоне дат?  [new]
petre
Member

Откуда: Кривой Рог
Сообщений: 42
MinistrBob,

в предыдущем запросе потерял два слова:
DECLARE @tabletask TABLE (
  [idtask] int,
  [dateBegin] datetime,
  [dateEnd] datetime)
INSERT INTO @tabletask
VALUES
(45, '2017-11-27 10:37:59.640', '2017-11-30 16:02:58.077')

DECLARE @tableCal TABLE (
  [date1] datetime,
  [date2] datetime)
INSERT INTO @tableCal
VALUES
('2017-11-26 09:00', '2017-11-26 18:00'),
('2017-11-27 09:00', '2017-11-27 18:00'),
('2017-11-28 09:00', '2017-11-28 18:00'),
('2017-11-29 09:00', '2017-11-29 18:00'),
('2017-11-30 09:00', '2017-11-30 18:00')

select sum(datediff(ss,begindate, endDate))
 from (
select case when tt.dateBegin>tc.date1 then tt.dateBegin else tc.date1 end as BeginDate
       ,case when tt.dateEnd<tc.date2 then tt.dateend else tc.date2 end as EndDate
 from @tabletask as tt, @tableCal as tc
 where tc.date1 between tt.dateBegin and tt.dateEnd or tc.date2 between tt.dateBegin and tt.dateEnd
 ) a
10 янв 18, 15:51    [21093920]     Ответить | Цитировать Сообщить модератору
 Re: Как вычислить рабочее время для каждого дня в диапазоне дат?  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
case when tt.dateBegin>tc.date1 then tt.dateBegin else tc.date1 end as BeginDate

+ очередной офф-топик

Какой там уже сервер на дворе? 2017.
А год какой? 2018.

XML - 2005
CLR - 2005
LEAD/LAG - 2008R2
CONCAT - 2012
JSON - 2016
STRING_AGG - 2017
REGEXP - NEVER
LEAST/GREATEST - NEVER


Ладно, регулярки сами по себе - зло, и только упертые будут реализовывать их, благо CLR есть.
Но отсутствие скалярных MIN/MAX бесит уже на протяжении 13 лет.
И если CASE на 2 переменных можно переварить, то когда нужно выбрать наименьшее из 3+ полей... оу еее...
И не нужно мне рассказывать про
SELECT MIN( [v] ) FROM ( VALUES ( 1 ), ( 2 ), ( NULL ), ( -1 ) ) t( [v] )
- performance degrade убивает на корню всю красоту этого решения.

При этом внедряются всяческие фичи вроде File Tables, Column Store Indexes, Sparse Columns, Memory Tables, которые реально использует с десяток контор...
10 янв 18, 18:26    [21094519]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить