Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Расчет времени выполнения заявки без учета выходных и праздников(учет только раб времени)  [new]
antonkashin
Member

Откуда:
Сообщений: 7
Всем, добрый день.

Подскажите, реально ли в oracle посчитать время между датой поступления заявки и датой ее закрытия, при этом не учитываем выходные и праздники и ночное время, берем только рабочее, если заявка пришла 15.03.2019 20:00, а завершили ее 16.03.2019 8:00, при условии, что сотрудники работают с 7:00 до 21:00. В данном примере время составило 2 часа.

Календарь с выходными и праздниками есть.

Пока что все это выгружается в access и там считается
15 мар 19, 16:57    [21834091]     Ответить | Цитировать Сообщить модератору
 Re: Расчет времени выполнения заявки без учета выходных и праздников(учет только раб времени)  [new]
MazoHist
Member

Откуда:
Сообщений: 92
Реально. Уже считали такую же задачу
15 мар 19, 17:08    [21834105]     Ответить | Цитировать Сообщить модератору
 Re: Расчет времени выполнения заявки без учета выходных и праздников(учет только раб времени)  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1786
antonkashin,
15 мар 19, 17:09    [21834106]     Ответить | Цитировать Сообщить модератору
 Re: Расчет времени выполнения заявки без учета выходных и праздников(учет только раб времени)  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1786
ОЙ (рука дрогнула)


если есть календар, то почему бы не посчитать

надо акуратненько расписать кобинации начало/конец/выходной

....
stax
15 мар 19, 17:11    [21834111]     Ответить | Цитировать Сообщить модератору
 Re: Расчет времени выполнения заявки без учета выходных и праздников(учет только раб времени)  [new]
antonkashin
Member

Откуда:
Сообщений: 7
Stax,

Первый и последний день у меня есть представление как посчитать, а на счет полных дней и куда календарь прикрутить че то не выходит
18 мар 19, 08:45    [21835579]     Ответить | Цитировать Сообщить модератору
 Re: Расчет времени выполнения заявки без учета выходных и праздников(учет только раб времени)  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1786
antonkashin
Stax,

Первый и последний день у меня есть представление как посчитать, а на счет полных дней и куда календарь прикрутить че то не выходит


первый и последний день считем отдельно (имхо их посчитать сложнее)
тогда полные дни (без первого и последнего)

with t as (
select 200 tn, to_date('14/03/19 09:30','dd.mm.rr hh24:mi') w_from,to_date('18/03/19 18:00','dd.mm.rr hh24:mi') w_to from dual union all
select 200 tn, to_date('14/03/19 06:30','dd.mm.rr hh24:mi') w_from,to_date('14/03/19 12:00','dd.mm.rr hh24:mi') w_to from dual union all
select 200 tn, to_date('16/03/19 06:30','dd.mm.rr hh24:mi') w_from,to_date('24/03/19 22:30','dd.mm.rr hh24:mi') w_to from dual 
)
,calendar as(
select date '2019-03-10' d,1 f from dual union all
select date '2019-03-11' d,1 f from dual union all
select date '2019-03-12' d,1 f from dual union all
select date '2019-03-13' d,1 f from dual union all
select date '2019-03-14' d,1 f from dual union all
select date '2019-03-15' d,1 f from dual union all
select date '2019-03-16' d,0 f from dual union all
select date '2019-03-17' d,0 f from dual union all
select date '2019-03-18' d,1 f from dual union all
select date '2019-03-19' d,1 f from dual union all
select date '2019-03-20' d,1 f from dual union all
select date '2019-03-21' d,1 f from dual union all
select date '2019-03-22' d,1 f from dual union all
select date '2019-03-23' d,0 f from dual union all
select date '2019-03-24' d,0 f from dual union all
select date '2019-03-25' d,1 f from dual )
select 
 t.*
,greatest(
  trunc(w_to)-trunc(w_from)-1 -- всего днив
  -(select count(*) cc from calendar c where f=0 and c.d between trunc(w_from)+1 and trunc(w_to)-1) --к-во выходных
,0)
--(select count(*) cc from calendar c where f=1 and c.d between trunc(w_from)+1 and trunc(w_to)-1) --влоб
 d_work
from t
SQL> /

        TN W_FROM   W_TO         D_WORK
---------- -------- -------- ----------
       200 14.03.19 18.03.19          1
       200 14.03.19 14.03.19          0
       200 16.03.19 24.03.19          5


.....
stax
18 мар 19, 10:38    [21835652]     Ответить | Цитировать Сообщить модератору
 Re: Расчет времени выполнения заявки без учета выходных и праздников(учет только раб времени)  [new]
antonkashin
Member

Откуда:
Сообщений: 7
Stax, ты гений.


Спасибо большое! Во всем разобрался
18 мар 19, 11:12    [21835694]     Ответить | Цитировать Сообщить модератору
 Re: Расчет времени выполнения заявки без учета выходных и праздников(учет только раб времени)  [new]
antonkashin
Member

Откуда:
Сообщений: 7
Stax, начал тестировать на своей таблице и сразу выдает ошибку SQL Error: ORA-01427: подзапрос одиночной строки возвращает более одной строки
drop table test_work;
create table test_work as
with --t as (select t1.*
--from SAF_SMS t1
-- select to_date('04.03.2019 21:30:00','dd.mm.yyyy hh24:mi:ss') start_d
--       ,to_date('08.03.2019 06:30:00','dd.mm.yyyy hh24:mi:ss') finish_d
-- from dual
-- )
/* ,*/ tt as(
 select 
 case 
  when start_dt<trunc(start_dt)+7/24 and 
  (select count(*)cc from PROIZVOD_CALENDAR pc where pc.HOLIDAY_INDICATOR='Y'
    and pc.full_date= trunc(start_dt))=0 then 14/24
  when start_dt>trunc(start_dt)+21/24 or 
  (select count(*)cc from PROIZVOD_CALENDAR pc where pc.HOLIDAY_INDICATOR='Y'
    and pc.full_date= trunc(start_dt))>0 then 0
  else trunc(start_dt)+21/24-start_dt
  end mi_start
  ,
 case
  when end_dt <trunc(end_dt)+7/24 or 
  (select count(*)cc from PROIZVOD_CALENDAR pc where pc.HOLIDAY_INDICATOR='Y'
    and pc.full_date= trunc(end_dt))>0 then 0
  when end_dt>trunc(end_dt)+21/24 
  and (select count(*)cc from PROIZVOD_CALENDAR pc where pc.HOLIDAY_INDICATOR='Y'
    and pc.full_date= trunc(end_dt))=0 then 14/24
  else (end_dt-(trunc(end_dt)+17/24))
  end mi_finish
  ,
 (select 
    greatest(
      trunc(end_dt)-trunc(start_dt)-1
      -(select count(*)cc from PROIZVOD_CALENDAR pc where pc.HOLIDAY_INDICATOR='Y'
        and pc.full_date between trunc(start_dt)+1 and trunc(end_dt)-1 ),0)*(14/24) mi
  from SAF_SMS
  )mi_all
  , t1.*
  from SAF_SMS t1)
   
 select tt.*,
    decode (trunc(start_dt), trunc(end_dt)
             , (least(end_dt,trunc(end_dt)+21/24)-greatest(start_dt, trunc(start_dt)+7/24))
             ,mi_all+mi_start+mi_finish) work_time
 from tt;
18 мар 19, 14:18    [21835968]     Ответить | Цитировать Сообщить модератору
 Re: Расчет времени выполнения заявки без учета выходных и праздников(учет только раб времени)  [new]
-2-
Member

Откуда:
Сообщений: 14571
antonkashin
тестировать на своей таблице
На своем запросе.
Не нужно всуе наводить тень на гений Stax.
18 мар 19, 14:59    [21836058]     Ответить | Цитировать Сообщить модератору
 Re: Расчет времени выполнения заявки без учета выходных и праздников(учет только раб времени)  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1786
antonkashin,

-- (select 
    greatest(
      trunc(end_dt)-trunc(start_dt)-1
      -(select count(*)cc from PROIZVOD_CALENDAR pc where pc.HOLIDAY_INDICATOR='Y'
        and pc.full_date between trunc(start_dt)+1 and trunc(end_dt)-1 ),0)*(14/24) 
--  from SAF_SMS
--  )
mi_all


ps
если в календаре есть рабочие дни то проще, хотя хз
       (select count(*)cc from PROIZVOD_CALENDAR pc where pc.HOLIDAY_INDICATOR<>'Y'
        and pc.full_date between trunc(start_dt)+1 and trunc(end_dt)-1 )*(14/24) 


.....
stax
18 мар 19, 15:09    [21836077]     Ответить | Цитировать Сообщить модератору
 Re: Расчет времени выполнения заявки без учета выходных и праздников(учет только раб времени)  [new]
antonkashin
Member

Откуда:
Сообщений: 7
Stax, спасибо большое!!
18 мар 19, 16:34    [21836216]     Ответить | Цитировать Сообщить модератору
 Re: Расчет времени выполнения заявки без учета выходных и праздников(учет только раб времени)  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1786
antonkashin,

главное сдесь акуратненько все выверить

автор
Первый и последний день у меня есть представление как посчитать


проверте напр для
 select to_date('15.03.2019 08:00:00','dd.mm.yyyy hh24:mi:ss') start_dt
       ,to_date('19.03.2019 08:00:00','dd.mm.yyyy hh24:mi:ss') end_dt

пусть 16,17 выходной

я так понимаю, ето 28 часов

....
stax
18 мар 19, 17:34    [21836316]     Ответить | Цитировать Сообщить модератору
 Re: Расчет времени выполнения заявки без учета выходных и праздников(учет только раб времени)  [new]
antonkashin
Member

Откуда:
Сообщений: 7
Stax, да все правильно, получается 28 часов. Проверил всевозможные комбинации, вроде, считает корректно. Спасибо еще раз.
19 мар 19, 08:18    [21836803]     Ответить | Цитировать Сообщить модератору
 Re: Расчет времени выполнения заявки без учета выходных и праздников(учет только раб времени)  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1786
antonkashin
Stax, да все правильно, получается 28 часов. Проверил всевозможные комбинации, вроде, считает корректно. Спасибо еще раз.

with SAF_SMS as (
 select to_date('15.03.2019 08:00:00','dd.mm.yyyy hh24:mi:ss') start_dt
       ,to_date('19.03.2019 08:00:00','dd.mm.yyyy hh24:mi:ss') end_dt
 from dual)
,PROIZVOD_CALENDAR as (
select date '2019-03-15' full_date,'N' HOLIDAY_INDICATOR  from dual union all
select date '2019-03-16' d,'Y' f from dual union all
select date '2019-03-17' d,'Y' f from dual union all
select date '2019-03-18' d,'N' f from dual union all
select date '2019-03-19' d,'N' f from dual union all
select date '2019-03-20' d,'N' f from dual
)
,tt as(
 select
 case
  when start_dt<trunc(start_dt)+7/24 and
  (select count(*)cc from PROIZVOD_CALENDAR pc where pc.HOLIDAY_INDICATOR='Y'
    and pc.full_date= trunc(start_dt))=0 then 14/24
  when start_dt>trunc(start_dt)+21/24 or
  (select count(*)cc from PROIZVOD_CALENDAR pc where pc.HOLIDAY_INDICATOR='Y'
    and pc.full_date= trunc(start_dt))>0 then 0
  else trunc(start_dt)+21/24-start_dt
  end mi_start
  ,
 case
  when end_dt <trunc(end_dt)+7/24 or
  (select count(*)cc from PROIZVOD_CALENDAR pc where pc.HOLIDAY_INDICATOR='Y'
    and pc.full_date= trunc(end_dt))>0 then 0
  when end_dt>trunc(end_dt)+21/24
  and (select count(*)cc from PROIZVOD_CALENDAR pc where pc.HOLIDAY_INDICATOR='Y'
    and pc.full_date= trunc(end_dt))=0 then 14/24
  else (end_dt-(trunc(end_dt)+17/24))
  end mi_finish
  ,
    greatest(
      trunc(end_dt)-trunc(start_dt)-1
      -(select count(*)cc from PROIZVOD_CALENDAR pc where pc.HOLIDAY_INDICATOR='Y'
        and pc.full_date between trunc(start_dt)+1 and trunc(end_dt)-1 ),0)*(14/24)
  mi_all
  , t1.*
  from SAF_SMS t1)
 select tt.*,
    decode (trunc(start_dt), trunc(end_dt)
             , (least(end_dt,trunc(end_dt)+21/24)-greatest(start_dt, trunc(start_dt)+7/24))
             ,mi_all+mi_start+mi_finish) work_time
,28/24
 from tt
/
 48  /

  MI_START  MI_FINISH     MI_ALL START_DT END_DT    WORK_TIME      28/24
---------- ---------- ---------- -------- -------- ---------- ----------
,541666667      -,375 ,583333333 15.03.19 19.03.19        ,75 1,16666667


.....
stax
19 мар 19, 09:13    [21836841]     Ответить | Цитировать Сообщить модератору
 Re: Расчет времени выполнения заявки без учета выходных и праздников(учет только раб времени)  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1786
antonkashin,

else (end_dt-(trunc(end_dt)+17/24))

....
stax
19 мар 19, 09:22    [21836852]     Ответить | Цитировать Сообщить модератору
 Re: Расчет времени выполнения заявки без учета выходных и праздников(учет только раб времени)  [new]
antonkashin
Member

Откуда:
Сообщений: 7
Stax, прошу прощенья. В моем запросе все уже поправлено(единица как-то затесалась). else (end_dt-(trunc(end_dt)+7/24))
19 мар 19, 10:38    [21836954]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить