Отправляет email-рассылки с помощью сервиса Sendsay

Открыто о СУБД Oracle на русском

Рассылка закрыта

При закрытии подписчики были переданы в рассылку "СУБД Oracle "с нуля"" на которую и рекомендуем вам подписаться.

Вы можете найти рассылки сходной тематики в Каталоге рассылок.

  Июнь 2002   →
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Автор
Сергей

Статистика

10.773 подписчиков
-8 за неделю
←   Все выпуски   →

Открыто о СУБД Oracle на русском определение количества рабочих дней в диапазоне дат


Информационный Канал Subscribe.Ru

Выпуск 1

Вычисление количества рабочих дней между двумя датами

Уважаемые подписчики! Этот выпуск посвящен одному из десятков вопросов (вопрос, кстати, давний), на которые ответил вчера знаменитый дядя Том (Том Кайт) на сайте asktom.oracle.com

Дядю Тома спросили:

Как получить в PL/SQL количество дней между двумя датами, исключая субботы и воскресенья?

и он ответил

Примерно так:

ops$tkyte@8i> @test 29-feb-2000 01-feb-2000
ops$tkyte@8i> set echo on
ops$tkyte@8i>
ops$tkyte@8i> select count(*)
  2    from ( select rownum rnum
  3             from all_objects
  4            where rownum <= to_date('&1') - to_date('&2')+1 )
  5   where to_char( to_date('&2')+rnum-1, 'DY' )
                      not in ( 'SAT', 'SUN' )
  6  /
old   4:   where rownum <= to_date('&1') - to_date('&2')+1 )
new   4:   where rownum <= to_date('29-feb-2000') -
                           to_date('01-feb-2000')+1 )
old   5:  where to_char( to_date('&2')+rnum-1, 'DY' )
                    not in ( 'SAT', 'SUN' )
new   5:  where to_char( to_date('01-feb-2000')+rnum-1, 'DY' )
                    not in ( 'SAT', 'SUN' )

  COUNT(*)
----------
        21

Проверьте, что используемая в неявно создаваемом представлении таблица (в моем примере - all_objects) имеет не меньше строк, чем охватываемое количество дней (т.е. в ней должно быть не менее 366 строк для обработки дат за 1 год, 3660 - за 10 лет, и т.д.). Представление all_objects, обычно содержащее тысячи строк, в большинстве случаев отлично подойдет. Но всегда можно построить собственную очень компактную таблицу, чтобы вычисление выполнялось быстрее.

Логическим продолжением будет учет "праздников" и других нерабочих дней, которые тоже надо исключить. Например:

ops$tkyte@8i> create table exclude_dates ( no_work date primary key ) organization index;

Table created.

ops$tkyte@8i> insert into exclude_dates values ( '14-FEB-2000' );

1 row created.

ops$tkyte@8i>
ops$tkyte@8i>
ops$tkyte@8i> select count(*)
  2    from ( select rownum rnum
  3             from all_objects
  4            where rownum <= to_date('&1') - to_date('&2')+1 )
  5   where to_char( to_date('&2')+rnum-1, 'DY' )
                not in ( 'SAT', 'SUN' )
  6     and not exists
        ( select null from exclude_dates where no_work =
  7               trunc(to_date('&2')+rnum-1) )
  8  / old   4:  where rownum <= to_date('&1') - to_date('&2')+1 )
new   4:  where rownum <= to_date('29-feb-2000') -
                          to_date('01-feb-2000')+1 )
old   5:  where to_char( to_date('&2')+rnum-1, 'DY' )
                 not in ( 'SAT', 'SUN' )
new   5:  where to_char( to_date('01-feb-2000')+rnum-1, 'DY' )
                 not in ( 'SAT', 'SUN' )
old   7:    trunc(to_date('&2')+rnum-1) )
new   7:    trunc(to_date('01-feb-2000')+rnum-1) )   

   COUNT(*)
----------
        20

Обсуждение

Комментарий Сэма, 7 октября 2001 года

Зачем отнимать 1 от rownum в этом фрагменте:

select count(*)
  2    from ( select rownum-1 rnum
  3             from all_objects
  4            where rownum <= to_date('&1') - to_date('&2')+1 )
  5   where to_char( to_date('&2')+rnum-1, 'DY' )
                      not in ( 'SAT', 'SUN' )

Еще вопрос: как в одном SQL-операторе решить следующую проблему.

Дядя Джим получал зарплату с января 2001 года, но с 15 марта 2001 перешел на половину ставки в 45 долларов в час. Как в одном операторе посчитать, сколько денег должны дяде Джиму в настощее время.

Ответ дяди Тома

Подсказка: используйте конструкцию UNION ALL, еще одно неявное представление и функцию SUM 

select sum(sal)
 from ( select ... запрос на период с января по март
        union all
        select .... запрос на период с марта по сейчас )

или используйте decode/case в операторе select для определения суммы (оставляю как упражнение читателям, поскольку это не относится к теме исходного вопроса ;)

Комментарий Асифа Момена из Рияда, 8 октября 2001 года

Привет, Том,

Предложенный тобой запрос - просто замечательный. Но его надо исправить. Вместо "rownum-1", надо выбирать просто "rownum". Причина в том, что для первой записи "rownum-1" вернет 0, и конструкция were to_char(to_date('&2')+rnum-1, 'DY') not in ('SAT', 'SUN') будет возвращать день на один раньше заданного диапазона.

В твоем примере с периодом с 01-feb-2000 по 29-feb-2000 конструкция where будет проверять 31-jan-2000. В этом случае это неважно, поскольку 31-jan-2000 - не суббота и не воскресенье, но для периода с 01-may-2000 по 31-may-2000, как и для любого периода, начинающегося с понедельника, результат будет неправильный.

Ответ дяди Тома

Да, вы правы, спасибо, что обратили внимание. Я исправил код.

Вопрос в догонку Сонали Келкара, 4 июня 2002 года

Будет ли это работать - один из наших клиентов говорит, что так быстрее

CREATE OR REPLACE FUNCTION num_business_days(start_date IN DATE, end_date IN DATE)
   RETURN NUMBER IS
busdays NUMBER := 0;
stDate DATE;
enDate DATE; 

BEGIN 

stDate := TRUNC(start_date);
enDate := TRUNC(end_date);

if enDate >= stDate
then
  -- Получаем абсолютное количество дней
  busdays := enDate - stDate
        --  Теперь вычитаем входные дни
        --  Этот оператор округляет диапазон до целых дней (с помощью
        --  TRUNC), а затем делит результат на 7 для получения
        --  количества недель, и умножает на 2, чтобы получить выходные.
     - ((TRUNC(enDate,'D')-TRUNC(stDate,'D'))/7)*2
        -- Добавляем 1 для получения диапазона включительно
     + 1;   

  /* Уточняем, если последний день - суббота */
  IF TO_CHAR(enDate,'D') = '7' THEN
    busdays := busdays - 1;
  END IF;

  /* Уточняем, если последний день - воскресенье */
  IF TO_CHAR(stDate,'D') = '1' THEN
    busdays := busdays - 1;
  END IF;
else
   busdays := 0;
END IF;

   RETURN(busdays);
END;
/

Ответ дяди Тома

Разберитесь, протестируйте, сравните.... Тогда узнаете, "будет ли это работать"... Похоже на другой пример на сайте asktom. Но, как и с любой программой, - прежде чем использовать:

  • Разберитесь, что она делает и как
  • Протестируйте
  • Cравните с другими решениями

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

В следующем выпуске

Следующий выпуск будет, скорее всего, посвящен обзору новых материалов, опубликованных в мае на сайте Ixora. Будут и комментарии к этому выпуску, конечно. Следите за новостями на сайте проекта Open Oracle.

С наилучшими пожеланиями,

  В.К.

http://subscribe.ru/
E-mail: ask@subscribe.ru 		Отписаться
Убрать рекламу
В избранное