Добавление записи в запросе при условии

[Rekky]

Доброго дня всем.
Даны два столбца (выбираются запросом). В первом даты по возрастанию. второй характеризует високосность года.
1 28.07.2010 365
2 30.09.2010 365
3 30.12.2010 365
4 30.03.2011 365
5 30.06.2011 365
6 30.09.2011 365
7 30.12.2011 365
8 30.03.2012 366
9 02.07.2012 366
10 01.10.2012 366
11 31.12.2012 366
12 01.04.2013 365
13 01.07.2013 365

Необходимо при смене года с обычного на високосный и наоборот добавлять запись: 31.12.<нужный год> <кол-во дней в году>, так же в запросе.
например между 7 и 8 записью должна появится 31.12.2011 365
Буду признательна за помощь.

[soleil@mmc]

субд?
а что делать в вашем случае для записи №11 - она на месте, но по условию

при смене года с обычного на високосный и наоборот добавлять запись: 31.12.<нужный год> <кол-во дней в году>

[Rekky]

извините, забыла указать.oracle.
между 11 и 12 строк добавлять не нужно.

[Serge_Bliznykov]

к слову, это обязательно надо в SQL запросе сделать?
а процедура на PL/SQL не пойдёт?
или, можно это вообще на клиенте сделать... (если он есть, конечно )

[Rekky]

обязательно в запросе.
есть предположение сделать все юнионами.
например к выборке всех дат добавить сначала юнион где есть переход с 365 на 366. там где все 365 заменить дату на 31.12, затем добавить юнион с 366 на 365 и изменить значение. затем отсортировать все это дело по дате.
но как в where сравнить две соседние даты большой для меня вопрос)

 where (year_long = 365 and lead(year_long) over(partition by id1 order by dateout) = 366)

ругается, что функции окна в данном месте запрещены((
Здесь я пыталась использовать union all

Также есть идея с использованием union. К выборке всех дат добавить юнион и там уже в селекте проверять даты где есть переход и заменять case? дублирующиеся строки уберутся union- ом. Но это пока в теории, возможно встретится еще какое препятствие. к тому же это очень громоздко.

[Stilet]

как в where сравнить две соседние даты большой для меня вопрос)

Есть в Оракле так называемые Аналитические функции

Функции LEAD и LAG можно рассматривать как способы индексации в пределах
группы. С помощью этих функций можно обратиться к любой отдельной строке.

Может тебе они помогут?

[Rekky]

Так вот lead в where использовать нельзя..ну я решила воспользоваться вторым способом, где использовать в селект можно.

select id1, date, year_long from table
union id1,
select 
case when year_long = 365 and lead(year_long) over(partition by id1 order by date) = 366 and to_char(date) <> '31.12.'||To_char(date, 'yyyy')
then to_date('31.12.'||to_char(date,'yyyy'),'dd.mm.yyyy')
when year_long = 366 and lead(year_long) over(partition by id1 order by date) = 365 and to_char(date) <> '31.12.'||To_char(date, 'yyyy')
then to_date('31.12.'||to_char(date,'yyyy'),'dd.mm.yyyy')
else date
end date,
id1
order by id, dateout

Дублирующие строки убирает, но и все вроде бы правильно работает на примере...но не знаю насколько это безопасно и корректно

[soleil@mmc]

чота даже обкатать идеи не на чем в данный момент

идея №1
добавляем поле

nvl(lag(year_long) over(order by dateout), year_long)

(чота не очевидно для чего вам там партишн по айди, в условии никаких айди нет, но при необходимости прикрутите и партишн)
потом сверху на это поле прикручиваем кейс, в котором ищем переход 366 -> 365 и 365 -> 366
полкчаем такое (с учетом проверки на существование такой даты в наборе данных - внутренний кейс)

case
when
  ((nvl(lag(year_long) over(order by dateout), year_long) = 366) and (year_long = 365) 
or (nvl(lag(year_long) over(order by dateout), year_long) = 365) and (year_long = 366))
then 
     case 
     when (dateout <> (trunc(dateout, 'yy') + year_long - 1))  then 1
     else 0
     end
  else 0
end

в итоге получаем нечто такое

select
  dateout,
  year_long
from t
union all
select
  trunc(dateout, 'yy') + year_long -1 dateout,
  year_long
from(
select
  dateout,
  year_long,
  case
   when
      ((nvl(lag(year_long) over(order by dateout), year_long) = 366) and (year_long = 365) 
      or (nvl(lag(year_long) over(order by dateout), year_long) = 365) and (year_long =  366))
    then 
       case 
        when (dateout <> (trunc(dateout, 'yy') + year_long - 1))  then 1
        else 0
        end
      else 0
    end f1
from t)
where 0=0
   and f1 = 1

идея №2
раз все знают правила определения високосного года

год является високосным, если он кратен 4 и при этом не кратен 100, либо кратен 400. Год не является високосным, если он не кратен 4, либо кратен 100 и не кратен 400.

по этим правилам определить какой год високосный из данного набора записей, есть ли у него предшествующий год в этом наборе и по этим данным добавить недостающие строки


апд
еще раз пеерчитал свою писанину и понял что lag() везде меняем на lead() и все будет работать

2 Rekky

неявное приведение даты в строку может оказаться бомбой замедленного действия

to_char(date) <> '31.12.'||To_char(date, 'yyyy')

апд 2
проверил пример после замены лаг на лид - все работает как надо

with 
  t as (
  select to_date('28.07.2010', 'DD.MM.YYYY') dateout, 365 year_long from dual union all
  select to_date('30.09.2010', 'DD.MM.YYYY'), 365 from dual union all
  select to_date('30.12.2010', 'DD.MM.YYYY'), 365 from dual union all
  select to_date('30.03.2011', 'DD.MM.YYYY'), 365 from dual union all
  select to_date('30.06.2011', 'DD.MM.YYYY'), 365 from dual union all
  select to_date('30.09.2011', 'DD.MM.YYYY'), 365 from dual union all
  select to_date('30.12.2011', 'DD.MM.YYYY'), 365 from dual union all
  select to_date('30.03.2012', 'DD.MM.YYYY'), 366 from dual union all
  select to_date('02.07.2012', 'DD.MM.YYYY'), 366 from dual union all
  select to_date('01.10.2012', 'DD.MM.YYYY'), 366 from dual union all
  select to_date('31.12.2012', 'DD.MM.YYYY'), 366 from dual union all
  select to_date('01.04.2013', 'DD.MM.YYYY'), 365 from dual union all
  select to_date('01.07.2013', 'DD.MM.YYYY'), 365 from dual
  )
  
select
  dateout,
  year_long
from t
union all
select
  trunc(dateout, 'yy') + year_long -1 dateout,
  year_long
from(
select
  dateout,
  year_long,
  case
   when
      ((nvl(lead(year_long) over(order by dateout), year_long) = 366) and (year_long = 365) 
      or (nvl(lead(year_long) over(order by dateout), year_long) = 365) and (year_long =  366))
    then 
       case 
        when (dateout <> (trunc(dateout, 'yy') + year_long - 1))  then 1
        else 0
        end
      else 0
    end f1
from t)
where 0=0
   and f1 = 1
order by dateout

[Rekky]

Да проверила по таблицам, все отлично работает, спасибо, ваш вариант пожалуй лучше только вопрос для чего нужно условие 0 = 0 ?
P.S. пыталась добавить вам отзыв, но не позволили придется ограничится СПАСИБО

[soleil@mmc]

Сообщение от Rekky

только вопрос для чего нужно условие 0 = 0 ?

для красоты
и еще для легкого комментирования остальных фильтров запроса