GoldenLine
Zaloguj się
Łukasz W.

Łukasz W. Senior Oracle
Developer

Temat: obliczanie ilości miesięcy w poszczególnych latach

Mam taki problem. Mam tabelę z kolumnami: ID (unique), data_od i data_do. Okres czasu między datami jest ciągły, ale dla poszczególnych ID jest różny, np. dla Id = 2 data_od to 01.05.2014 a data_do to 31.10.2016.
A dla Id = 3 data_od to 01.07.2014 a data_do to 31.10.2014.
Zadanie jest takie, żeby policzyć liczbę miesięcy dla poszczególnych lat w podanym okresie. Nie chodzi o całościową liczbę miesięcy, czyli data_do - data_od, ale liczbę miesięcy dla każdego roku osobno. Z tym, że jeśli mówimy o pierwszym przykładzie to tych lat będzie 3, a więc trzeba policzyć liczbę miesięcy dla tych trzech lat z osobna, a w drugim przykładzie będzie to jeden rok, a więc liczba miesięcy dla tego jednego roku.
Oczywiście te zakresy mogą być większe, np. obejmować 50 lat itd., więc chodzi o jakiś jeden algorytm, który będzie liczył dla każdej z takich ewentualności.
Prawdopodobnie trzeba będzie to zrobić jakoś w pętli, aczkolwiek nie mam pomysłu jak się do tego zabrać. Może ktoś z was będzie wiedział jak to zrobić.
Link do wypowiedziLink
Kamil Stawiarski

Kamil Stawiarski Oracle Certified
Master | Oracle ACE

Temat: obliczanie ilości miesięcy w poszczególnych latach

Łukasz, a możesz podać przykład rozwiązania jaki chcesz uzyskać? Jak to ma dokładnie wyglądać?
Link do wypowiedziLink
Łukasz W.

Łukasz W. Senior Oracle
Developer

Temat: obliczanie ilości miesięcy w poszczególnych latach

Jest jedna tabela, którą przedstawiłem wcześniej z id (unikalne), data_od i data_do i druga tabela z id, które nie są już unikalne, jest to klucz obcy pierwszej tabeli. W zależności od ilości lat (dla których chcemy policzyć liczbę miesięcy) tyle jest rekordów o takim samym id w drugiej tabeli.
Chodzi o wyliczenie przez ile miesięcy w danym roku obowiązywała dana umowa. Potem jest to potrzebne do dalszych wyliczeń, ale tutaj chodzi mi o to jak napisać taki blok, który by to liczył.
Wiadomo na pewno, że pierwszy rok to będzie zawsze 31.12.yyyy - data_od chyba, że umowa trwała tylko przez ileś miesięcy, więc będzie to data_do - data_od. W ostatnim roku będzie to data_do - 01.01.yyyy, lata w środku będą miały zawsze po 12 miesięcy. Tylko jak napisać taki uniwersalny kod, który to będzie liczył dla każdego przypadku, w sensie różnej ilości lat obowiązywania umowy i różnych dat data_od i data_do.Ten post został edytowany przez Autora dnia 16.10.14 o godzinie 20:12
Link do wypowiedziLink
Andrzej P.

Andrzej P. Oracle Database
Consultant

Temat: obliczanie ilości miesięcy w poszczególnych latach

Taki kod policzy miesiące kalendarzowe w podziale na id oraz konkretne lata:
WITH temp(id, miesiac, koniec) AS (

  SELECT id, to_char(data_od,'yyyy.mm') miesiac, to_char(data_do, 'yyyy.mm') koniec FROM tabela

  UNION ALL

  SELECT id, to_char(add_months(to_date(miesiac,'yyyy.mm'), 1),'yyyy.mm'), koniec FROM temp

  WHERE add_months(to_date(miesiac,'yyyy.mm'), 1) <= to_date(koniec, 'yyyy.mm')

)

SELECT id, substr(miesiac, 1, 4) rok, COUNT(*) FROM temp

GROUP BY id, substr(miesiac, 1, 4)

ORDER BY id, substr(miesiac, 1, 4);

Wyniki:
        ID ROK    COUNT(*)

---------- ---- ----------

         2 2014          8

         2 2015         12

         2 2016         10

         3 2014          4

Kod jest brzydki (dużo to_char i to_date), ale dzięki temu zadziała na większej ilości silników bazy danych.
Do wersji 11.2.0.2 włącznie występował bug, który nie pozwalał np. wykorzystać trunc(date ...) w zapytaniach hierarchicznych podobnych do tego.
Link do wypowiedziLink
Łukasz W.

Łukasz W. Senior Oracle
Developer

Temat: obliczanie ilości miesięcy w poszczególnych latach

Dzięki za pomoc. Bardziej by mi jednak odpowiadał blok niż zapytanie select. Może źle trochę to wytłumaczyłem. W zasadzie druga tabela jest mało istotna, chciałem tylko przedstawić jak mniej więcej wygląda logika i struktura.
Za pomocą funkcji wyliczyłem sobie liczbę lat, która przypada na daną umowę i jest ona pod zmienną v_lata.
Teraz potrzebuję blok, który będzie liczył ilość miesięcy w tych poszczególnych latach. Prawdopodobnie da się to jakoś dość prosto zrobić w pętli for, jednak nie wiem jak to wykombinować.
Link do wypowiedziLink
Tomasz Kania

Tomasz Kania DBA, SoftSystem Sp.
z o.o.

Temat: obliczanie ilości miesięcy w poszczególnych latach

To może chociaż przedstaw jakiś przykładowy wynik, który chcesz uzyskać...
Zapytanie Andrzeja możesz bez problemu "włożyć" do bloku PL/SQL.
Z tego co zrozumiałem, to na wejściu chcesz podać (id,data_od,data_do) a na wyjściu chcesz otrzymać...? no właśnie, co?
Jakąś tablicę ?
Doprecyzuj wymagania.Ten post został edytowany przez Autora dnia 17.10.14 o godzinie 18:55
Link do wypowiedziLink
Łukasz W.

Łukasz W. Senior Oracle
Developer

Temat: obliczanie ilości miesięcy w poszczególnych latach

Procedura ta liczy marże od każdej umowy. Z tym, że każda umowa, która występuje w pierwszej tabeli i ma unikalny ID jest zawierana na różne okresy czasu. Np. umowa 1 od 1.04.2014 do 31.10.2016,, umowa nr 2 w jakimś innym przedziale czasowym, np. 50 lat. Marża zaś liczona jest nie od umowy, a od każdego roku, w którym jest ona zawarta. Z tym, że jak np. w umowie nr 1 w pierwszym roku i ostatnim nie jest to 12 miesięcy, a kilka, a umowa liczy się tak, że chociaż np. w pierwszym roku umowa trwałaby tylko miesiąc, a kończyłaby się np. w styczniu przyszłego roku to mimo to liczymy dwie marże. Stąd, aby naliczyć marże trzeba policzyć ilość miesięcy ile występuje w danym roku trwania danej umowy i pomnożyć przez daną stawkę umowy, która jest stała i naliczana dla każdego miesiąca.
W procedurze jako parametr wejściowy podajemy tylko ID, jako parametr wyjściowy już wyliczoną prowizję dla każdego roku osobno. Więc tak naprawdę trzeba to policzyć w pętli, z tym, że to da wtedy tyle wyników dla ilu lat liczymy marże. Dlatego raczej wyniku nie da się zapisać w zmiennej. Myślę, że najlepiej byłoby to zapisać do innej tabeli, gdzie id nie będzie unikalne (będzie występowało tyle razy ile lat). Ewentualnie zrobić klucz główny na dwóch kolumnach ID i rok.
Link do wypowiedziLink
Tomasz Kania

Tomasz Kania DBA, SoftSystem Sp.
z o.o.

Temat: obliczanie ilości miesięcy w poszczególnych latach

No cóż, dalej nie opisałeś dokładnie w jakiej postaci chcesz mieć wynik wyjściowy...
Osobiście pewnie wybrałbym rozwiązanie Andrzeja, ale jak już musi być w PL/SQL to np. takie "cóś"...


declare

  dt_od date;

  dt_do date;

  um_id number := 2;  

  type rmies is table of pls_integer index by pls_integer;

  umowy_mies rmies;

  rok pls_integer;

  prev_rok pls_integer;

  ile_mies pls_integer := 1;

begin

  select last_day(data_od),last_day(data_do),extract(year from data_od) into dt_od,dt_do,prev_rok from umowy where id=um_id;  

  umowy_mies(prev_rok) := ile_mies;  

  loop

    dt_od := add_months(dt_od,1);

    exit when dt_od > dt_do;

    rok := extract(year from dt_od);

    if rok > prev_rok then 

      ile_mies := 1;

      prev_rok := rok;

    else

      ile_mies := ile_mies + 1;

    end if;

    umowy_mies(rok) := ile_mies;         

  end loop;

  

  for i in umowy_mies.first..umowy_mies.last     

    loop

      --tutaj mozesz sobie policzyc "prowizje/marże"

      dbms_output.put_line('rok:' || i || ', ile_mies: ' || umowy_mies(i));

    end loop;

end;

/
Link do wypowiedziLink
Monika Mitura

Monika Mitura Oracle Developer,
Pretius

Temat: obliczanie ilości miesięcy w poszczególnych latach

czyli jestli masz umowe od 12.2014 do stycznie 2016 to będa trzy prowizje?
Jeśli koniec i poczatek to sa zawsze pierwszy dzien miesiaca i ostatni to możę coś takiego

declare
DStart date := to_date('01122014','ddmmyyyy') ;
dEnd date :=to_date('31012016','ddmmyyyy') ;
dCurrentDate date;
dCurEnd date;
nIleMies number;
nIleLat number;
begin
nIleLat := to_number( to_char(dend , 'yyyy')) - to_number(to_char(dstart , 'yyyy')) + 1 ;
dCurrentDate := dStart;

dbms_output.put_line ( nIleLat );
for i in 1..nIleLat loop
dCurEnd := least(dEnd ,to_date('3112'||to_char(dCurrentDate , 'yyyy'), 'ddmmyyyy'));

nIleMies :=trunc(months_between(dcurend, dcurrentdate)) +1;

dbms_output.put_line(dCurEnd || ' ' || dCurrentDate || ' ' || nilemies);
dCurrentDate := to_date('0101'||to_char(to_number(to_char(dCurrentDate , 'yyyy') )+1),'ddmmyyyy');

end loop;

end;
Link do wypowiedziLink
« Wróć do tematów
Odpowiedz

Podobne tematy


Następna dyskusja:

Oracle XE/ Linux "zawisa" n...




Wyślij zaproszenie do
Anuluj