Сайт Макса Пантюхина

Главная
Обо мне
Ссылки
Фотографии
SQL и не только
Резюме
Предыдущая
Следующая
Р - значит ракета

      

      

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

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

create table EMPL_DATA
    (
      FIO varchar2(100),
      BDAY date
    );

comment on column EMPL_DATA.FIO
is 'Сотрудник';
comment on column EMPL_DATA.BDAY
is 'Дата рождения';

А задача такая: отобрать сотрудников у которых день рождения случится в ближайшие 45 дней.

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

Решение 1. (не мое. Некто Галина Нестерова)

select *
  from empl_data EMP
 where   mod(months_between(sysdate + 45, EMP.bday), 12)
       < mod(months_between(sysdate, EMP.bday), 12)

Этот запрос определяет не изменилось ли целое количество лет в начале и в конце заданного периода. У этого запроса есть одна особенность: если человек родился 29 февраля, то запрос будет показывать, что у него есть день рождения и в високосный год и не в високосный. Хотя, недостаток это спорный. Все зависит от конкретики задачи. Нужно или не нужно таких людей показывать.

Решение 2 (мое)

select FIO.*
  from empl_data FIO
 where to_date( to_char(FIO.bday, 'DDMM')
             || case
                   when   to_number(to_char(FIO.bday, 'MMDD'))
                        < to_number(to_char(sysdate, 'MMDD')) then '0002'
                   else   '0001'
                end,
             'DDMMYYYY') <= to_date(to_char(sysdate, 'DDMM')
                                 || '0001', 'DDMMYYYY') + 45

Ну это — типичное решение "в лоб" .(Причем, с ошибкой) Первое, что приходит в голову — преобразовать даты к текущему и последующему году (чтобы при выполнении запроса в конце года не упустить сотрудников, у которых день рождения в начале года). Следующее, что приходит в голову — а зачем нам именно текущий и следующий год, когда можно взять два любых просто идущих друг за другом года. Я взял 0001 год и 0002. Ну и конечно же сразу нарвался.
Если в списке есть человек с днем рождения 29 февраля, то запрос упадет, так как года 0001 и 0002 - не високосные. Впрочем, если бы я подставил реальные года это бы тоже ничего не решило.

Решение 3 (опять мое)

Собственно, это модифицированное решение 2.

select FIO.FIO, to_char(FIO.Bday, 'DDMM')
  from (select BDE.FIO, BDE.Bday,
               to_date( decode(to_char(BDE.Bday, 'DDMM'),
               '2902', decode(round(trunc(sysdate) + 200)
              - trunc(sysdate), 366, '2902', '2802'),
                        to_char(BDE.Bday, 'DDMM'))
            || to_char(sysdate, 'YYYY'),
            'DDMMYYYY') CyrYear,
               to_date( decode(to_char(BDE.Bday, 'DDMM'),
               '2902', decode(round(trunc(trunc(trunc(sysdate)
                                               + 400)) + 200)
                       - trunc(trunc(trunc(sysdate) + 400)),
                         366, '2902',
                         '2802'),
                to_char(BDE.Bday, 'DDMM'))
            || to_char(trunc(trunc(sysdate) + 400), 'YYYY'),
            'DDMMYYYY') NextYear
          from EMPL_DATA BDE) FIO
 where CyrYear - sysdate between 0 and 45
    or NextYear - sysdate between 0 and 45

Практически это тоже решание, постороенное на преобразовании даты рождения к текущему году однако попутно дата 29.02 в невисокосные года преобразуется к 28.02. Кстати, пользуясь таким преобразованием, можно уйти от недостатка Решения 1. Если в невисокосные года людей с днем рождения 29.02 показывать вообще нельзя, то достаточно преобразовывать эту дату к любому числу, заведомо не входящему в диапазон (например к sysdate - 50) .

Хочу только пояснить, зачем я делаю " trunc(sysdate) " и " round(trunc(sysdate) + 200) ". Таким образом я получаю первый и последний день текущего года. Соответсвенно, " trunc(trunc(trunc(sysdate) + 400)) " и " round(trunc(trunc(trunc(sysdate) + 400)) + 200) " дают первый и последний день следующего года. Можно было и по другому, через те же преобразования to_char + to_date, например, но я почему-то так сделал.

Решение 4 (не мое. Некто Алексей Обросов)

select EMP.*
  from empl_data EMP,
       (select to_char((trunc(sysdate) + rownum - 1), 'ddmm') cd
          from all_objects
         where rownum < 45) d
 where to_char(EMP.bday, 'ddmm') = d.cd

На мой взгляд, это самое интересное решение. Основано на использовании большой таблицы (в данном случае это представление all_objects.) исключительно в целях получения последовательности нумерованных записей, начиная с 1 (за счет применения rownum). За счет этого строится календарь за требуемый период.

 

 

Hosted by uCoz