SQL: задача о рабочем времени / forpes.ru

Главная
SQL: задача о рабочем времени

SQL: задача о рабочем времени +16

21.05.2019 08:41

bzq 101 8700 Источник

Здравствуйте, в эфире снова Радио SQL! Разминайте ганглии, расправляйте псевдоподии (или наоборот?) и настраивайтесь на нашу гравитационную волну!

В прошлый раз меня чуть было не подвергли остракизму за разбор (https://habr.com/ru/post/359064/) олимпиадной задачи на SQL, якобы она была недостаточно приближена к жизни. Как будто теги «ненормальное программирование» и «олимпиада» не говорят сами за себя. Но, очевидно, теги никто не читает! И тем не менее, я всё равно продолжу тему разбора задачек на замечательном языке программирования SQL. Потому что лапки (чешутся).

Сегодня нас ждёт задача исключительно жЫзненная, и даже практически рабочая. С ней я столкнулся, пытаясь посчитать выполнение SLA по заявкам от любивных пользователей. Суть исходной проблемы в следующем: надо было посчитать продолжительность работы по каждой заявке и сравнить с тем, что мы обещали. Всё бы ничего, но время в обязательствах было декларировано рабочее, а из изменений статусов в заявках я мог получить только календарное. И тут – мысль! Вот же она, задачечка! Не слишком сложная, но и не совсем тривиальная. Как раз чтобы размять центральные отделы ваших вегетативных нервных систем, сделав их более симпатическими!

Итак, сформулирую условие.

Есть несколько временных интервалов, заданных датой-временем своего начала и конца (пример в синтаксисе PostgreSQL):

with periods(id, start_time, stop_time) as (
  values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp), 
        (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp), 
        (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
        (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
)

Требуется в один SQL-запрос (ц) вычислить продолжительность каждого интервала в рабочих часах. Считаем, что рабочими у нас являются будние дни с понедельника по пятницу, рабочее время всегда с 10:00 до 19:00. Кроме того, в соответствии с производственным календарём РФ существует некоторое количество официальных праздничных дней, которые рабочими не являются, а какие-то из выходных дней, наоборот, являются рабочими из-за переноса тех самых праздников. Укороченность предпраздничных дней учитывать не надо, считаем их полными. Так как праздничные дни год от года меняются, то есть задаются явным перечислением, то ограничимся датами только из 2018 и 2019 годов. Уверен, что при необходимости решение можно будет легко дополнить.

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

 id |     start_time      |      stop_time      | work_hrs 
----+---------------------+---------------------+----------
  1 | 2019-03-29 07:00:00 | 2019-04-08 14:00:00 | 58:00:00
  2 | 2019-04-10 07:00:00 | 2019-04-10 20:00:00 | 09:00:00
  3 | 2019-04-11 12:00:00 | 2019-04-12 16:07:12 | 13:07:12
  4 | 2018-12-28 12:00:00 | 2019-01-16 16:00:00 | 67:00:00

Исходные данные на корректность не проверяем, считаем всегда start_time <= stop_time.

Специальными конструкциями диалекта SQL от PostgreSQL пользоваться можно, но злоупотреблять не надо. Для полной корректности условия дополню, что запрос должен выполняться на PostgreSQL версии 10 или старше.

Через месяц будет разбор задачи. Решения не привожу, чтобы был стимул порешать самостоятельно. Убедительная просьба — код в комментариях размещайте под спойлерами!

Last but not least. Если уж меня угораздило разместить эту статью в корпоративном блоге Postgres Professional, то воспользуемся некоторыми корпоративными плюшками: за самое интересное решение этой задачи разыграем бесплатный поход на PGConf.Russia 2020. Критерии интересности будут лично мои, плюс тех из коллег, с кем я сочту нужным посоветоваться. Удачи!

UPDATE! Что-то я смотрю, что рабочие часы воспринимаются почему-то исключительно без рабочих минут. Минуты не забывайте! Я поправил исходные интервалы и ожидаемый ответ, чтобы подчеркнуть наличие минут.

Комментарии (101)

faustxp
21.05.2019 12:29
#20179712
1. Перечисление праздников в WITH входит в понятие одного запроса?
2. Рабочий день 9 или 8 часов?
3. Учитывать предпраздничные укороченные дни?
1. ZaEzzz
  21.05.2019 12:38
  #20179782
  Что-то мне подсказывает:
  1) Скорее всего вылеты по праздникам и рабочим в выходные находятся в отдельной таблице и эти данные входят в рамки одного запроса.
  2) Если в теле речь про SLA — скорее всего без обеда. Т.е. 9 часов.
  3) Явно указано, что предпраздничные обычные.
1. aleksandy
  21.05.2019 12:39
  #20179786
  2. Учитывая, что стандартный рабочий день — это 8 часов, то, скорее всего, второе.
  
  3. В задании же написано
  
  Укороченность предпраздничных дней учитывать не надо, считаем их полными.
  1. faustxp
    21.05.2019 12:45
    #20179818
    В ответе за 1 день стоит 9 часов, так что верно первое.
1. bzq Автор
  21.05.2019 12:46
  #20179824
  Мне кажется, что удобнее задать праздники именно в CTE.
  По остальным вопросам все ответы есть в условии: рабочее время с 10:00 до 19:00, что составляет девять часов; предпраздничный день является полным.

InChaos
21.05.2019 13:25
#20180080
Ваш же ответ некорректен Вами же поставленным условиям.
Заявка 2:
Если условие, что работа строго с 10:00 до 19:00, то закрытие заявки не может быть в 20:00 по определению. Но ответ — 9 часов, что соответствует 19:00. Но если верно что заявка закрыта в 20:00, значит человек переработал, и заявка выполнялась 10 часов, но тогда опять же по условиям она должна быть закрыта на следующий день 2019-04-11 в 11:00.

bzq Автор
21.05.2019 13:42
#20180172
Если условие, что работа строго с 10:00 до 19:00, то закрытие заявки не может быть в 20:00 по определению

Почему не может? Никаким условиям не противоречит. Условия говорят, что нужно посчитать, сколько часов из интервала были рабочими.
1. InChaos
  21.05.2019 14:18
  #20180372
  Кто закрыл заявку? Если в 19:00 все ушли.
  1. bzq Автор
    21.05.2019 14:43
    #20180510
    Чтобы не вступать в длительную и бесплодную дискуссию, сам пользователь закрыл. (:

the_unbridled_goose

21.05.2019 14:02

#20180272

Решение в лоб

with periods(id, start_time, stop_time) as (
    values (1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
           (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
           (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:00:00'::timestamp),
           (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
),
     holidays (h_date) as (
         values ('2018-01-01'),
                ('2018-01-02'),
                ('2018-01-03'),
                ('2018-01-04'),
                ('2018-01-05'),
                ('2018-01-07'),
                ('2018-01-08'),
                ('2018-02-23'),
                ('2018-03-08'),
                ('2018-05-01'),
                ('2018-05-09'),
                ('2018-06-12'),
                ('2018-11-04'),
                ('2019-01-01'),
                ('2019-01-02'),
                ('2019-01-03'),
                ('2019-01-04'),
                ('2019-01-05'),
                ('2019-01-07'),
                ('2019-01-08'),
                ('2019-02-23'),
                ('2019-03-08'),
                ('2019-05-01'),
                ('2019-05-09'),
                ('2019-06-12'),
                ('2019-11-04')
     ),
     magic as (
         select id,
                start_time,
                stop_time,
                dd
         from periods,
              generate_series(start_time, stop_time - interval '1 hour', '1 hour') dd
         where extract(dow from dd) between 1 and 5
           and extract(hours from dd) >= 10
           and extract(hours from dd) < 19
           and not exists(select 0 from holidays where h_date::date = dd::date))
select id, start_time, stop_time, count(1) as work_hrs
from magic
group by id, start_time, stop_time
order by id
;

bzq Автор

21.05.2019 16:59

#20181106

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

the_unbridled_goose

21.05.2019 17:29

#20181230

Добавил еще шаманства для минут и секунд.

Еще больше загадочности

with periods(id, start_time, stop_time) as (
    values (1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
           (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
           (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
           (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
),
     holidays (h_date) as (
         values ('2018-01-01'),
                ('2018-01-02'),
                ('2018-01-03'),
                ('2018-01-04'),
                ('2018-01-05'),
                ('2018-01-07'),
                ('2018-01-08'),
                ('2018-02-23'),
                ('2018-03-08'),
                ('2018-05-01'),
                ('2018-05-09'),
                ('2018-06-12'),
                ('2018-11-04'),
                ('2019-01-01'),
                ('2019-01-02'),
                ('2019-01-03'),
                ('2019-01-04'),
                ('2019-01-05'),
                ('2019-01-07'),
                ('2019-01-08'),
                ('2019-02-23'),
                ('2019-03-08'),
                ('2019-05-01'),
                ('2019-05-09'),
                ('2019-06-12'),
                ('2019-11-04')
     ),
     magic as (
         select id,
                start_time,
                stop_time,
                dd,
                case
                    when dd = last_value(dd) over (partition by id, start_time, stop_time) and
                         extract(hours from stop_time) between 10 and 19
                        then stop_time - last_value(dd) over (partition by id, start_time, stop_time)
                    else interval '1 hour' end as w_hours
         from periods,
              generate_series(start_time, stop_time, '1 hour') dd
         where extract(dow from dd) between 1 and 5
           and extract(hours from dd) >= 10
           and extract(hours from dd) < 19
           and not exists(select 0 from holidays where h_date::date = dd::date)
     )
select id, start_time, stop_time, to_char(sum(w_hours), 'HH24:MI:SS') as work_hrs
from magic
group by id, start_time, stop_time
order by id;

bzq Автор

21.05.2019 17:40

#20181284

Хорошая боевая магия, но пока колдунства недостаточно. Попробуйте IDDQD.

the_unbridled_goose

21.05.2019 18:13

#20181504

Почему недостаточно?) Результат получен, в логике расхождений с истиной особо не вижу, что не так?

bzq Автор

21.05.2019 18:49

#20181654

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

the_unbridled_goose

21.05.2019 19:20

#20181802

Вот так вот должно быть хорошо.

Просветление достигнуто

with periods(id, start_time, stop_time) as (
    values (1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
           (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
           (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
           (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
),
     holidays (h_date) as (
         values ('2018-01-01'),
                ('2018-01-02'),
                ('2018-01-03'),
                ('2018-01-04'),
                ('2018-01-05'),
                ('2018-01-07'),
                ('2018-01-08'),
                ('2018-02-23'),
                ('2018-03-08'),
                ('2018-05-01'),
                ('2018-05-09'),
                ('2018-06-12'),
                ('2018-11-04'),
                ('2019-01-01'),
                ('2019-01-02'),
                ('2019-01-03'),
                ('2019-01-04'),
                ('2019-01-05'),
                ('2019-01-07'),
                ('2019-01-08'),
                ('2019-02-23'),
                ('2019-03-08'),
                ('2019-05-01'),
                ('2019-05-09'),
                ('2019-06-12'),
                ('2019-11-04')
     ),
     magic as (
         select id,
                to_char(start_time, 'yyyy-mm-dd HH24:MI:SS') as start_time,
                to_char(stop_time, 'yyyy-mm-dd HH24:MI:SS')  as stop_time,
                dd,
                case
                    when dd = last_value(dd) over (partition by id, start_time, stop_time) and
                         extract(hours from stop_time) between 10 and 18
                        then stop_time - last_value(dd) over (partition by id, start_time, stop_time)
                    else interval '1 hour' end               as w_hours
         from periods,
              generate_series(start_time, stop_time, '1 hour') dd
         where extract(dow from dd) between 1 and 5
           and extract(hours from dd) >= 10
           and extract(hours from dd) < 19
           and not exists(select 0 from holidays where h_date::date = dd::date)
     )
select id, start_time, stop_time, to_char(sum(w_hours), 'HH24:MI:SS') as work_hrs
from magic
group by id, start_time, stop_time
order by id;

bzq Автор

22.05.2019 13:04

#20184734

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

the_unbridled_goose

22.05.2019 13:30

#20184892

В очередной раз понял, нашел и исправил.

n + 1

with periods(id, start_time, stop_time) as (
    values (1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
           (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
           (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
           (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
),
     holidays (h_date) as (
         values ('2018-01-01'),
                ('2018-01-02'),
                ('2018-01-03'),
                ('2018-01-04'),
                ('2018-01-05'),
                ('2018-01-07'),
                ('2018-01-08'),
                ('2018-02-23'),
                ('2018-03-08'),
                ('2018-05-01'),
                ('2018-05-09'),
                ('2018-06-12'),
                ('2018-11-04'),
                ('2019-01-01'),
                ('2019-01-02'),
                ('2019-01-03'),
                ('2019-01-04'),
                ('2019-01-05'),
                ('2019-01-07'),
                ('2019-01-08'),
                ('2019-02-23'),
                ('2019-03-08'),
                ('2019-05-01'),
                ('2019-05-09'),
                ('2019-06-12'),
                ('2019-11-04')
     ),
     magic as (
         select id,
                to_char(start_time, 'yyyy-mm-dd HH24:MI:SS') as start_time,
                to_char(stop_time, 'yyyy-mm-dd HH24:MI:SS')  as stop_time,
                dd,
                first_value(dd) over (partition by id, start_time, stop_time),
                last_value(dd) over (partition by id, start_time, stop_time),
                case
                    when dd = last_value(dd) over (partition by id, start_time, stop_time) and
                         extract(hours from stop_time) between 10 and 18
                        then stop_time - last_value(dd) over (partition by id, start_time, stop_time)
                    when dd = first_value(dd) over (partition by id, start_time, stop_time) and
                         extract(hours from start_time) between 10 and 18
                        then first_value(dd) over (partition by id, start_time, stop_time) + '1 hour' - start_time
                    else interval '1 hour' end               as w_hours
         from periods,
              generate_series(date_trunc('hour', start_time), date_trunc('hour', stop_time), '1 hour') dd
         where extract(dow from dd) between 1 and 5
           and extract(hours from dd) >= 10
           and extract(hours from dd) < 19
           and not exists(select 0 from holidays where h_date::date = dd::date)
     )
select id, start_time, stop_time, to_char(sum(w_hours), 'HH24:MI:SS') as work_hrs
from magic
group by id, start_time, stop_time
order by id;

bzq Автор

22.05.2019 16:26

#20185916

Потерялись периоды с пустым рабочим временем, нет учёта дополнительных рабочих дней. И что-то как-то распухла магия-то. (:

the_unbridled_goose

22.05.2019 18:39

#20186614

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

finally

with periods(id, start_time, stop_time) as (
    values (1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
           (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
           (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
           (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
),
     holidays (h_date) as (
         values ('2018-01-01'),
                ('2018-01-02'),
                ('2018-01-03'),
                ('2018-01-04'),
                ('2018-01-05'),
                ('2018-01-07'),
                ('2018-01-08'),
                ('2018-02-23'),
                ('2018-03-08'),
                ('2018-05-01'),
                ('2018-05-09'),
                ('2018-06-12'),
                ('2018-11-04'),
                ('2018-12-31'),
                ('2019-01-01'),
                ('2019-01-02'),
                ('2019-01-03'),
                ('2019-01-04'),
                ('2019-01-05'),
                ('2019-01-07'),
                ('2019-01-08'),
                ('2019-02-23'),
                ('2019-03-08'),
                ('2019-05-01'),
                ('2019-05-09'),
                ('2019-06-12'),
                ('2019-11-04')
     ),
     subst_days (s_date) as (
         values ('2018-04-28'),
                ('2018-06-09'),
                ('2018-12-29')
     ),
     magic as (
         select id,
                to_char(start_time, 'yyyy-mm-dd HH24:MI:SS') as start_time,
                to_char(stop_time, 'yyyy-mm-dd HH24:MI:SS')  as stop_time,
                case
                    when dd = last_value(dd) over (partition by id, start_time, stop_time) and
                         extract(hours from stop_time) between 10 and 18
                        then stop_time - last_value(dd) over (partition by id, start_time, stop_time)
                    when dd = first_value(dd) over (partition by id, start_time, stop_time) and
                         extract(hours from start_time) between 10 and 18
                        then first_value(dd) over (partition by id, start_time, stop_time) + '1 hour' - start_time
                    when extract(hours from dd) < 10
                        or extract(hours from dd) >= 19 then interval '0 hours'
                    else interval '1 hour' end               as w_hours
         from periods,
              generate_series(date_trunc('hour', start_time), date_trunc('hour', stop_time), '1 hour') dd
         where (exists(select 1 from subst_days where dd::date = s_date::date) or extract(dow from dd) between 1 and 5)
           and not exists(select 0
                          from holidays
                          where h_date::date = dd::date)
     )
select id, start_time, stop_time, to_char(sum(w_hours), 'HH24:MI:SS') as work_hrs
from magic
group by id, start_time, stop_time
order by id;

bzq Автор

23.05.2019 17:38

#20191802

Не, не выходит каменный цветок. На вот таких периодах некорректно считает:
('2018-06-10 18:46:10', '2018-06-13 10:18:18'),
('2019-04-28 21:00:00', '2019-04-28 21:00:00').
На первом неверное значение (18:32:08 вместо 18:18), второй совсем теряет.

PS некритично, но всё же выходные в holidays — неправильные данные, я у себя для тестов исправлял.

the_unbridled_goose

23.05.2019 18:42

#20192138

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

...

with periods(id, start_time, stop_time) as (
    values (1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
           (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
           (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
           (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp),
           (5, '2018-06-10 18:46:10'::timestamp, '2018-06-13 10:18:18'::timestamp),
           (6, '2019-04-28 21:00:00'::timestamp, '2019-04-28 21:00:00'::timestamp)
),
     holidays (h_date) as (
         values ('2018-01-01'),
                ('2018-01-02'),
                ('2018-01-03'),
                ('2018-01-04'),
                ('2018-01-05'),
                ('2018-01-07'),
                ('2018-01-08'),
                ('2018-02-23'),
                ('2018-03-08'),
                ('2018-05-01'),
                ('2018-05-09'),
                ('2018-06-12'),
                ('2018-11-04'),
                ('2018-12-31'),
                ('2019-01-01'),
                ('2019-01-02'),
                ('2019-01-03'),
                ('2019-01-04'),
                ('2019-01-05'),
                ('2019-01-07'),
                ('2019-01-08'),
                ('2019-02-23'),
                ('2019-03-08'),
                ('2019-05-01'),
                ('2019-05-09'),
                ('2019-06-12'),
                ('2019-11-04')
     ),
     subst_days (s_date) as (
         values ('2018-04-28'),
                ('2018-06-09'),
                ('2018-12-29')
     ),
     magic as (
         select id,
                to_char(start_time, 'yyyy-mm-dd HH24:MI:SS') as start_time,
                to_char(stop_time, 'yyyy-mm-dd HH24:MI:SS')  as stop_time,
                case
                    when dd = last_value(dd) over (partition by id, start_time, stop_time) and
                         extract(hours from stop_time) between 10 and 18
                        then stop_time - last_value(dd) over (partition by id, start_time, stop_time)
                    when extract(hours from dd) < 10
                        or extract(hours from dd) >= 19 then interval '0 hours'
                    when dd = first_value(dd) over (partition by id, start_time, stop_time) and
                         extract(hours from start_time) between 10 and 18
                        then first_value(dd) over (partition by id, start_time, stop_time) + '1 hour' - start_time
                    else interval '1 hour' end               as w_hours
         from periods,
              generate_series(date_trunc('hour', start_time), date_trunc('hour', stop_time), '1 hour') dd
         where (exists(select 1 from subst_days where dd::date = s_date::date) or extract(dow from dd) between 1 and 5)
           and not exists(select 0
                          from holidays
                          where h_date::date = dd::date)
         union all
         select id,
                to_char(start_time, 'yyyy-mm-dd HH24:MI:SS') as start_time,
                to_char(stop_time, 'yyyy-mm-dd HH24:MI:SS')  as stop_time,
                interval '0 hours'
         from periods,
              generate_series(date_trunc('hour', start_time), date_trunc('hour', stop_time), '1 hour') dd
         where not exists(select 1
                          from subst_days
                          where dd::date = s_date::date
                             or extract(dow from dd) between 6 and 7
                             or exists(select 0
                                       from holidays
                                       where h_date::date = dd::date)
             ))
select id, start_time, stop_time, to_char(sum(w_hours), 'HH24:MI:SS') as work_hrs
from magic
group by id, start_time, stop_time
order by id;

XareH

21.05.2019 14:11

#20180316

Скрытый текст

with periods (id,start_time, stop_time) as(
  VALUES(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
        (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
        (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:00:00'::timestamp),
        (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
),
minimax AS (select min(start_time),max(stop_time) FROM periods),
days AS(SELECT gs.d,CASE WHEN extract(isodow from gs.d) IN (6,7) THEN  coalesce(holy.iswrk,false) ELSE coalesce(holy.iswrk,true) END iswrk
FROM minimax
JOIN LATERAL  generate_series(min::date,max::date,'24:00') gs(d) ON 1=1
left join (values ('2019-01-01'::date,false),('2019-01-02',false),('2019-01-03',false),('2019-01-04',false),('2019-01-07',false),('2019-01-08',false),('2019-03-08',false),
('2019-05-01',false),('2019-05-02',false),('2019-05-03',false),('2019-05-09',false),('2019-05-10',false),('2019-06-12',false),('2019-11-04',false),
('2018-01-01',false),('2018-01-02',false),('2018-01-03',false),('2018-01-04',false),('2018-01-05',false),('2018-01-08',false),('2018-02-23',false),
('2018-03-08',false),('2018-03-09',false),('2018-04-28',true),('2018-04-30',false),('2018-05-01',false),('2018-05-02',false),('2018-05-09',false),
('2018-06-09',true),('2018-06-11',false),('2018-06-12',false),('2018-11-05',false),('2018-12-29',true),('2018-12-31',false)) as holy(d,iswrk)
on gs.d=holy.d)

SELECT id,start_time,stop_time, (count(*)-2)*9+
  CASE when extract(hour from start_time)>=19 THEN 0
       when extract(hour from start_time)<=10 THEN 9
       ELSE 19-extract(hour from start_time)
  END+
  CASE WHEN extract(hour from stop_time)>=19 THEN 9
       WHEN extract(hour from stop_time)<=10 THEN 0
       ELSE extract(hour from stop_time)-10
  END
FROM periods JOIN days 
ON 
  d>=start_time::date
  AND d<=stop_time::date
  AND iswrk
GROUP BY id,start_time,stop_time
ORDER BY id

XareH

21.05.2019 15:20

#20180674

Учел заявки, которые сами закрылись в нерабочее время

 SELECT id,start_time,stop_time, (count(d))*9
  -CASE
       WHEN count(d)=0 OR start_time::date<min(d) THEN 0
       when extract(hour from start_time)>=19 THEN 9
       when extract(hour from start_time)<=10 THEN 0
       ELSE extract(hour from start_time)-10
  END
  -CASE WHEN count(d)=0 OR stop_time::date>max(d) THEN 0 
       WHEN extract(hour from stop_time)>=19 THEN 0
       WHEN extract(hour from stop_time)<=10 THEN 9
       ELSE 19-extract(hour from stop_time)
  END
FROM periods LEFT JOIN days 
ON 
  d>=start_time::date
  AND d<=stop_time::date
  AND iswrk
GROUP BY id,start_time,stop_time
ORDER BY id

bzq Автор

21.05.2019 16:57

#20181102

Да, так лучше.

XareH

21.05.2019 18:17

#20181518

минуты и секунды

SELECT id,start_time,stop_time, (count(d))*interval '9 hour'
  -CASE
       WHEN count(d)=0 OR start_time::date<min(d) THEN interval '0 hour'
       when start_time - min(d)>=interval '19 hour' THEN interval '9 hour'
       when  start_time-min(d)<=interval '10 hour' THEN interval '0 hour'
       ELSE start_time -min(d) - interval '10 hour'
  END
  -CASE WHEN count(d)=0 OR stop_time::date>max(d) THEN interval '0 hour' 
       WHEN  stop_time - max(d)>=interval '19 hour' THEN interval '0 hour'
       WHEN stop_time - max(d)<=interval '10 hour' THEN interval '9 hour'
       ELSE  interval '19 hour' - ( stop_time - max(d))
  END
FROM periods LEFT JOIN days 
ON 
  d>=start_time::date
  AND d<=stop_time::date
  AND iswrk
GROUP BY id,start_time,stop_time
ORDER BY id

XareH

21.05.2019 22:40

#20182576

Проверил скрипт на большом объеме данных и понял, что не правильно использую LATERAL

Так будет быстрее

with periods(id, start_time, stop_time) as (
  values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp), 
        (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp), 
        (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
        (4, '2018-12-29 20:00:00'::timestamp, '2019-01-3 16:00:00'::timestamp)

), holy(dd,iswrk) AS (values ('2019-01-01'::date,false),('2019-01-02',false),('2019-01-03',false),('2019-01-04',false),('2019-01-07',false),
('2019-01-08',false),('2019-03-08',false),('2019-05-01',false),('2019-05-02',false),('2019-05-03',false),('2019-05-09',false),('2019-05-10',false),
('2019-06-12',false),('2019-11-04',false),('2018-01-01',false),('2018-01-02',false),('2018-01-03',false),('2018-01-04',false),('2018-01-05',false),
('2018-01-08',false),('2018-02-23',false),('2018-03-08',false),('2018-03-09',false),('2018-04-28',true),('2018-04-30',false),('2018-05-01',false),
('2018-05-02',false),('2018-05-09',false),('2018-06-09',true),('2018-06-11',false),('2018-06-12',false),('2018-11-05',false),('2018-12-29',true),
('2018-12-31',false)) 

SELECT  id,start_time,stop_time, (count(gs.d))*interval '9 hour'
  -CASE
       WHEN count(d)=0 OR start_time::date<min(d) THEN interval '0 hour'
       when start_time - min(d)>=interval '19 hour' THEN interval '9 hour'
       when start_time - min(d)<=interval '10 hour' THEN interval '0 hour'
       ELSE start_time-min(d)-interval '10 hour'
  END
  -CASE WHEN count(d)=0 OR stop_time::date>max(d) THEN interval '0 hour'
       WHEN stop_time-max(d)>=interval '19 hour' THEN interval '0 hour'
       WHEN stop_time-max(d)<=interval '10 hour' THEN interval '9 hour'
       ELSE interval '19 hour'- (stop_time-max(d))
  END
FROM periods 
LEFT JOIN LATERAL  (SELECT * FROM generate_series(start_time::date,stop_time::date,'1 day') gs(d) left join holy ON gs.d=holy.dd )gs(d,dd,iswrk) ON 
CASE WHEN extract(isodow from gs.d) IN (6,7) THEN coalesce(iswrk,false) ELSE coalesce(iswrk,true) END 

GROUP BY id,start_time,stop_time
ORDER BY id;

bzq Автор
22.05.2019 13:31
#20184896
Бинго! Этот вариант запроса уже дал на моих тестовых данных правильный результат.

uaggster
21.05.2019 14:20
#20180388
ИМХО, всё просто.
Через рекурсивное CTE воссоздадим список дней от, включительно, 01.01.2018 до 31.12.2019.
Производственный календарь берём вот тут:
data.gov.ru/opendata/resource/8ba5011a-233e-4e01-a1d2-ff5598d0f34f#2/0.0/0.0
Это текстовый файл, и легко, также в CTE, может быть развернут в линейный список.
Ну и далее Select из periods, outer apply (Select SUM(дни) from первое СТЕ inner join второе СТЕ, со списком рабочих дней, если день — рабочий, и день >= стартовой даты и <=конечной даты из periods).

Как то так.

Tatikoma
21.05.2019 15:53
#20180814
Про сокращенные рабочие дни как-то забыли. Недостаточно реалистично :-)
1. bzq Автор
  22.05.2019 16:15
  #20185836
  Почему-то ни разу не видел, чтобы в SLA указывали, что предпраздничные рабочие дни на час короче. В SLA обычно пишут «бла-бла-бла за N часов в рабочее время» и рабочее время определено «с/по в рабочие дни кроме выходных и официальных праздников», всё.
  
  Буду рад, если хоть одно из решений позволит учесть укороченные предпраздничные дни. Когда я эту задачу решал в реальной жизни, я на это заложился, но не пригодилось.

bzq Автор
21.05.2019 16:01
#20180854
Пожалуйста прячьте код под спойлер!

bzq Автор
21.05.2019 16:09
#20180878
Что-то я смотрю, что рабочие часы воспринимаются почему-то исключительно без рабочих минут. Минуты не забывайте! Я поправил исходные интервалы и ожидаемый ответ, чтобы подчеркнуть наличие минут.

bzq Автор
21.05.2019 16:55
#20181084
Решение от DanStopka, прислано мне в личку:

спойлер
Добрый день! Для решения задачи зарегистрировался на хабре, комменты оставлять не дает.
Мое решение:
with periods(id, start_time, stop_time) as ( values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp), (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp), (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:00:00'::timestamp), (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp) ), hollydays(dt) as (values ('2019-03-29')) select p.id, start_time, stop_time, cnt work_hrs from periods join ( select id, count(1) cnt from (select id, generate_series(start_time, stop_time — interval '1 hour', '1 hour') tm from periods) p where to_char(tm, 'hh24')::int between 10 and 19 — 1 and extract(dow from tm) between 1 and 5 and tm::date not in (select dt::date from hollydays) group by id ) p on p.id = periods.id

vav180480_2

21.05.2019 17:11

#20181158

-1

Тута

with periods(id, start_time, stop_time) as (
  values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp), 
        (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp), 
        (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:00:00'::timestamp),
        (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
)

select id, count(*) - (case when extract(hour from stop_time) between 10 and 19 then 1 else 0 end) hours
  from periods p, generate_series(start_time, stop_time, interval '1 hour') as i
 where to_char(i, 'D') not in ('1', '7') and extract(HOUR FROM i) between 10 and 18
 group by id, stop_time

StrangerInTheKy

21.05.2019 18:02

#20181436

Я этот ваш постгрес уважаю, конечно, но по жизни работаю с ораклом. Такшта простите, если чо. Нагуглил какие-то функции для работы с интервалами и генерации последовательностей, взял первое, что попалось.

У меня в 9.5 все работает.

with periods(id, start_time, stop_time) as (
  values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp), 
        (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp), 
        (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
        (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
    ),
  wd as ( -- это все дни 2018 и 2019 годов
         select workday + '10 hour'::interval st, workday + '19 hour'::interval en -- начало и конец раб. дня
           from (select '2018-01-01'::timestamp + i * '1 day'::interval workday
		   from generate_series(0,729) i
		  where mod(i, 7) not in (5, 6)  -- минус выходные
		  -- плюс рабочие выходные
		  union all select '2018-04-28'::timestamp
		  union all select '2018-06-09'::timestamp
		  union all select '2018-12-29'::timestamp
                 -- минус праздничные будни
		 except select '2018-01-01'::timestamp
		 except select '2018-01-02'::timestamp
		 except select '2018-01-03'::timestamp
		 except select '2018-01-04'::timestamp
		 except select '2018-01-05'::timestamp
		 except select '2018-01-08'::timestamp
		 except select '2018-02-23'::timestamp
		 except select '2018-03-08'::timestamp
		 except select '2018-03-09'::timestamp
		 except select '2018-04-30'::timestamp
		 except select '2018-05-01'::timestamp
		 except select '2018-05-02'::timestamp
		 except select '2018-05-09'::timestamp
		 except select '2018-06-11'::timestamp
		 except select '2018-06-12'::timestamp
		 except select '2018-11-05'::timestamp
		 except select '2018-12-31'::timestamp
		 except select '2019-01-01'::timestamp
		 except select '2019-01-02'::timestamp
		 except select '2019-01-03'::timestamp
		 except select '2019-01-04'::timestamp
		 except select '2019-01-07'::timestamp
		 except select '2019-01-08'::timestamp
		 except select '2019-03-08'::timestamp
		 except select '2019-05-01'::timestamp
		 except select '2019-05-02'::timestamp
		 except select '2019-05-03'::timestamp
		 except select '2019-05-09'::timestamp
		 except select '2019-05-10'::timestamp
		 except select '2019-06-12'::timestamp
		 except select '2019-11-04'::timestamp) t
	)
-- собсна решение начинается тут:
select id, start_time, stop_time, sum(work_end - work_start) work_hrs 
  from (select p.id, p.start_time, p.stop_time,
	       case when p.start_time > wd.st then p.start_time else wd.st end work_start,
	       case when p.stop_time < wd.en then p.stop_time else wd.en end work_end
	from periods p join wd 
	     on p.start_time < wd.en and p.stop_time > wd.st
	) tt
 group by id, start_time, stop_time
 order by id;

bzq Автор
21.05.2019 18:27
#20181570
Респект, работает! Для «неродной» системы вообще супер. Хотя generate_series можно использовать более прямо, сразу даты генерировать.

Envek

21.05.2019 18:04

#20181450

Каюсь, я слегка подсмотрел в первые два решения (в частности, стырил оттуда график выходных, ибо самому его делать было лень), но они мне не понравились, потому что при подсчёте времени сам собой напрашивается встроенный в PostgreSQL тип interval (и я его очень сильно люблю, хоть сам почти и не пользуюсь). Поэтому я пошёл и сделал всё по своему. Поэтому, вот:

Решение на типе interval

with periods(id, start_time, stop_time) as (
  values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp), 
        (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp), 
        (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
        (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
),
holiday_overrides(day, working) AS (
  values ('2019-01-01'::date,false),('2019-01-02',false),('2019-01-03',false),('2019-01-04',false),('2019-01-07',false),('2019-01-08',false),('2019-03-08',false),('2019-05-01',false),('2019-05-02',false),('2019-05-03',false),('2019-05-09',false),('2019-05-10',false),('2019-06-12',false),('2019-11-04',false),('2018-01-01',false),('2018-01-02',false),('2018-01-03',false),('2018-01-04',false),('2018-01-05',false),('2018-01-08',false),('2018-02-23',false),('2018-03-08',false),('2018-03-09',false),('2018-04-28',true),('2018-04-30',false),('2018-05-01',false),('2018-05-02',false),('2018-05-09',false),('2018-06-09',true),('2018-06-11',false),('2018-06-12',false),('2018-11-05',false),('2018-12-29',true),('2018-12-31',false)
),
period_worktime(period_id, worktime) AS (
  SELECT 
    periods.id AS period_id,
    SUM(
      CASE 
        WHEN days.day::date = periods.start_time::date THEN (days.day::date + 'PT19H'::interval) - greatest(periods.start_time, days.day::date + 'PT10H'::interval)
        WHEN days.day::date = periods.stop_time::date  THEN least(periods.stop_time, days.day::date + 'PT19H'::interval) - (days.day::date + 'PT10H'::interval)
        ELSE 'PT9H'::interval
      END
    ) AS worktime
  FROM periods JOIN LATERAL generate_series(start_time::date, stop_time::date, '1 day'::interval) days(day) ON 1=1
  WHERE
    NOT EXISTS (SELECT * FROM holiday_overrides ho WHERE ho.day = days.day::date AND working=false)
    AND (
         extract(isodow from days.day) NOT IN (6,7) 
      OR EXISTS (SELECT * FROM holiday_overrides ho WHERE ho.day = days.day::date AND working=true)
    )
  GROUP BY period_id
)
SELECT periods.*, period_worktime.worktime
FROM periods JOIN period_worktime ON periods.id = period_worktime.period_id;

bzq Автор
21.05.2019 18:41
#20181624
Согласен, интервалы здесь очень в тему.

mi888
21.05.2019 20:46
#20182158
Скрытый текст
Пишу с телефона, потому не код а слова напишу:
Как насчет просто посчитать время рабочее краев диапазона плюс( (всего_дней минус празничных_или_выходных) умножить на 8)
1. bzq Автор
  22.05.2019 13:35
  #20184926
  Да, алгоритм-то несложен — посчитать количество рабочих дней, правильно учесть первый и последний дни, всё сложить. Осталось это выразить на SQL.
  PS умножить надо на 9

puyol_dev2
21.05.2019 22:08
#20182440
Вообще по логике в каждом интервале отсекается день начала и день окончания. Считается количество рабочих дней в каждом интервале (для каждой недели 5 дней * на количество недель + доп рабочие дни — праздники) и умножается на 8. Потом прибавляются рабочие часы начала интервала (19-00 минус время начала) и конца интервала (10-00 плюс время окончания). То есть по сути задача сводится, на мой взгляд, к расчету количества рабочих недель
1. vav180480_2
  21.05.2019 22:15
  #20182472
  Не надо мне ничего говорить, просто покажи свой код (с) не помню
  1. puyol_dev2
    21.05.2019 22:17
    #20182482
    Кодом пусть занимаются программисты ))
    
    Вообще расчет количества недель довольно быстро гуглится
    
    www.sqlines.com/postgresql/how-to/datediff
    
    + конечно забыл про неполные недели для интервалов. Полагаю нужно вычислить день недели начала и конца интервалов, исключить эти дни и посчитать по 5 дневке
    
    vav180480_2
    22.05.2019 07:24
    #20183282
    Зачем так много говорить? Просто покажи код… хотя бы нагугленный:)
    
    puyol_dev2
    22.05.2019 12:50
    #20184642
    Смотри под спойлером SQL
    
    vav180480
    22.05.2019 13:46
    #20184990
    Посмотрел, занятно, а с минутами что? Там под id=3 результат не верный.
    
    puyol_dev2
    23.05.2019 09:21
    #20188662
    -1
    Верный. Если ты отработал 13:07:12, значит в часах это 14, а не 13
1. findoff
  21.05.2019 23:47
  #20182764
  Начал писать с такой же логикой, закончил когда понял насколько это усложняет решение, и что в реальных кейсах (с интервалами допустим в пределах года), мы получаем увеличение O сложности в 1-4 раза по сравнению с оптимизированным, за счет сильного увеличения сложности алгоритма и ухудшения читаемости.
  А практической необходимости такой оптимизации и вовсе придумать не смог.
  
  Сложность оценивал как O(periods * holidays + periods + periods * holidays_overrides) для оптимального, и O(periods * (holidays + periods_weekdays - holidays_overrides)) для решения с генерацией всех выходных. Исходя из того что нам нужно обрабатывать много периодов, но не столетия с кучей выходных.
  1. puyol_dev2
    22.05.2019 12:47
    #20184624
    Ну вот что получилось с округлением до часов
    
    SQL
    with periods(id, start_time, stop_time) as ( values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp), (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp), (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp), (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp) ) , calc_table as ( select id, date_part('day', date_trunc('day',stop_time) - date_trunc('day',start_time))::int as date_diff, date_trunc('day',start_time)+ interval '10 hour' as start_work_time, date_trunc('day',stop_time)+ interval '19 hour' as end_work_time from periods ) -- промежуточная таблица вычислений select p.*, trunc(c.date_diff/7)*5*9 -- количество полных недель в рабочих часах + c.date_diff%7*9 -- дробная часть в рабочих часах - case when trunc(c.date_diff/7) > 0 and c.date_diff%7 > 0 then (extract(isodow from start_time) - c.date_diff%7)*9 else 0 end -- корректировка дробной части на выходные дни + (9 - case when c.end_work_time > stop_time then date_part('hour', c.end_work_time - stop_time) else 0 end - case when start_time > c.start_work_time then date_part('hour', start_time - c.start_work_time) else 0 end) -- корректировка рабочих часов начала и конца интервала - case when p.id = 4 then 9 * 9 else 0 end -- количество праздничных дней в 4м интервале + case when p.id = 4 then 1 * 9 else 0 end -- 1 дополнительный рабочий день в 4м интервале as work_hrs from periods as p inner join calc_table as c on p.id = c.id
    
    bzq Автор
    22.05.2019 14:28
    #20185194
    Вроде работает, но без минут нехорошо.

vav180480_2
22.05.2019 08:32
#20183426
удалено

vav180480
22.05.2019 08:38
#20183442
Итерации по дням посекундная точность
with periods(id, start_time, stop_time) as ( values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp), (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp), (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:00:00'::timestamp), (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp) ) ,holidays (start_time, stop_time) as ( values('2019-01-01'::timestamp, '2019-01-08'::timestamp) ) select id, start_time, stop_time ,sum(least(i + interval '19 hour', stop_time) - greatest(i + interval '10 hour', start_time)) work_hrs from periods p ,generate_series(start_time::date, stop_time, interval '1 day') as i where to_char(i, 'D') not in ('1', '7') and not exists ( select 1 from holidays where i between start_time and stop_time) group by id, start_time, stop_time order by id
1. vav180480
  22.05.2019 09:34
  #20183624
  Решение с исправленными исходными данными
  
  итерации по дням с посекундной точностью
  with periods(id, start_time, stop_time) as ( values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp), (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp), (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp), (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp) ) ,holidays (start_time, stop_time) as ( values('2019-01-01'::timestamp, '2019-01-08'::timestamp) ) select id, start_time, stop_time ,sum(least(i + interval '19 hour', stop_time) - greatest(i + interval '10 hour', start_time)) work_hrs from periods p ,generate_series(start_time::date, stop_time, interval '1 day') as i where to_char(i, 'D') not in ('1', '7') and not exists ( select 1 from holidays where i between start_time and stop_time) group by id, start_time, stop_time order by id
  
  puyol_dev2
  22.05.2019 12:49
  #20184636
  Твой код не работает
  
  SQL Error [42P01]: ОШИБКА: отношение «periods» не существует
  Позиция: 162
  
  vav180480
  22.05.2019 13:21
  #20184828
  1) мой код работает
  2) правильно писать «твой код не работает в ...» далее нужно конкретно написать где
  3) я делал в этой песочнице: rextester.com
  Language: PostgreSQL
  усё робит
  4) где ты поджигал?
  там табличные выражения поддерживаются?
  
  puyol_dev2
  22.05.2019 13:33
  #20184908
  Ты уже исправил. Молодец. Я тебе код ошибки выложил предыдущего твоего нерабочего варианта
  
  vav180480
  22.05.2019 13:38
  #20184946
  1) Предыдущий работает так же как и первый, исправлены только и исключительно исходные данные.
  2) Я не могу редактировать сообщения через полчаса после их опубликования, я их опубликовал несколько часов назад.
  3) Я не могу редактировать сообщения после того как на них ответили, а ты ответил.
  4) Врать и говнокодить — не хорошо.
  
  puyol_dev2
  22.05.2019 13:44
  #20184980
  Я не знаю, что ты можешь, а что нет. Но у твоего сообщения стоит метка, что ты его редактировал
  
  vav180480
  22.05.2019 13:52
  #20185016
  1) Не знаешь, не делай предположений и не озвучивай этих предположений, в зависимости от ситуации это может выглядеть например обидно или например смешно
  2) Попробуй отредактировать СВОЕ сообщение которое ты написал более получаса назад
  
  bzq Автор
  22.05.2019 14:41
  #20185286
  Решение не учитывает праздничные дни.
  
  Про задачку с интервалами сформулируйте более формально, чтобы было понятно какими входными данными оперировать и в какой форме результат получить. С удовольствием порешаю на досуге.
  
  vav180480
  22.05.2019 14:47
  #20185324
  какие конкретно праздничные дни не учтены? у меня ответ не верен? мой ответ не совпал с вашим?
  
  bzq Автор
  22.05.2019 15:23
  #20185526
  Да, в условии интервалы могут быть из 2018-2019 гг., а Вы учли только новогодние праздники 2019 года.
  
  vav180480
  22.05.2019 15:31
  #20185566
  Зачем загромождать решение? Лично мне лениво заниматься мартышкиным трудом и расписывать все праздники за два года, когда нужно только 8 дней одного года. Оно будет нагляднее если вместо одной строки в holidays будет двадцать две? Как это повлияет на результат? Как это повлияет на алгоритм? Или вам мой алгоритм кажется уж слишком лаконичным?:) Ведь в реале будут не табличные выражения periods и holidays, а таблички базы данных periods и hilidays. Я добавил только те праздники которые попадают в заданные интервалы.
  
  bzq Автор
  22.05.2019 16:03
  #20185754
  Я отчасти с Вами согласен, но тем не менее в условии я просил учесть праздники за два года и мне сперва пришлось «при необходимости решение можно будет легко дополнить», чтобы погонять на тестовых данных. Я бы не стал проявлять излишний формализм, если бы это был единственный нюанс Вашего решения. Но у Вас не учитываются дополнительные рабочие дни, например, 2018-04-28 был рабочим днём. Также потерялись в выводе периоды, которые совсем не попали на рабочее время.
  А вот компактность Вашего решения мне очень нравится.
  
  vav180480
  22.05.2019 16:19
  #20185876
  ниже привел решение с учетом рабочих дней на выходных

OrmEugensson

22.05.2019 15:27

#20185552

К сожалению, на Postgres пишу только для развлечения (основная RDBMS — MS Sql Server), поэтому код может быть не идеален с точки зрения использования Postgres специфического синтаксиса.

Код без итерации по дням

with cte_periods(id, start_time, stop_time) as (
    values
		(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
        (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
        (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
        (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
), cte_holidays as (
   select
		h.date::date
   from (values
		('2018-01-01'),
		('2018-01-02'),
		('2018-01-03'),
		('2018-01-04'),
		('2018-01-05'),
		('2018-01-07'),
		('2018-01-08'),
		('2018-02-23'),
		('2018-03-08'),
		('2018-05-01'),
		('2018-05-09'),
		('2018-06-12'),
		('2018-11-04'),
		('2019-01-01'),
		('2019-01-02'),
		('2019-01-03'),
		('2019-01-04'),
		('2019-01-05'),
		('2019-01-07'),
		('2019-01-08'),
		('2019-02-23'),
		('2019-03-08'),
		('2019-05-01'),
		('2019-05-09'),
		('2019-06-12'),
		('2019-11-04')
	) as h(date)
), cte_holidays_no_weekends as (
	select h.date
	from cte_holidays as h
    where
 		extract(isodow from h.date) not in (6,7)
), cte_periods_extended as (
    select
    	p.id,
        case when p.start_time::time < '10:00' then '10:00' else p.start_time::time end as start_time,
        case when p.stop_time::time > '19:00' then '19:00' else p.stop_time::time end as stop_time,
        p.start_time::date as start_date,
        p.stop_time::date as stop_date,
        p.stop_time::date - p.start_time::date - 1 as days_count,
        (p.stop_time::date - '2017-01-07'::date) / 7 - (p.start_time::date - '2017-01-07'::date) / 7 as saturdays_count,
        (p.stop_time::date - '2017-01-08'::date) / 7 - (p.start_time::date - '2017-01-08'::date) / 7 as sundays_count
    from cte_periods as p
)
select
	p.id,
	p.start_date + p.start_time as start_time,
	p.stop_date + p.stop_time as stop_time,
	case
	    when p.start_date = p.stop_date then
	        p.stop_time - p.start_time
		else
		    p.stop_time - p.start_time + '9:00' +
			(p.days_count - p.saturdays_count - p.sundays_count - h.holidays_count) * 9 * interval '1 hour'
	end as work_hours
from cte_periods_extended as p
    cross join lateral (
        select count(*)
        from cte_holidays_no_weekends as c
        where
        	c.date >= p.start_date and
            c.date <= p.stop_date
	) as h(holidays_count)

bzq Автор

22.05.2019 15:41

#20185624

Интересный подход, пока из опубликованных решений никто не пробовал так считать, но есть ошибка в реализации. Запустите на интервале ('2019-01-01 21:00:00', '2019-01-01 21:00:00'), эффект неожиданный.

OrmEugensson
22.05.2019 16:29
#20185922
уверен, что есть неучтенные кейсы, в целом я протестировал только на данной выборке (и не проверял на отрицательные величины). Проверю сегодня вечером. Что мне нравится в таком решении, так это то, что оно не зависит от длины периодов — меньше вероятность, что оно перестанет работать с течением времени.

OrmEugensson

22.05.2019 16:59

#20186074

Не выдержал, поправил, добавил учёт перенесённых выходных (добавил только пару дней в 2018 году для тестирования)

Код без итерации по дням + учет перенесенных выходных дней

with cte_periods(id, start_time, stop_time) as (
    values
		(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
        (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
        (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
        (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp),
        (5, '2019-01-01 21:00:00'::timestamp, '2019-01-01 21:00:00'::timestamp),
        (6, '2018-04-27 09:00:00'::timestamp, '2018-05-03 15:01:00'::timestamp),
        (7, '2019-01-01 09:00:00'::timestamp, '2019-01-01 09:15:00'::timestamp)
), cte_holidays as (
   select
		h.date::date
   from (values
		('2018-01-01'),
		('2018-01-02'),
		('2018-01-03'),
		('2018-01-04'),
		('2018-01-05'),
		('2018-01-07'),
		('2018-01-08'),
		('2018-02-23'),
		('2018-03-08'),
		('2018-04-30'),
		('2018-05-01'),
        ('2018-05-02'),
		('2018-05-09'),
		('2018-06-12'),
		('2018-11-04'),
		('2019-01-01'),
		('2019-01-02'),
		('2019-01-03'),
		('2019-01-04'),
		('2019-01-05'),
		('2019-01-07'),
		('2019-01-08'),
		('2019-02-23'),
		('2019-03-08'),
		('2019-05-01'),
		('2019-05-09'),
		('2019-06-12'),
		('2019-11-04')
	) as h(date)
), cte_working_weekend as (
   select
		h.date::date
   from (values
		('2018-04-28')
	) as h(date)
), cte_holidays_no_weekends as (
	select h.date
	from cte_holidays as h
    where
 		extract(isodow from h.date) not in (6,7)
), cte_periods_extended as (
    select
    	p.id,
        p.start_time as original_start_time,
        p.stop_time as original_stop_time,
        case
            when p.start_time::time < '10:00' then '10:00'
            when p.start_time::time > '19:00' then '19:00'
            else p.start_time::time
        end as start_time,
        case
            when p.stop_time::time < '10:00' then '10:00'
            when p.stop_time::time > '19:00' then '19:00'
            else p.stop_time::time
        end as stop_time,
        p.start_time::date as start_date,
        p.stop_time::date as stop_date,
        p.stop_time::date - p.start_time::date - 1 as days_count,
        (p.stop_time::date - '2017-01-07'::date) / 7 - (p.start_time::date - '2017-01-07'::date) / 7 as saturdays_count,
        (p.stop_time::date - '2017-01-08'::date) / 7 - (p.start_time::date - '2017-01-08'::date) / 7 as sundays_count
    from cte_periods as p
)
select
	p.id,
	p.original_start_time as start_time,
	p.original_stop_time as stop_time,
	case
	    when p.start_date = p.stop_date then
	        p.stop_time - p.start_time
		else
		    p.stop_time - p.start_time + '9:00' +
			(p.days_count - p.saturdays_count - p.sundays_count - h.holidays_count + ww.working_weekends_count) * 9 * interval '1 hour'
	end as work_hours
from cte_periods_extended as p
    cross join lateral (
        select count(*) from cte_holidays_no_weekends as tt where tt.date between p.start_date and p.stop_date
    ) as h(holidays_count)
    cross join lateral (
        select count(*) from cte_working_weekend as tt where tt.date between p.start_date and p.stop_date
	) as ww(working_weekends_count)

bzq Автор

22.05.2019 18:50

#20186682

Что ж не добавили в рабочие дни ещё 2018-06-09 и 2018-12-29? Не так уж это и утомительно.

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

OrmEugensson

23.05.2019 16:53

#20191570

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

bzq Автор

23.05.2019 18:15

#20192006

У Вас 2018-06-10 подхватывается рабочим днём. Вот пример данных, где неверно считается:
('2018-06-09 16:51:40', '2018-06-10 10:55:50')
Предварительно я Ваш запрос скорректировал правильными праздниками и доп. рабочими днями:

Заголовок спойлера

with ...

cte_holidays as (

 select h.date::date

 from (values('2018-01-01'), -- 2018

 ('2018-01-02'),

 ('2018-01-03'),

 ('2018-01-04'),

 ('2018-01-05'),

 ('2018-01-08'),

 ('2018-02-23'),

 ('2018-03-08'),

 ('2018-03-09'),

 ('2018-05-01'),

 ('2018-05-02'),

 ('2018-05-09'),

 ('2018-06-11'),

 ('2018-06-12'),

 ('2018-11-05'),

 ('2018-12-31'),

 ('2019-01-01'), -- 2019

 ('2019-01-02'),

 ('2019-01-03'),

 ('2019-01-04'),

 ('2019-01-07'),

 ('2019-01-08'),

 ('2019-03-08'),

 ('2019-05-01'),

 ('2019-05-02'),

 ('2019-05-03'),

 ('2019-05-09'),

 ('2019-05-10'),

 ('2019-06-12'),

 ('2019-11-04') ) as h(date)

), cte_working_weekend as (

 select h.date::date

 from (values ('2018-04-28'),

 ('2018-06-09'),

 ('2018-12-29') ) as h(date)

), ...

OrmEugensson

23.05.2019 23:31

#20193044

Переделал логику немного. Считаются отдельно полные рабочие дни + время начала и конца периода. Главное достоинство этого подхода — он гораздо меньше зависит от длины периода (по сравнению с решениями, которые множат периоды на дни). Возможно, конкретно в этой задаче это не так уж важно (сомнительно, что в системе заявок будут заявки, которые открыты годами)

Код без итерации по дням + tsrange для остатка

with cte_periods(id, start_time, stop_time) as (
    values
		(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
		(2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
		(3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
		(4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp),
		(5, '2019-01-01 21:00:00'::timestamp, '2019-01-01 21:00:00'::timestamp),
		(6, '2018-06-09 16:51:40'::timestamp, '2018-06-10 10:55:50'::timestamp)
), cte_holidays as (
   select
		h.date::date
   from (values
		('2018-01-01'), -- 2018
		('2018-01-02'),
		('2018-01-03'),
		('2018-01-04'),
		('2018-01-05'),
		('2018-01-08'),
		('2018-02-23'),
		('2018-03-08'),
		('2018-03-09'),
		('2018-05-01'),
		('2018-05-02'),
		('2018-05-09'),
		('2018-06-11'),
		('2018-06-12'),
		('2018-11-05'),
		('2018-12-31'),
		('2019-01-01'), -- 2019
		('2019-01-02'),
		('2019-01-03'),
		('2019-01-04'),
		('2019-01-07'),
		('2019-01-08'),
		('2019-03-08'),
		('2019-05-01'),
		('2019-05-02'),
		('2019-05-03'),
		('2019-05-09'),
		('2019-05-10'),
		('2019-06-12'),
		('2019-11-04')
	) as h(date)
), cte_working_weekend as (
   select
		h.date::date
   from (values
		('2018-04-28'),
		('2018-06-09'),
		('2018-12-29')
	) as h(date)
), cte_holidays_improved as (
	select h.date
	from cte_holidays as h
	where
		extract(isodow from h.date) not in (6,7)
)
select
	p.id,
	p.start_time,
	p.stop_time,
	coalesce(a1.days, 0) * interval '9 hour' + coalesce(a2.hours, interval '0 hour')
from cte_periods as p
	cross join lateral (select p.start_time::date, p.stop_time::date) as d(start_date, stop_date)
	cross join lateral (select d.stop_date - d.start_date) as k(date_diff)
	left join lateral (
		select
			k.date_diff - 2 -
			((d.stop_date - '0001-01-07'::date) / 7 - (d.start_date - '0001-01-05'::date) / 7) -
 			((d.stop_date - '0001-01-08'::date) / 7 - (d.start_date - '0001-01-06'::date) / 7) -
 			(select count(*) from cte_holidays_improved as tt where tt.date > d.start_date and tt.date < d.stop_date) +
			(select count(*) from cte_working_weekend as tt where tt.date > d.start_date and tt.date < d.stop_date)
 		where
 			k.date_diff > 1
	) as a1(days) on true
	cross join lateral (
		select sum((upper(b.hours) - lower(b.hours)))
		from (
			select d.start_date, p.start_time, d.start_date + '19:00'::time where k.date_diff >= 1 union all
			select d.stop_date, d.stop_date + '10:00'::time, p.stop_time where k.date_diff >= 1 union all
			select d.start_date, p.start_time, p.stop_time where k.date_diff = 0
		) as a(date, start_time, stop_time)
			cross join lateral (select
				tsrange(a.start_time, a.stop_time, '[]') *
				tsrange(a.date + '10:00'::time, a.date + '19:00'::time, '[]')
			) as b(hours)
		where
			not exists (select * from cte_holidays_improved as tt where tt.date = a.date) and
			(extract(isodow from a.date) not in (6,7) or exists (select * from cte_working_weekend as tt where tt.date = a.date))
	) as a2(hours)

vav180480
22.05.2019 16:18
#20185864
Решение с учетом праздничных дней и с учетом рабочих выходных дней
with periods(id, start_time, stop_time) as ( values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp), (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp), (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp), (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp) ) ,holidays (holiday) as ( values ('2018-01-01'::timestamp), ('2018-01-02'::timestamp), ('2018-01-03'::timestamp), ('2018-01-04'::timestamp), ('2018-01-05'::timestamp), ('2018-01-07'::timestamp), ('2018-01-08'::timestamp), ('2018-02-23'::timestamp), ('2018-03-08'::timestamp), ('2018-05-01'::timestamp), ('2018-05-09'::timestamp), ('2018-06-12'::timestamp), ('2018-11-04'::timestamp), ('2019-01-01'::timestamp), ('2019-01-02'::timestamp), ('2019-01-03'::timestamp), ('2019-01-04'::timestamp), ('2019-01-05'::timestamp), ('2019-01-07'::timestamp), ('2019-01-08'::timestamp), ('2019-02-23'::timestamp), ('2019-03-08'::timestamp), ('2019-05-01'::timestamp), ('2019-05-09'::timestamp), ('2019-06-12'::timestamp), ('2019-11-04'::timestamp) ) ,work_holidays (work_holiday) as ( values ('2018-04-28'::timestamp) ) select id, start_time, stop_time ,sum(least(i + interval '19 hour', stop_time) - greatest(i + interval '10 hour', start_time)) work_hrs from periods p ,generate_series(start_time::date, stop_time, interval '1 day') as i where (to_char(i, 'D') not in ('1', '7') or exists ( select 1 from work_holidays where i = work_holiday) ) and not exists ( select 1 from holidays where i = holiday) group by id, start_time, stop_time order by id
1. bzq Автор
  22.05.2019 16:32
  #20185936
  Круто. Но потерялись периоды, совсем не попадающие на рабочее время.
  
  vav180480
  22.05.2019 17:01
  #20186084
  Я не понял это как? В нерабочее время завели заявку (вечером после работы по пьяни) и в нерабочее время закрыли (утром следующего дня до работы когда протрезвели) вообще не работая?:) Напишите интервальчик для примера и жизненную ситуацию по этому интервальчику.
  
  valery1707
  22.05.2019 17:29
  #20186254
  Заявку заводит человек внешний к тому кто работает в рамках рабочих часов SLA.
  Клиент тех поддержки написал что у него ничего не работает, а потом понял что у него локальная проблема и закрыл её.
  
  А сам пример, с моей точки зрения, намекает что нужно для таких интервалов строку в результате всё же показывать.
  А вот, например, изначально вообще не выводил такой интервал в результате.
  
  vav180480
  22.05.2019 17:36
  #20186294
  Домой приду — попробую, там придется все условия из раздела where в раздел select тащить, а сие некрасиво просто.
  
  valery1707
  22.05.2019 18:49
  #20186670
  У меня в select условий нет.
  А самый сложный where — для рассчёта рабочих дней. В реальном проекте я бы материализовал таблицу с рабочими днями, так что этот where ушёл бы в другое место.
  
  bzq Автор
  23.05.2019 22:29
  #20192888
  Во-первых, в SLA указывают время, когда предоставляется сервис, а не рабочее время исполнителя. Это может совсем не совпадать, хотя удобнее конечно, чтобы графики рабочего времени если не совпадали, то по крайней мере покрывали время в SLA, а то работать будет некому. Но никто не мешает нам оказывать какие-то услуги, например, только в первой половине дня.
  
  Во-вторых, у нас может быть гибкий график. Или в рабочее время человек убежал по своим делам, а вечером задержался (или утром пораньше начал), чтобы успеть доделать всё вовремя.
  
  В-третьих, у нас могут быть офисы в разных часовых поясах. Или в разных городах. В городах поменьше любят график работы с 09 до 18, а в Москве-Питере с 10 до 19.
  
  В-четвёртых, ночной дежурный скучал, от нечего делать подчистил всякие недозакрытые задачки. А может и какую-то свою работу доделал и закрыл.
  
  В-пятых, клиент обратился в нерабочее время, а потом понял, что ложная тревога, или сам справился и закрыл.
  
  В-шестых, бывают запланированные работы в нерабочее время. Каждый dba знает, что кое-что надо делать в отсутствие пользователей.
  
  В-седьмых, иногда в IT бывают авралы, когда просто нужно сделать. Лучше, когда это редко (а ещё лучше если никогда), но иногда бывает.
  
  В-восьмых, пришёл робот и автоматически поменял статусы, просроченное по таймаутам закрыл.
  
  Ну короче не только по пьяни в нерабочее время что-то происходит на работе. Надеюсь, достаточно привёл жизненных примеров. (:

valery1707

22.05.2019 16:40

#20185976

Вот мой вариант

-- https://habr.com/ru/company/postgrespro/blog/448368/
with periods(id, start_time, stop_time) as (
  values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
        (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
        (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
        (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
),

-- http://data.gov.ru/opendata/resource/8ba5011a-233e-4e01-a1d2-ff5598d0f34f#2/0.0/0.0
-- All this block can be persisted in real table
holidays_raw(year, jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec) as (
  values(2018,'1,2,3,4,5,6,7,8,13,14,20,21,27,28','3,4,10,11,17,18,22*,23,24,25','3,4,7*,8,9,10,11,17,18,24,25,31','1,7,8,14,15,21,22,28*,29,30','1,2,5,6,8*,9,12,13,19,20,26,27','2,3,9*,10,11,12,16,17,23,24,30','1,7,8,14,15,21,22,28,29','4,5,11,12,18,19,25,26','1,2,8,9,15,16,22,23,29,30','6,7,13,14,20,21,27,28','3,4,5,10,11,17,18,24,25','1,2,8,9,15,16,22,23,29*,30,31'),
        (2019,'1,2,3,4,5,6,7,8,12,13,19,20,26,27','2,3,9,10,16,17,22*,23,24','2,3,7*,8,9,10,16,17,23,24,30,31','6,7,13,14,20,21,27,28,30*','1,2,3,4,5,8*,9,10,11,12,18,19,25,26','1,2,8,9,11*,12,15,16,22,23,29,30','6,7,13,14,20,21,27,28','3,4,10,11,17,18,24,25,31','1,7,8,14,15,21,22,28,29','5,6,12,13,19,20,26,27','2,3,4,9,10,16,17,23,24,30','1,7,8,14,15,21,22,28,29,31*')
),
holidays_by_month(year, month, day) as (
  select year, 1, unnest(string_to_array(jan, ',')) from holidays_raw
  union all
  select year, 2, unnest(string_to_array(feb, ',')) from holidays_raw
  union all
  select year, 3, unnest(string_to_array(mar, ',')) from holidays_raw
  union all
  select year, 4, unnest(string_to_array(apr, ',')) from holidays_raw
  union all
  select year, 5, unnest(string_to_array(may, ',')) from holidays_raw
  union all
  select year, 6, unnest(string_to_array(jun, ',')) from holidays_raw
  union all
  select year, 7, unnest(string_to_array(jul, ',')) from holidays_raw
  union all
  select year, 8, unnest(string_to_array(aug, ',')) from holidays_raw
  union all
  select year, 9, unnest(string_to_array(sep, ',')) from holidays_raw
  union all
  select year, 10, unnest(string_to_array(oct, ',')) from holidays_raw
  union all
  select year, 11, unnest(string_to_array(nov, ',')) from holidays_raw
  union all
  select year, 12, unnest(string_to_array(dec, ',')) from holidays_raw
),
holidays(day) as (
  select concat(year, '-', month, '-', day)::date
  from holidays_by_month
  -- Звёздочкой помечены сокращённые дни, но по условию задачи они считаются рабочими
  where day NOT like '%*'
),

-- Календарь всех дней на нужный период
calendar(day) as (
  select generate_series(min(start_time), max(stop_time), '1 day')::date from periods
),

-- Только рабочие дни в рамках нашего календаря
workdays(day, begin, until) as (
  select C.day, C.day + '10 hours'::interval, C.day + '19 hours'::interval
  from calendar AS C
  -- todo Тут явно можно сделать оптимальнее
  where (C.day NOT IN (select day from holidays AS H where H.day = C.day))
    and (
      (EXTRACT(DOW FROM C.day) between 1 and 5)
      or
      -- Сокращённый день считается полностью рабочим даже если это суббота
      exists (
        select 1
        from holidays_by_month AS HH
        where HH.year = EXTRACT(YEAR FROM C.day)
          and HH.month = EXTRACT(MONTH FROM C.day)
          and HH.day = concat(EXTRACT(DAY FROM C.day), '*')
      )
    )
)

select P.id
-- , P.start_time, P.stop_time
-- , W.*
-- , LEAST(P.stop_time, W.until) - GREATEST(W.begin, P.start_time)
, to_char(max(P.start_time), 'YYYY-MM-DD HH24:MI:SS') as start_time
, to_char(max(P.stop_time), 'YYYY-MM-DD HH24:MI:SS') as stop_time
, to_char(sum(LEAST(P.stop_time, W.until) - GREATEST(W.begin, P.start_time)), 'HH24:MI:SS') as work_hrs
from periods AS P
left outer join workdays AS W ON (W.day between P.start_time::date AND P.stop_time::date)
group by P.id
order by 1, 4
;

valery1707
22.05.2019 16:43
#20185994
Залил на rextester
https://rextester.com/RUHPF66549

bzq Автор
23.05.2019 12:33
#20189900
На короткой выборке работает правильно, но на боевых даёт ошибку.

На каких данных некорректно работает пока не понял. Чуть позже отвечу, где ошибка.

eranthis

22.05.2019 22:39

#20187422

with periods (id, start_time, stop_time) as (
    values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp), 
          (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp), 
          (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
          (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
)
select periods.*, hours from (
    select id, sum(upper(hours) - lower(hours)) hours
    from (
        select working_day::timestamp from periods
        join generate_series(start_time::date, stop_time::date, '1 day') working_day on extract(isodow from working_day::timestamp) < 6
        except -- holidays
        select * from unnest('{2019-01-01,2019-01-02,2019-01-03,2019-01-04,2019-01-07,2019-01-08,2019-03-08,2019-05-01,2019-05-02,2019-05-03,2019-05-09,2019-05-10,2019-06-12,2019-11-04,2018-01-01,2018-01-02,2018-01-03,2018-01-04,2018-01-05,2018-01-08,2018-02-23,2018-03-08,2018-03-09,2018-04-30,2018-05-01,2018-05-02,2018-05-09,2018-06-11,2018-06-12,2018-11-05,2018-12-31}'::date[])
        union -- working weekends
        select * from unnest('{2018-04-28,2018-06-09,2018-12-29}'::date[])
    )_, tsrange(working_day + '10:00', working_day + '19:00') working_time
    join (periods cross join tsrange(start_time, stop_time) period_range) on period_range && working_time,
    tsrange(period_range * working_time) hours
    group by 1
)_ join periods using (id)

eranthis
22.05.2019 23:17
#20187570
прошу прощения, в первом ответе не закрепил под спойлер.
либо то же самое приклеить к периодам контекстно
with periods (id, start_time, stop_time) as ( values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp), (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp), (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp), (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp) ) select *, ( select sum(upper(hours) - lower(hours)) hours from generate_series(start_time::date, stop_time::date, '1 day')_, "timestamp"(_) period_day, tsrange(period_day + '10:00', period_day + '19:00') working_time join tsrange(start_time, stop_time) period_range on period_range && working_time, tsrange(period_range * working_time) hours where extract(isodow from period_day) < 6 and period_day <> all('{2019-01-01,2019-01-02,2019-01-03,2019-01-04,2019-01-07,2019-01-08,2019-03-08,2019-05-01,2019-05-02,2019-05-03,2019-05-09,2019-05-10,2019-06-12,2019-11-04,2018-01-01,2018-01-02,2018-01-03,2018-01-04,2018-01-05,2018-01-08,2018-02-23,2018-03-08,2018-03-09,2018-04-30,2018-05-01,2018-05-02,2018-05-09,2018-06-11,2018-06-12,2018-11-05,2018-12-31}') or period_day = any('{2018-04-28,2018-06-09,2018-12-29}') ) from periods
1. bzq Автор
  23.05.2019 12:04
  #20189734
  Круто, оба решения работают. Использование tsrange, all, any позволило сделать очень компактный запрос.
  
  eranthis
  23.05.2019 16:43
  #20191516
  тут в идеале бы ещё знать, как представлен календарь праздников и рабочих уикендов, так как параметрически их вряд ли передают в запрос. задача была бы более приближена к реальной, а так решение выглядит несколько искусственно.
  
  bzq Автор
  24.05.2019 10:41
  #20194408
  В реальной жизни может быть и так, и эдак. В зависимости от того, разовый ли это запрос или регулярный. Я специально не стал конкретизировать представление календаря, чтобы посмотреть на полёт мысли. Вот, например, Ваше представление мне очень понравилось. Компактнее ни у кого не получилось.
1. OrmEugensson
  24.05.2019 08:44
  #20193862
  +1
  Красиво применили функцию-тип «timestamp», я и не знал, что так можно в Postgres

vav180480

23.05.2019 07:23

#20188232

Решение с итерациями по дням, посекундной точностью, с учетом перенесенных на выходные рабочих дней и показываются заявки вообще не попавшие в рабочее время

Код

with periods(id, start_time, stop_time) as (
  values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp), 
        (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp), 
        (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
        (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp),
        (5, '2018-12-28 20:00:00'::timestamp, '2018-12-29 04:00:00'::timestamp)   
)
,holidays (holiday) as (
         values ('2018-01-01'::timestamp),
                ('2018-01-02'::timestamp),
                ('2018-01-03'::timestamp),
                ('2018-01-04'::timestamp),
                ('2018-01-05'::timestamp),
                ('2018-01-07'::timestamp),
                ('2018-01-08'::timestamp),
                ('2018-02-23'::timestamp),
                ('2018-03-08'::timestamp),
                ('2018-05-01'::timestamp),
                ('2018-05-09'::timestamp),
                ('2018-06-12'::timestamp),
                ('2018-11-04'::timestamp),
                ('2019-01-01'::timestamp),
                ('2019-01-02'::timestamp),
                ('2019-01-03'::timestamp),
                ('2019-01-04'::timestamp),
                ('2019-01-05'::timestamp),
                ('2019-01-07'::timestamp),
                ('2019-01-08'::timestamp),
                ('2019-02-23'::timestamp),
                ('2019-03-08'::timestamp),
                ('2019-05-01'::timestamp),
                ('2019-05-09'::timestamp),
                ('2019-06-12'::timestamp),
                ('2019-11-04'::timestamp)    
    )
,work_holidays (work_holiday) as (
         values ('2018-04-28'::timestamp)
    )    

select id, start_time, stop_time
      ,sum(case when (to_char(i, 'D') not in ('1', '7') or w.work_holiday is not null)
                 and h.holiday is null
                then greatest(least(i + interval '19 hour', stop_time)
                    -greatest(i + interval '10 hour', start_time),interval '0 hour')
                else interval '0 hour'
                end) work_hrs
  from periods p
      ,generate_series(start_time::date, stop_time, interval '1 day') as i
  left join holidays h on h.holiday = i
  left join work_holidays w on w.work_holiday = i
 group by id, start_time, stop_time
 order by id

bzq Автор
23.05.2019 12:28
#20189876
Бинго! Логика верная, но из чьего-то запроса выше Вы скопировали неправильные данные для праздников и не указали все дополнительные рабочие дни. После того, как я поправил, результаты на моих тестовых выборках стали сходиться с моими результатами.
1. vav180480
  24.05.2019 10:23
  #20194322
  Мне понравилась идея высказанная где то выше, не итерировать по дням, итерировать по исключениям (праздникам и переносам, которых заведомо меньше) в каждой заявке, потому как я заради теста, в дате окончания указал вместо 2019 — 22019 год (ну ну а чо) — песочница сдохла, и я догадываюсь почему:) На выходных мобыть подумкаю.

Megacinder

23.05.2019 18:16

#20192010

pg 9.5

with wt_periods(id, start_time, stop_time) as
(
values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
(2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
(3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
(4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
)

,wt_par as
(
select
id
,start_time :: timestamp as i_from_dt
,stop_time :: timestamp as i_to_dt
,10 :: numeric as i_start_slave_time
,19 :: numeric as i_stop_slave_time
from
wt_periods
)

,wt_gen_dt as
(
select
pa1.id
,pa1.i_from_dt
,pa1.i_to_dt
,pa1.i_start_slave_time
,pa1.i_stop_slave_time
,dt
,case
when extract(dow from dt) in (6, 0)
or sh1.day_off is not null
then
0
else
1
end as is_slave_day
from wt_par pa1
cross join generate_series(
date_trunc('day', i_from_dt)
, date_trunc('day', i_to_dt)
, '24 hours' :: interval
) as dt
left join otrsuser.stat_holidays sh1 --табличка с праздничными днями
on sh1.day_off = dt
)

select
id
,i_from_dt
,i_to_dt
,sum( case --дата конца раб дня
when is_slave_day = 0
then
dt + interval '0 hours'
else
greatest(
least(
dt + interval '1 hours' * i_stop_slave_time
, i_to_dt
)
, dt + interval '1 hours' * i_start_slave_time
, i_from_dt
)
end
— case --дата начала раб дня
when is_slave_day = 0
then
dt + interval '0 hours'
else
greatest(
dt + interval '1 hours' * i_start_slave_time
, i_from_dt
)
end ) as sum_slave_mi
from
wt_gen_dt
group by
id
,i_from_dt
,i_to_dt

bzq Автор

23.05.2019 18:56

#20192182

Табличка otrsuser.stat_holidays не найдена.

Megacinder

24.05.2019 11:29

#20194656

Ещё и формат поехал. Переписал. Но, думаю, это решение довольно популярно

Заголовок спойлера

with wt_periods(id, start_time, stop_time) as 
    (
        values  (1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp)
              , (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp)
              , (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp)
              , (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
    )

    ,wt_holyday(id, day_off, comment) as
    (
        values  (79,  '2018-01-01 00:00:00' :: timestamp, 'НГ')
              , (80,  '2018-01-02 00:00:00' :: timestamp, 'НГ')
              , (81,  '2018-01-03 00:00:00' :: timestamp, 'НГ')
              , (82,  '2018-01-04 00:00:00' :: timestamp, 'НГ')
              , (83,  '2018-01-05 00:00:00' :: timestamp, 'НГ')
              , (84,  '2018-01-08 00:00:00' :: timestamp, 'НГ')
              , (85,  '2018-02-23 00:00:00' :: timestamp, 'ДЗО')
              , (86,  '2018-03-08 00:00:00' :: timestamp, 'МЖД')
              , (87,  '2018-03-09 00:00:00' :: timestamp, 'МЖД')
              , (88,  '2018-04-30 00:00:00' :: timestamp, 'Первомай')
              , (89,  '2018-05-01 00:00:00' :: timestamp, 'Первомай')
              , (90,  '2018-05-02 00:00:00' :: timestamp, 'Первомай')
              , (91,  '2018-05-09 00:00:00' :: timestamp, 'День Победы')
              , (92,  '2018-06-11 00:00:00' :: timestamp, 'День России')
              , (93,  '2018-06-12 00:00:00' :: timestamp, 'День России')
              , (94,  '2018-11-05 00:00:00' :: timestamp, 'День народного единства')
              , (95,  '2018-12-31 00:00:00' :: timestamp, 'НГ')
              , (96,  '2019-01-01 00:00:00' :: timestamp, 'НГ')
              , (97,  '2019-01-02 00:00:00' :: timestamp, 'НГ')
              , (98,  '2019-01-03 00:00:00' :: timestamp, 'НГ')
              , (99,  '2019-01-04 00:00:00' :: timestamp, 'НГ')
              , (100, '2019-01-07 00:00:00' :: timestamp, 'НГ')
              , (101, '2019-01-08 00:00:00' :: timestamp, 'НГ')
              , (102, '2019-03-08 00:00:00' :: timestamp, 'МЖД')
              , (103, '2019-05-01 00:00:00' :: timestamp, 'Первомай')
              , (104, '2019-05-02 00:00:00' :: timestamp, 'Первомай')
              , (105, '2019-05-03 00:00:00' :: timestamp, 'Первомай')
              , (106, '2019-05-09 00:00:00' :: timestamp, 'День Победы')
              , (107, '2019-05-10 00:00:00' :: timestamp, 'День Победы')
              , (108, '2019-06-12 00:00:00' :: timestamp, 'День России')
              , (109, '2019-11-04 00:00:00' :: timestamp, 'День народного единства')
    )

    ,wt_par as
    (
        select
             id
            ,start_time :: timestamp as i_from_dt
            ,stop_time :: timestamp as i_to_dt
            ,10 :: numeric as i_start_slave_time
            ,19 :: numeric as i_stop_slave_time
        from
            wt_periods
    )

    ,wt_gen_dt as
    (
        select
            pa1.id
            ,pa1.i_from_dt
            ,pa1.i_to_dt
            ,pa1.i_start_slave_time
            ,pa1.i_stop_slave_time
            ,dt 
            ,case when extract(dow from dt) in (6, 0) or sh1.day_off is not null then 0 else 1 end  as is_slave_day
        from wt_par pa1
            cross join generate_series(
                                        date_trunc('day', i_from_dt)
                                      , date_trunc('day', i_to_dt)
                                      , '24 hours' :: interval
                                      ) as dt
            left join wt_holyday  sh1 --табличка с праздничными днями
                on sh1.day_off = dt
    )

select
     id
    ,i_from_dt
    ,i_to_dt
    ,sum( case --дата конца раб дня 
              when is_slave_day = 0
                  then
                      dt + interval '0 hours'
                  else
                      greatest(
                                least(
                                       dt + interval '1 hours' * i_stop_slave_time
                                     , i_to_dt
                                     )
                              , dt + interval '1 hours' * i_start_slave_time
                              , i_from_dt
                              )
          end
          -
          case --дата начала раб дня 
              when is_slave_day = 0
                  then
                      dt + interval '0 hours'
                  else
                      greatest(
                                dt + interval '1 hours' * i_start_slave_time
                              , i_from_dt
                              )
          end )  as sum_slave_mi
from
    wt_gen_dt
group by
     id
    ,i_from_dt
    ,i_to_dt

bzq Автор
24.05.2019 12:44
#20195102
Не учитываете никак дополнительные рабочие дни. Например, 2018-04-28 был рабочим днём. Поэтому на моих выборках результаты не сходятся.

Подход может и не оригинален, но всё равно спасибо за участие. Не так много народу вообще показало работающие запросы.

TelepnevDmitriy

23.05.2019 18:17

#20192012

решение с timestamp range

Решение

with periods(id, start_time, stop_time) as (
    values (1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
           (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
           (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
           (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
),
     holidays(period) as (
         values (tsrange('2018-01-01 00:00:00', '2018-01-08 23:59:59')),
                (tsrange('2018-02-23 00:00:00', '2018-02-23 23:59:59')),
                (tsrange('2018-03-08 00:00:00', '2018-03-09 23:59:59')),
                (tsrange('2018-04-30 00:00:00', '2018-05-02 23:59:59')),
                (tsrange('2018-05-09 00:00:00', '2018-05-09 23:59:59')),
                (tsrange('2018-06-11 00:00:00', '2018-06-12 23:59:59')),
                (tsrange('2018-11-05 00:00:00', '2018-11-05 23:59:59')),
                (tsrange('2018-12-31 00:00:00', '2019-01-08 23:59:59')),
                (tsrange('2019-03-08 00:00:00', '2019-03-08 23:59:59')),
                (tsrange('2019-05-01 00:00:00', '2019-05-03 23:59:59')),
                (tsrange('2019-05-09 00:00:00', '2019-05-10 23:59:59')),
                (tsrange('2019-06-12 00:00:00', '2019-06-12 23:59:59')),
                (tsrange('2019-11-04 00:00:00', '2019-11-04 23:59:59')),
                (tsrange('2019-11-04 00:00:00', '2019-11-04 23:59:59'))
     ),
     transfered_work_days(d) as (
         values ('2018-04-28'),
                ('2018-06-09'),
                ('2018-12-29')
     )
select id, sum(work_time)
from (
         select id,
                least(stop_time, d::date + '19h'::interval)::timestamp -
                greatest(start_time, d::date + '10h'::interval)::timestamp as work_time
         from (
                  select id, generate_series(start_time::date, stop_time::date, '1d'::interval) d, start_time, stop_time from periods
              ) as days
         where (exists(select true from transfered_work_days twd where twd.d::date = days.d::date limit 1) or
                extract(isodow from days.d) not in (6, 7))
           and not exists(select true from holidays where period @> (days.d::timestamp) limit 1)
     ) _
group by id
order by id

nikotin77

23.05.2019 18:17

#20192014

Так вижу

with 
periods(id, start_time, stop_time) as ( values
  (1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
  (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
  (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
  (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)	    
),
holidays(date_key) as ( values 
  ('2018-01-01'::date), ('2018-01-02'::date), ('2018-01-03'::date), 
  ('2018-01-04'::date), ('2018-01-05'::date), ('2018-01-08'::date), 
  ('2018-02-23'::date), ('2018-03-08'::date), ('2018-03-09'::date), 
  ('2018-04-30'::date), ('2018-05-01'::date), ('2018-05-02'::date), 
  ('2018-05-09'::date), ('2018-06-11'::date), ('2018-06-12'::date), 
  ('2018-11-05'::date), ('2018-12-31'::date), 
  ('2019-01-01'::date), ('2019-01-02'::date), ('2019-01-03'::date), 
  ('2019-01-04'::date), ('2019-01-07'::date), ('2019-01-08'::date), 
  ('2019-03-08'::date), ('2019-05-01'::date), ('2019-05-02'::date), 
  ('2019-05-03'::date), ('2019-05-09'::date), ('2019-05-10'::date), 
  ('2019-06-12'::date), ('2019-11-04'::date)
),
work_holidays(date_key) as ( values 
  ('2018-04-28'::date), ('2018-06-09'::date), ('2018-12-29'::date)
)
select p.id, p.start_time, p.stop_time,        
       -- hours from start_time
       case 
         when start_time::date = min(h.time_key)  then '00:00:00'::interval
         when start_time::date = stop_time::date  then '00:00:00'::interval
	 when start_time::time > '19:00:00'::time then '00:00:00'::interval
	 when start_time::time < '10:00:00'::time then '09:00:00'::interval
	 else '19:00:00'::time - start_time::time
       end +
       -- hours from stop_time
       case 	        
         when stop_time::date = max(h.time_key)  then '00:00:00'::interval
	 when stop_time::date = start_time::date then least('19:00:00'::time,  stop_time::time) - greatest('10:00:00'::time, start_time::time)
	 when stop_time::time > '19:00:00'::time then '09:00:00'::interval
         when stop_time::time < '10:00:00'::time then '00:00:00'::interval
	 else stop_time::time - '10:00:00'::time
       end +
        -- 
       (
         -- count all days
	 greatest(p.stop_time::date - p.start_time::date - 1, 0) 
         -- count holidays 
	 -sum(case when h.time_key > p.start_time::date and h.time_key < p.stop_time::date then 1 else 0 end)
       )*'09:00:00'::interval as answer
from periods p
left outer join
(
  select time_key::date
  from generate_series(
    (select min(start_time)::date from periods), 
    (select max(stop_time)::date from periods), 
    interval '1 day'
  ) as time_key
  where to_char(time_key, 'D') in ('1', '7')
  union 
  select date_key
  from holidays
  except
  select date_key
  from work_holidays
) h	 
on h.time_key between p.start_time::date and p.stop_time::date
group by p.id, p.start_time, p.stop_time
order by p.id

Vadim-n

23.05.2019 18:47

#20192154

У меня вот что получилось. Таблица industrial_calendar имеет вид (id, date), где date — рабочие дни. Данные забираются с портала открытых данных РФ (data.gov.ru).

Код

with periods(id, start_time, stop_time) as (
  values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
        (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
        (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
        (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
)
select p.id,
       p.start_time as start_p,
       p.stop_time as end_p,
       sum((least((c.date || ' 19:00:00')::timestamp, p.stop_time) -
            greatest((c.date || ' 10:00:00')::timestamp, p.start_time))::time) as work_time
from periods p
       join industrial_calendar c on p.start_time::date <= c.date and p.stop_time::date >= c.date
group by p.id, p.start_time, p.stop_time

uaggster

23.05.2019 19:47

#20192410

Я понимаю, что здесь тусовка постгрес.
Я этим диалектом sql — не владею, и тем интереснее смотреть варианты. Познавательнее.
Победителей объявят?
Интересно будет глянуть код.

Для MSSQLSERVER, упрощенно, будет выглядеть так:

set language russian
;With [days]
as
(
	Select Cast('20180101' as date) [day]
	Union all
	Select DATEADD(day, 1, [days].[day])
	From [days]
	Where [days].[day] < '20191231'
)
,     [periods]
as
(
	Select *
	from (
	VALUES ( 1, Cast('2019-03-29 07:00:00' as datetime2), Cast('2019-04-08 14:00:00'as datetime2) )
	,      ( 2, '2019-04-10 07:00:00',                    '2019-04-10 20:00:00'                   )
	,      ( 3, '2019-04-11 12:00:00',                    '2019-04-12 16:07:12'                   )
	,      ( 4, '2018-12-28 12:00:00',                    '2019-01-16 16:00:00'                   )) t (id, start_time, stop_time)
)
,     holidays ( h_date )
as
(
	Select *
	from (
	values ( Cast('2018-01-01' as date) )
	,      ( '2018-01-02'               )
	,      ( '2018-01-03'               )
	,      ( '2018-01-04'               )
	,      ( '2018-01-05'               )
	,      ( '2018-01-07'               )
	,      ( '2018-01-08'               )
	,      ( '2018-02-23'               )
	,      ( '2018-03-08'               )
	,      ( '2018-05-01'               )
	,      ( '2018-05-09'               )
	,      ( '2018-06-12'               )
	,      ( '2018-11-04'               )
	,      ( '2019-01-01'               )
	,      ( '2019-01-02'               )
	,      ( '2019-01-03'               )
	,      ( '2019-01-04'               )
	,      ( '2019-01-05'               )
	,      ( '2019-01-07'               )
	,      ( '2019-01-08'               )
	,      ( '2019-02-23'               )
	,      ( '2019-03-08'               )
	,      ( '2019-05-01'               )
	,      ( '2019-05-09'               )
	,      ( '2019-06-12'               )
	,      ( '2019-11-04'               )
	) t(h_date)
)
Select a.*
,t.cnt * 9.0
+ Case when Cast(a.start_time as time) between '10:00:00' and '19:00:00' then Cast(datediff(minute, '19:00:00', Cast(a.start_time as time)) as float) / 60.0 
Else 0.0 End
- Case when Cast(a.stop_time as time) between '10:00:00' and '19:00:00' then Cast(datediff(minute, '19:00:00', Cast(a.stop_time as time)) as float) / 60.0 
Else 0.0 End
[work_time]
from [periods] a
	Outer apply 
		(Select count(*) 
			from [days] d
			left join holidays h on h.h_date = d.[day]			 
			Where d.[day] between Cast(a.start_time as date) and Cast(a.stop_time as date)
				and h.h_date is null
				and DATEPART(weekday, d.[day]) not in (6,7)
		) t(cnt)
Option (maxrecursion 0)