Связать не связанные таблицы

Serg SSWM
Дата: 05.12.2012 09:09:02
Доброе утро.

Помогите решить задание. Есть 2 таблицы:
Motion (Движение) - записываются суммы долгов (операция 1) и возвратов (операция 2)
Rates (Курсы) - дата, с которой начинает действовать процентная ставка за пользованием остатка долга

Таблица Motion:
Дата       Операция     Сумма
ArcDate OperationId Summa
01.11.2012 1 500
01.11.2012 2 80
07.11.2012 2 150
09.11.2012 2 50
20.11.2012 2 220

Таблица Rates (курсы):
Дата        Курс							
ArcDate Rate
01.09.2012 13
05.11.2012 15
15.11.2012 17
25.11.2012 18

Нужно получить следующую таблицу:
Дата С      Дата По     Одолжил   Вернул  Остаток  База  Курс  Дней  Процент
ValidDate TillDate SummaOut SummaIn Rest Baza Rate Days Penalty
01.11.2012 01.11.2012 500 80 420 500 13 1 0,18
02.11.2012 04.11.2012 0 0 420 420 13 3 0,45
05.11.2012 07.11.2012 0 150 270 420 15 3 0,52
08.11.2012 09.11.2012 0 50 220 270 15 2 0,22
10.11.2012 14.11.2012 0 0 220 220 15 5 0,45
15.11.2012 20.11.2012 0 220 0 220 17 6 0,61

База - на эту сумму расчитываем процент
Дней - разница между ДатаПо и ДатаС + 1
Процент - База * Курс / 100 / 365 * Дней

Условия:
- если долг взят и отдан в один день, считаем один день
- нужно пересчитывать остаток долга в зависимости от курса на дату
- нужно формировать запрос на дату, к примеру при указании даты 18.11.2012 последняя строка должна быть следующая:

ValidDate   TillDate    SummaOut  SummaIn  Rest    Baza  Rate  Days  Penalty
15.11.2012 28.11.2012 0 0 220 220 17 4 0,41

Делаю выборка под Ораклом 11.
Изя Кацман
Дата: 05.12.2012 10:40:00
Камрад Serg SSWM,
Почитай вот ето на форуме "Работа": Студентам, желающим помощи
и определись по какому Пути ты хочешь пойти :)
Serg SSWM
Дата: 05.12.2012 11:16:13
Добрый день, Изя Кацман.

Я запрос сделал, но он мне не очень нравится. Вот сам запрос:
WITH u as
(select (add_months(trunc(sysdate,'yyyy'),12)-trunc(sysdate,'yyyy')) as days from dual)

select
  ValueDate, SummaIn, SummaOut, greatest(summain,summaout) as Total, TillDate, Duration, Rate, 
       round(greatest(summain,summaout)*(Duration)*(rate/100)/u.days) penalty
from (       
select
  ValueDate, SummaIn, SummaOut, TillDate, Duration,
        FIRST_VALUE(rate) over (PARTITION BY cnt ORDER BY valuedate) rate
from
(        
select 
  ValueDate, SummaIn, SummaOut, TillDate, TillDate-ValueDate+1 Duration, Rate,
  COUNT(RATE) OVER(ORDER BY valuedate) as cnt  
from (  

select 
  case when SummaIn=0
    then (lag(tilldate, 1) over (order by valuedate))+1
    else TillDate
  end ValueDate,
  SummaIn, SummaOut, TillDate, Rate
From
(  
select 
  ddt.valuedate, 
  sum(decode(ddt.dealdocumenttypeid,16,DDT.usesumma,0)) SummaIn,
  sum(decode(ddt.dealdocumenttypeid,12,DDT.usesumma,0)) SummaOut,
  case when sum(decode(ddt.dealdocumenttypeid,12,DDT.usesumma,0))>0
    then valuedate
    else (lead(valuedate, 1, to_date('26/11/2012','dd.mm.yyyy')+1) over (order by valuedate))-1 
  end tilldate,
  null rate
from dealcommercialloan l, dealcommercialtranche t, dealdoctransaction ddt, aaccount a
where t.dealid=ddt.dealid and t.loandealid=l.dealid
  and (
       (ddt.dealdocumenttypeid in (11,12) and ddt.accountid<>a.id)
        or
       (ddt.dealdocumenttypeid=16 and ddt.accountid=a.id)
      )
  and a.accountno=l.interestAccrualACCOUNTNO and a.currencyid=l.currencyid
  and t.dealid=100555
group by ddt.valuedate
having ddt.valuedate<='26112012'
)
union all
SELECT arcdate as valuedate, 0,0,null,rate FROM VInterestBaseValue where interestbaseid=10
)
)
) r, u
where  not (r.summain=0 and r.summaout=0)


В своем вопросе я упростил задание, дальше доделаю/подставлю сам.
dealdocumenttypeid=16 - это дать в долг
dealdocumenttypeid=12 - возврат долга
VInterestBaseValue - хранятся курсы
Serg SSWM
Дата: 05.12.2012 11:40:45
Заполненные таблицы
(
  select ArcDate, OperationId, Summa from 
  (
    select '01112012' ArcDate, 1 OperationId, 500 Summa from dual
    union
    select '01112012' ArcDate, 2 OperationId, 80  Summa from dual
    union
    select '07112012' ArcDate, 2 OperationId, 150 Summa from dual
    union
    select '09112012' ArcDate, 2 OperationId, 50  Summa from dual
    union
    select '20112012' ArcDate, 2 OperationId, 220 Summa from dual
  )
) Motion
,
(
  select ArcDate, Rate from 
  (
    select '01092012' ArcDate, 13 Rate from dual
    union
    select '05112012' ArcDate, 15 Rate from dual
    union
    select '15112012' ArcDate, 17 Rate from dual
    union
    select '25112012' ArcDate, 18 Rate from dual
  )
) Rates
Serg SSWM
Дата: 05.12.2012 15:04:47
Сделал запрос, но уж больно не оптимизирован, может "гуру" поправят.

Запрос:
with Rates as
(
  select ArcDate, Rate from
  (
    select ArcDate, Rate from 
    (
        select to_date('01092012') ArcDate, 13 Rate from dual
        union
        select to_date('05112012') ArcDate, 15 Rate from dual
        union
        select to_date('15112012') ArcDate, 17 Rate from dual
        union
        select to_date('25112012') ArcDate, 18 Rate from dual
    )
  )  
)

-- Подсчитываю пеню
select
  FromDate, TillDate, SummaOut, SummaIn, Rest, Baza, Rate, Days, round(Baza * Rate / 365 * Days)/100 Penalty
from
    -- Узнаем базу, убираем не нужные строки
    (
    select
      FromDate, TillDate, SummaOut, SummaIn, Rest, 
      case when SummaOut>0
        then SummaOut
        else LAG(Rest) OVER (ORDER BY TillDate)
      end Baza,  
      Rate, Days
    from
        -- Определяем Курс, Кол-во дней
        (
        select 
          FromDate, TillDate, SummaOut, SummaIn, 
          SUM(SummaOut-SummaIn) OVER(ORDER BY FromDate) as Rest,
          (select Rate from Rates where ArcDate=(select MAX(ArcDate) from Rates where ArcDate<=B.TillDate)) Rate,
          (TillDate-FromDate)+1 Days
        from
            -- Определяем Дату C
            (
            select 
              case when SummaOut>0
                then 
                  TillDate
                else
                  (LAG(TillDate) OVER (ORDER BY TillDate))+1
              end FromDate, TillDate, SummaOut, SummaIn
            from 
              -- Определяем Дату По
              (  
                 select 
                   case when SummaIn>0 then ArcDate else ArcDate-1 end TillDate, SummaOut, SummaIn
                 from
                   -- Разделяем на "Одолжить" и "Возврат", добавляем таблицу с Курсами, сортируем
                   (
                        select 
                          Arcdate, sum(decode(OperationId, 1, Summa, 0)) SummaOut, sum(decode(OperationId, 2, Summa, 0)) SummaIn, Null Rate
                        from  
                        (
                          select ArcDate, OperationId, Summa from 
                          (
                            select to_date('01112012') ArcDate, 1 OperationId, 500 Summa from dual
                            union
                            select to_date('01112012') ArcDate, 2 OperationId, 80  Summa from dual
                            union
                            select to_date('07112012') ArcDate, 2 OperationId, 150 Summa from dual
                            union
                            select to_date('09112012') ArcDate, 2 OperationId, 50  Summa from dual
                            union
                            select to_date('20112012') ArcDate, 2 OperationId, 220 Summa from dual
                          )
                        ) Motion
                        group by ArcDate
                        union 
                        select ArcDate, 0, 0, Rate from Rates 
                        order by 1
                   )
                   -- Конец Разделяем на "Одолжить" и "Возврат", добавляем таблицу с Курсами, сортируем
              ) 
              -- Конец Определяем Дату По
            ) B
            -- Конец Определяем Дату C
        ) 
        -- Конец Определяем Курс, Кол-во дней    
    where not (SummaOut=0 and SummaIn=0 and Rest=0)
    )
    -- Конец Узнаем базу, убираем не нужные строки
-- Конец Подсчитываю пеню