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)


  1. 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, что составляет девять часов; предпраздничный день является полным.


  1. 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.


  1. 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

        Чтобы не вступать в длительную и бесплодную дискуссию, сам пользователь закрыл. (:


  1. 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
;


    1. bzq Автор
      21.05.2019 16:59
      #20181106

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


      1. 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;


        1. bzq Автор
          21.05.2019 17:40
          #20181284

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


          1. the_unbridled_goose
            21.05.2019 18:13
            #20181504

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


            1. bzq Автор
              21.05.2019 18:49
              #20181654

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


              1. 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;


                1. bzq Автор
                  22.05.2019 13:04
                  #20184734

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


                  1. 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;


                    1. bzq Автор
                      22.05.2019 16:26
                      #20185916

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


                      1. 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;


                        1. 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 — неправильные данные, я у себя для тестов исправлял.


                          1. 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;


  1. 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


    1. 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


      1. bzq Автор
        21.05.2019 16:57
        #20181102

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


        1. 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


          1. 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;


          1. bzq Автор
            22.05.2019 13:31
            #20184896

            Бинго! Этот вариант запроса уже дал на моих тестовых данных правильный результат.


  1. 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).

    Как то так.


  1. Tatikoma
    21.05.2019 15:53
    #20180814

    Про сокращенные рабочие дни как-то забыли. Недостаточно реалистично :-)


    1. bzq Автор
      22.05.2019 16:15
      #20185836

      Почему-то ни разу не видел, чтобы в SLA указывали, что предпраздничные рабочие дни на час короче. В SLA обычно пишут «бла-бла-бла за N часов в рабочее время» и рабочее время определено «с/по в рабочие дни кроме выходных и официальных праздников», всё.

      Буду рад, если хоть одно из решений позволит учесть укороченные предпраздничные дни. Когда я эту задачу решал в реальной жизни, я на это заложился, но не пригодилось.


  1. bzq Автор
    21.05.2019 16:01
    #20180854

    Пожалуйста прячьте код под спойлер!


  1. bzq Автор
    21.05.2019 16:09
    #20180878

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


  1. 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


  1. 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


  1. 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;


    1. bzq Автор
      21.05.2019 18:27
      #20181570

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


  1. Envek
    21.05.2019 18:04
    #20181450
    +1

    Каюсь, я слегка подсмотрел в первые два решения (в частности, стырил оттуда график выходных, ибо самому его делать было лень), но они мне не понравились, потому что при подсчёте времени сам собой напрашивается встроенный в 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;


    1. bzq Автор
      21.05.2019 18:41
      #20181624

      Согласен, интервалы здесь очень в тему.


  1. mi888
    21.05.2019 20:46
    #20182158

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

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


    1. bzq Автор
      22.05.2019 13:35
      #20184926

      Да, алгоритм-то несложен — посчитать количество рабочих дней, правильно учесть первый и последний дни, всё сложить. Осталось это выразить на SQL.
      PS умножить надо на 9


  1. 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 дневке


        1. vav180480_2
          22.05.2019 07:24
          #20183282

          Зачем так много говорить? Просто покажи код… хотя бы нагугленный:)


          1. puyol_dev2
            22.05.2019 12:50
            #20184642

            Смотри под спойлером SQL


            1. vav180480
              22.05.2019 13:46
              #20184990

              Посмотрел, занятно, а с минутами что? Там под id=3 результат не верный.


              1. 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


        1. bzq Автор
          22.05.2019 14:28
          #20185194

          Вроде работает, но без минут нехорошо.


  1. vav180480_2
    22.05.2019 08:32
    #20183426

    удалено


  1. 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


      1. puyol_dev2
        22.05.2019 12:49
        #20184636

        Твой код не работает

        SQL Error [42P01]: ОШИБКА: отношение «periods» не существует
        Позиция: 162


        1. vav180480
          22.05.2019 13:21
          #20184828

          1) мой код работает
          2) правильно писать «твой код не работает в ...» далее нужно конкретно написать где
          3) я делал в этой песочнице: rextester.com
          Language: PostgreSQL
          усё робит
          4) где ты поджигал?
          там табличные выражения поддерживаются?


          1. puyol_dev2
            22.05.2019 13:33
            #20184908

            Ты уже исправил. Молодец. Я тебе код ошибки выложил предыдущего твоего нерабочего варианта


            1. vav180480
              22.05.2019 13:38
              #20184946

              1) Предыдущий работает так же как и первый, исправлены только и исключительно исходные данные.
              2) Я не могу редактировать сообщения через полчаса после их опубликования, я их опубликовал несколько часов назад.
              3) Я не могу редактировать сообщения после того как на них ответили, а ты ответил.
              4) Врать и говнокодить — не хорошо.


              1. puyol_dev2
                22.05.2019 13:44
                #20184980

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


                1. vav180480
                  22.05.2019 13:52
                  #20185016

                  1) Не знаешь, не делай предположений и не озвучивай этих предположений, в зависимости от ситуации это может выглядеть например обидно или например смешно
                  2) Попробуй отредактировать СВОЕ сообщение которое ты написал более получаса назад


      1. bzq Автор
        22.05.2019 14:41
        #20185286

        Решение не учитывает праздничные дни.

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


        1. vav180480
          22.05.2019 14:47
          #20185324

          какие конкретно праздничные дни не учтены? у меня ответ не верен? мой ответ не совпал с вашим?


          1. bzq Автор
            22.05.2019 15:23
            #20185526

            Да, в условии интервалы могут быть из 2018-2019 гг., а Вы учли только новогодние праздники 2019 года.


            1. vav180480
              22.05.2019 15:31
              #20185566

              Зачем загромождать решение? Лично мне лениво заниматься мартышкиным трудом и расписывать все праздники за два года, когда нужно только 8 дней одного года. Оно будет нагляднее если вместо одной строки в holidays будет двадцать две? Как это повлияет на результат? Как это повлияет на алгоритм? Или вам мой алгоритм кажется уж слишком лаконичным?:) Ведь в реале будут не табличные выражения periods и holidays, а таблички базы данных periods и hilidays. Я добавил только те праздники которые попадают в заданные интервалы.


              1. bzq Автор
                22.05.2019 16:03
                #20185754

                Я отчасти с Вами согласен, но тем не менее в условии я просил учесть праздники за два года и мне сперва пришлось «при необходимости решение можно будет легко дополнить», чтобы погонять на тестовых данных. Я бы не стал проявлять излишний формализм, если бы это был единственный нюанс Вашего решения. Но у Вас не учитываются дополнительные рабочие дни, например, 2018-04-28 был рабочим днём. Также потерялись в выводе периоды, которые совсем не попали на рабочее время.
                А вот компактность Вашего решения мне очень нравится.


                1. vav180480
                  22.05.2019 16:19
                  #20185876

                  ниже привел решение с учетом рабочих дней на выходных


  1. 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)


    1. bzq Автор
      22.05.2019 15:41
      #20185624

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


      1. OrmEugensson
        22.05.2019 16:29
        #20185922

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


      1. 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)


        1. bzq Автор
          22.05.2019 18:50
          #20186682

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

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


          1. OrmEugensson
            23.05.2019 16:53
            #20191570

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


            1. 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)
              ), ...


              1. 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)


  1. 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

      Круто. Но потерялись периоды, совсем не попадающие на рабочее время.


      1. vav180480
        22.05.2019 17:01
        #20186084

        Я не понял это как? В нерабочее время завели заявку (вечером после работы по пьяни) и в нерабочее время закрыли (утром следующего дня до работы когда протрезвели) вообще не работая?:) Напишите интервальчик для примера и жизненную ситуацию по этому интервальчику.


        1. valery1707
          22.05.2019 17:29
          #20186254

          Заявку заводит человек внешний к тому кто работает в рамках рабочих часов SLA.
          Клиент тех поддержки написал что у него ничего не работает, а потом понял что у него локальная проблема и закрыл её.


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


          1. vav180480
            22.05.2019 17:36
            #20186294

            Домой приду — попробую, там придется все условия из раздела where в раздел select тащить, а сие некрасиво просто.


            1. valery1707
              22.05.2019 18:49
              #20186670

              У меня в select условий нет.
              А самый сложный where — для рассчёта рабочих дней. В реальном проекте я бы материализовал таблицу с рабочими днями, так что этот where ушёл бы в другое место.


        1. bzq Автор
          23.05.2019 22:29
          #20192888

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

          Во-вторых, у нас может быть гибкий график. Или в рабочее время человек убежал по своим делам, а вечером задержался (или утром пораньше начал), чтобы успеть доделать всё вовремя.

          В-третьих, у нас могут быть офисы в разных часовых поясах. Или в разных городах. В городах поменьше любят график работы с 09 до 18, а в Москве-Питере с 10 до 19.

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

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

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

          В-седьмых, иногда в IT бывают авралы, когда просто нужно сделать. Лучше, когда это редко (а ещё лучше если никогда), но иногда бывает.

          В-восьмых, пришёл робот и автоматически поменял статусы, просроченное по таймаутам закрыл.

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


  1. 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
;


    1. valery1707
      22.05.2019 16:43
      #20185994

      Залил на rextester

      https://rextester.com/RUHPF66549


    1. bzq Автор
      23.05.2019 12:33
      #20189900

      На короткой выборке работает правильно, но на боевых даёт ошибку.

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


  1. 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)


    1. 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 позволило сделать очень компактный запрос.


        1. eranthis
          23.05.2019 16:43
          #20191516

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


          1. bzq Автор
            24.05.2019 10:41
            #20194408

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


      1. OrmEugensson
        24.05.2019 08:44
        #20193862
        +1

        Красиво применили функцию-тип «timestamp», я и не знал, что так можно в Postgres


  1. 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


    1. bzq Автор
      23.05.2019 12:28
      #20189876

      Бинго! Логика верная, но из чьего-то запроса выше Вы скопировали неправильные данные для праздников и не указали все дополнительные рабочие дни. После того, как я поправил, результаты на моих тестовых выборках стали сходиться с моими результатами.


      1. vav180480
        24.05.2019 10:23
        #20194322

        Мне понравилась идея высказанная где то выше, не итерировать по дням, итерировать по исключениям (праздникам и переносам, которых заведомо меньше) в каждой заявке, потому как я заради теста, в дате окончания указал вместо 2019 — 22019 год (ну ну а чо) — песочница сдохла, и я догадываюсь почему:) На выходных мобыть подумкаю.


  1. 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


    1. bzq Автор
      23.05.2019 18:56
      #20192182

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


      1. 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


        1. bzq Автор
          24.05.2019 12:44
          #20195102

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

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


  1. 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


  1. 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


  1. 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


  1. 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)