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

Откуда:
Сообщений: 3720
Вопрос не столько про SQL, сколько про работу с датами, но что-то не соображу, как это можно сделать.
Есть услуга, для которой зафиксирована дата подключения услуги START_DATE.
У услуги есть абонентская плата, период абонентской платы определяется следующими полями:
UNIT — единица периода (D - день, W - неделя, M - месяц, Y - год)
VALUE — длительность периода в единицах (целое число)
ADJUST — корректировка начала периода (0 - без корректировки, другое значение - скорректировать дату подключения на указанное значение)
Например (для START_DATE = 2011-03-12):
UNITVALUEADJUSTОписание (начало следующего периода)
Y10Ежегодно (12.03.2012)
M10Ежемесячно (12.04.2011)
M11Раз в месяц (01.04.2011)
M31Раз в квартал (01.04.2011)
W10Еженедельно (Сб 19.03.2011)
W11Раз в понедельник (Пн 14.03.2011)
D10Ежедневно (13.03.2012)
D100Раз в 10 дней (22.03.2012)

Сейчас конструирую монстра из CASE (по значению UNIT) и EXTRACT/TO_CHAR/TO_DATE (для учета ADJUST).
Но может что-то готовое есть?
________________________
Мы смотрим с оптимизмом...
...в оптический прицел.
27 апр 15, 17:00    [17570945]     Ответить | Цитировать Сообщить модератору
 Re: Помогите вычислить новый период для даты  [new]
suPPLer
Member

Откуда: Харків, Україна
Сообщений: 7794
Блог
Alibek B.,

а давайте в качестве примера Вы распишете ту же табличку для 29 февраля 2012 года.
27 апр 15, 17:18    [17571118]     Ответить | Цитировать Сообщить модератору
 Re: Помогите вычислить новый период для даты  [new]
Alibek B.
Member

Откуда:
Сообщений: 3720
В биллинговой системе датами манипулирует Perl, с помощью библиотеки Date::Calc, поэтому сохраняются все ее особенности.
Для интервалов фиксированной протяженности (дни и недели) добавляется соответствующее число дней.
Для месячных интервалов месяц не переносится (31 марта + месяц будет 30 апреля, а не 1 мая).
Для годовых интервалов месяц не переносится (29 февраля + год будет 28 февраля, а не 1 марта).

START_DATE = 2012-02-29
UNITVALUEADJUSTОписание (начало следующего периода)
Y10Ежегодно - 28.02.2013
M10Ежемесячно - 29.03.2012
M11Раз в месяц - 01.03.2012
M31Раз в квартал - 01.04.2012
W10Еженедельно - Ср 07.03.2012
W11Раз в понедельник - Пн 05.03.2012
D10Ежедневно - 01.03.2012
D100Раз в 10 дней - 10.03.2012
27 апр 15, 18:07    [17571461]     Ответить | Цитировать Сообщить модератору
 Re: Помогите вычислить новый период для даты  [new]
перенсица
Guest
Alibek B.
не переносится
30 апреля + 1 месяц = ?
27 апр 15, 18:34    [17571591]     Ответить | Цитировать Сообщить модератору
 Re: Помогите вычислить новый период для даты  [new]
Alibek B.
Member

Откуда:
Сообщений: 3720
В биллинговой системе получается 30 мая.
У меня получился такой монстр:
with S as
(
  select null as START_DATE from DUAL where 0 = 1
  union all select date'2012-02-29' from DUAL
  union all select date'2012-03-30' from DUAL
  union all select date'2012-03-31' from DUAL
  union all select date'2015-04-12' from DUAL
),
FEE as
(
  select null as FEE_PERIOD_UNIT, null as FEE_PERIOD_VALUE, null as FEE_PERIOD_ADJUST from DUAL where 0 = 1
  union all select 'Y', '1', '0' from DUAL
  union all select 'Y', '1', '1' from DUAL
  union all select 'M', '1', '0' from DUAL
  union all select 'M', '1', '1' from DUAL
  union all select 'M', '3', '1' from DUAL
  union all select 'W', '1', '0' from DUAL
  union all select 'W', '1', '1' from DUAL
  union all select 'D', '1', '0' from DUAL
  union all select 'D', '10', '0' from DUAL
)
select FEE.*, S.*
, case FEE.FEE_PERIOD_UNIT
    when 'D' then trunc(S.START_DATE)
    when 'W' then to_date(to_char(S.START_DATE,'YYYY')||to_char(S.START_DATE,'MM')||to_char(decode(FEE.FEE_PERIOD_ADJUST, 0, extract(day from S.START_DATE), extract(day from S.START_DATE) - (trunc(S.START_DATE) - trunc(S.START_DATE, 'IW'))), 'FM00'), 'YYYYMMDD')
    when 'M' then to_date(to_char(S.START_DATE,'YYYY')||to_char(S.START_DATE,'MM')||to_char(decode(FEE.FEE_PERIOD_ADJUST, 0, extract(day from S.START_DATE), FEE.FEE_PERIOD_ADJUST), 'FM00'), 'YYYYMMDD')
    when 'Y' then to_date(to_char(S.START_DATE,'YYYY')||to_char(decode(FEE.FEE_PERIOD_ADJUST, 0, extract(month from S.START_DATE), FEE.FEE_PERIOD_ADJUST), 'FM00')||to_char(decode(FEE.FEE_PERIOD_ADJUST, 0, extract(day from S.START_DATE), 1), 'FM00'), 'YYYYMMDD')
    else null
  end as FEE_PERIOD_START
, case FEE.FEE_PERIOD_UNIT
    when 'D' then trunc(sysdate) + FEE.FEE_PERIOD_VALUE
    when 'W' then to_date(to_char(sysdate,'YYYY')||to_char(sysdate,'MM')||to_char(decode(FEE.FEE_PERIOD_ADJUST, 0, extract(day from sysdate), extract(day from sysdate) - (trunc(sysdate) - trunc(sysdate, 'IW'))), 'FM00'), 'YYYYMMDD') + 7*FEE.FEE_PERIOD_VALUE
    when 'M' then add_months(to_date(to_char(S.START_DATE,'YYYY')||to_char(S.START_DATE,'MM')||to_char(decode(FEE.FEE_PERIOD_ADJUST, 0, extract(day from S.START_DATE), FEE.FEE_PERIOD_ADJUST), 'FM00'), 'YYYYMMDD'), 12*(extract(year from sysdate) - extract(year from S.START_DATE)) + (extract(month from sysdate) - extract(month from S.START_DATE)) + FEE.FEE_PERIOD_VALUE)
    when 'Y' then add_months(to_date(to_char(S.START_DATE,'YYYY')||to_char(decode(FEE.FEE_PERIOD_ADJUST, 0, extract(month from S.START_DATE), FEE.FEE_PERIOD_ADJUST), 'FM00')||to_char(decode(FEE.FEE_PERIOD_ADJUST, 0, extract(day from S.START_DATE), 1), 'FM00'), 'YYYYMMDD'), 12*(extract(year from sysdate) - extract(year from S.START_DATE)) + 12*FEE.FEE_PERIOD_VALUE)
    else null
  end as FEE_PERIOD_NEXT
, add_months(case FEE.FEE_PERIOD_UNIT
    when 'D' then trunc(sysdate) + FEE.FEE_PERIOD_VALUE
    when 'W' then to_date(to_char(sysdate,'YYYY')||to_char(sysdate,'MM')||to_char(decode(FEE.FEE_PERIOD_ADJUST, 0, extract(day from sysdate), extract(day from sysdate) - (trunc(sysdate) - trunc(sysdate, 'IW'))), 'FM00'), 'YYYYMMDD') + 7*FEE.FEE_PERIOD_VALUE
    when 'M' then add_months(to_date(to_char(S.START_DATE,'YYYY')||to_char(S.START_DATE,'MM')||to_char(decode(FEE.FEE_PERIOD_ADJUST, 0, extract(day from S.START_DATE), FEE.FEE_PERIOD_ADJUST), 'FM00'), 'YYYYMMDD'), 12*(extract(year from sysdate) - extract(year from S.START_DATE)) + (extract(month from sysdate) - extract(month from S.START_DATE)) + FEE.FEE_PERIOD_VALUE)
    when 'Y' then add_months(to_date(to_char(S.START_DATE,'YYYY')||to_char(decode(FEE.FEE_PERIOD_ADJUST, 0, extract(month from S.START_DATE), FEE.FEE_PERIOD_ADJUST), 'FM00')||to_char(decode(FEE.FEE_PERIOD_ADJUST, 0, extract(day from S.START_DATE), 1), 'FM00'), 'YYYYMMDD'), 12*(extract(year from sysdate) - extract(year from S.START_DATE)) + 12*FEE.FEE_PERIOD_VALUE)
    else null
  end, -decode(FEE.FEE_PERIOD_UNIT, 'Y', 12*FEE.FEE_PERIOD_VALUE, 'M', FEE.FEE_PERIOD_VALUE, 0)) - decode(FEE.FEE_PERIOD_UNIT, 'W', 7*FEE.FEE_PERIOD_VALUE, 'D', FEE.FEE_PERIOD_VALUE, 0) as FEE_PERIOD_CURRENT
, case FEE.FEE_PERIOD_UNIT
    when 'D' then trunc(sysdate) + FEE.FEE_PERIOD_VALUE
    when 'W' then to_date(to_char(sysdate,'YYYY')||to_char(sysdate,'MM')||to_char(decode(FEE.FEE_PERIOD_ADJUST, 0, extract(day from sysdate), extract(day from sysdate) - (trunc(sysdate) - trunc(sysdate, 'IW'))), 'FM00'), 'YYYYMMDD') + 7*FEE.FEE_PERIOD_VALUE
    when 'M' then add_months(to_date(to_char(S.START_DATE,'YYYY')||to_char(S.START_DATE,'MM')||to_char(decode(FEE.FEE_PERIOD_ADJUST, 0, extract(day from S.START_DATE), FEE.FEE_PERIOD_ADJUST), 'FM00'), 'YYYYMMDD'), 12*(extract(year from sysdate) - extract(year from S.START_DATE)) + (extract(month from sysdate) - extract(month from S.START_DATE)) + FEE.FEE_PERIOD_VALUE)
    when 'Y' then add_months(to_date(to_char(S.START_DATE,'YYYY')||to_char(decode(FEE.FEE_PERIOD_ADJUST, 0, extract(month from S.START_DATE), FEE.FEE_PERIOD_ADJUST), 'FM00')||to_char(decode(FEE.FEE_PERIOD_ADJUST, 0, extract(day from S.START_DATE), 1), 'FM00'), 'YYYYMMDD'), 12*(extract(year from sysdate) - extract(year from S.START_DATE)) + 12*FEE.FEE_PERIOD_VALUE)
    else null
  end -
  add_months(case FEE.FEE_PERIOD_UNIT
    when 'D' then trunc(sysdate) + FEE.FEE_PERIOD_VALUE
    when 'W' then to_date(to_char(sysdate,'YYYY')||to_char(sysdate,'MM')||to_char(decode(FEE.FEE_PERIOD_ADJUST, 0, extract(day from sysdate), extract(day from sysdate) - (trunc(sysdate) - trunc(sysdate, 'IW'))), 'FM00'), 'YYYYMMDD') + 7*FEE.FEE_PERIOD_VALUE
    when 'M' then add_months(to_date(to_char(S.START_DATE,'YYYY')||to_char(S.START_DATE,'MM')||to_char(decode(FEE.FEE_PERIOD_ADJUST, 0, extract(day from S.START_DATE), FEE.FEE_PERIOD_ADJUST), 'FM00'), 'YYYYMMDD'), 12*(extract(year from sysdate) - extract(year from S.START_DATE)) + (extract(month from sysdate) - extract(month from S.START_DATE)) + FEE.FEE_PERIOD_VALUE)
    when 'Y' then add_months(to_date(to_char(S.START_DATE,'YYYY')||to_char(decode(FEE.FEE_PERIOD_ADJUST, 0, extract(month from S.START_DATE), FEE.FEE_PERIOD_ADJUST), 'FM00')||to_char(decode(FEE.FEE_PERIOD_ADJUST, 0, extract(day from S.START_DATE), 1), 'FM00'), 'YYYYMMDD'), 12*(extract(year from sysdate) - extract(year from S.START_DATE)) + 12*FEE.FEE_PERIOD_VALUE)
    else null
  end, -decode(FEE.FEE_PERIOD_UNIT, 'Y', 12*FEE.FEE_PERIOD_VALUE, 'M', FEE.FEE_PERIOD_VALUE, 0)) + decode(FEE.FEE_PERIOD_UNIT, 'W', 7*FEE.FEE_PERIOD_VALUE, 'D', FEE.FEE_PERIOD_VALUE, 0) as FEE_PERIOD_DAYS
from S, FEE
order by decode(FEE_PERIOD_UNIT, 'Y', 360, 'M', 30, 'W', 7, 'D', 1) desc, FEE_PERIOD_VALUE, FEE_PERIOD_ADJUST, START_DATE

Можно ли это как-то сократить и в более читаемый вид привести?
Этот код не совсем соответствуют поведению Date::Calc, в этом примере 30 апреля + месяц получится 31 мая.
В принципе, следовало бы дополнительно обрабатывать подобные случаи, но тогда SQL-запрос станет вдвое больше, а он и так какой-то избыточный.
27 апр 15, 20:16    [17572106]     Ответить | Цитировать Сообщить модератору
 Re: Помогите вычислить новый период для даты  [new]
oragraf
Member

Откуда: Moscow
Сообщений: 1310
нефига плодить монстров. Создай табличку как в DWH - измерение дата, где для каждой даты будут заполнены эти поля и все.
27 апр 15, 20:28    [17572148]     Ответить | Цитировать Сообщить модератору
 Re: Помогите вычислить новый период для даты  [new]
Alibek B.
Member

Откуда:
Сообщений: 3720
Имеется ввиду на каждый день года для каждого возможного периода создать интервалы на N лет вперед?
Объем это в принципе небольшой, менее 100 тысяч строк.
Но эта таблица будет привязана к существующим периодам. Если я изменю или добавлю новый, таблицу нужно будет пересчитывать.
А поскольку изменяться периоды будут редко, то об обновлении таблицы можно и забыть.
27 апр 15, 20:41    [17572181]     Ответить | Цитировать Сообщить модератору
 Re: Помогите вычислить новый период для даты  [new]
suPPLer
Member

Откуда: Харків, Україна
Сообщений: 7794
Блог
Alibek B.
Можно ли это как-то сократить и в более читаемый вид привести?


with S as
(
  select null as START_DATE from DUAL where 0 = 1
  union all select date'2011-02-28' from DUAL
  union all select date'2011-03-12' from DUAL
  union all select date'2012-02-29' from DUAL
  union all select date'2012-03-31' from DUAL
  union all select date'2015-04-12' from DUAL
),
FEE as
(
  select null as PERIOD_UNIT, null as PERIOD_VALUE, null as PERIOD_ADJUST from DUAL where 0 = 1
  union all select 'Y', '1', '0' from DUAL
  union all select 'Y', '1', '1' from DUAL
  union all select 'M', '1', '0' from DUAL
  union all select 'M', '1', '1' from DUAL
  union all select 'M', '3', '1' from DUAL
  union all select 'W', '1', '0' from DUAL
  union all select 'W', '1', '1' from DUAL
  union all select 'D', '1', '0' from DUAL
  union all select 'D', '10', '0' from DUAL
) ,
-----------------------------------------------------------------------------------------------------------
my_fee as
( select decode( period_unit, 'Y', 'YYYY', 'M', 'MM', 'W', 'IW', 'D', 'DD' ) format
       , period_unit unit
       , to_number( period_value ) val
       , to_number( period_adjust ) adj 
       , decode( period_unit, 'Y', 12, 'M', 1, 'W', 7, 'D', 1 ) q
    from fee )
select start_date, unit, val, adj
     , case 
         when unit in ( 'M', 'Y' ) then 
           add_months( decode( adj, 1, trunc( start_date, format ), start_date ), val * q )
           - decode( adj, 0, greatest( extract( day from add_months( start_date, val * q ) ) - extract( day from start_date ), 0 ), 0 )
         when unit in ( 'W', 'D' ) then
           decode( adj, 1, trunc( start_date, format ), start_date ) + val * q
        end next_date
  from my_fee, s
27 апр 15, 20:57    [17572240]     Ответить | Цитировать Сообщить модератору
 Re: Помогите вычислить новый период для даты  [new]
Alibek B.
Member

Откуда:
Сообщений: 3720
Круто, спасибо.
Завтра буду приспосабливать.
27 апр 15, 21:47    [17572415]     Ответить | Цитировать Сообщить модератору
 Re: Помогите вычислить новый период для даты  [new]
Alibek B.
Member

Откуда:
Сообщений: 3720
suPPLer
decode( adj, 1, trunc( start_date, format )

Сразу не заметил.
ADJUST это не флаг (1/0).
Это значение более мелкой единицы периода, которое будет использовано для начала периода.
Например для UNIT='W' указывается день недели, который будет началом периода (1 - понедельник, 5 - пятница).
Для UNIT='M' указывается день недели, который будет началом периода (1 - первое число месяца, 5 - пятое число месяца).
Для UNIT='Y' указывается номер месяца, первое число которого будет началом периода.
Для UNIT='D' игнорируется.
ADJUST=0 означает, что началом периода будет то значение (день недели, день месяца, номер месяца), которое было на стартовую дату.

Тем не менее, даже мой скрипт данным способом можно здорово сократить, спасибо.
28 апр 15, 08:58    [17573363]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить