DDL
CREATE TABLE test(
id INT,
d_od DATE,
d_do DATE,
wartosc NUMBER(15,2));
CREATE SEQUENCE sq_test_id;
CREATE TRIGGER tr_test_id_bi
BEFORE INSERT ON test
FOR EACH ROW
BEGIN
:new.id := sq_test_id.NEXTVAL;
END;
/
dane
INSERT INTO test(d_od, d_do, wartosc) VALUES (To_Date('01.06.2015', 'dd.mm.yyyy'), To_Date('31.12.2016', 'dd.mm.yyyy'), 324);
INSERT INTO test(d_od, d_do, wartosc) VALUES (To_Date('01.01.2016', 'dd.mm.yyyy'), To_Date('31.12.2017', 'dd.mm.yyyy'), 123);
INSERT INTO test(d_od, d_do, wartosc) VALUES (To_Date('01.03.2017', 'dd.mm.yyyy'), To_Date('31.12.2017', 'dd.mm.yyyy'), 45);
INSERT INTO test(d_od, d_do, wartosc) VALUES (To_Date('01.06.2017', 'dd.mm.yyyy'), To_Date('31.12.2017', 'dd.mm.yyyy'), 75);
INSERT INTO test(d_od, d_do, wartosc) VALUES (To_Date('01.01.2017', 'dd.mm.yyyy'), To_Date('31.12.2019', 'dd.mm.yyyy'), 898);
INSERT INTO test(d_od, d_do, wartosc) VALUES (To_Date('01.01.2017', 'dd.mm.yyyy'), To_Date('31.12.2018', 'dd.mm.yyyy'), 543);
widok, który zwraca wszystkie miesiące pomiędzy najmniejszą i największą datą dla podanych danych (dla uproszczenia zapytania)
CREATE VIEW test_miesiace as
select
add_months(d_min, level-1) d_od,
add_months(d_min, level) - 1 d_do
from
(SELECT
Min(d_min) d_min,
Max(d_max) d_max
FROM
(SELECT
Least(d_od, d_do) d_min,
Greatest(d_od, d_do) d_max
FROM
test
)
)
CONNECT BY LEVEL <= Months_Between(Trunc(d_max,'MM'), Trunc(d_min,'MM'))+ 1;
liczymy wartość dla każdego miesiąca w danym roku
SELECT
To_Char(m.d_od, 'YYYY') rok,
To_Char(m.d_od, 'MM') miesiac,
Round(Sum(CASE WHEN t.d_od <= m.d_do AND t.d_do >= m.d_od THEN Least(m.d_do, t.d_do) - Greatest(m.d_od, t.d_od) + 1 ELSE 0 END * t.wartosc / 365)) wartosc
FROM
test_miesiace m,
test t
GROUP BY
To_Char(m.d_od, 'YYYY'),
To_Char(m.d_od, 'MM')
i na koniec robimy z tego pivota
SELECT
rok,
Sum(CASE WHEN miesiac = '01' THEN wartosc ELSE 0 END) styczen,
Sum(CASE WHEN miesiac = '02' THEN wartosc ELSE 0 END) luty,
Sum(CASE WHEN miesiac = '03' THEN wartosc ELSE 0 END) marzec,
Sum(CASE WHEN miesiac = '04' THEN wartosc ELSE 0 END) kwiecien,
Sum(CASE WHEN miesiac = '05' THEN wartosc ELSE 0 END) maj,
Sum(CASE WHEN miesiac = '06' THEN wartosc ELSE 0 END) czerwiec,
Sum(CASE WHEN miesiac = '07' THEN wartosc ELSE 0 END) lipiec,
Sum(CASE WHEN miesiac = '08' THEN wartosc ELSE 0 END) sierpien,
Sum(CASE WHEN miesiac = '09' THEN wartosc ELSE 0 END) wrzesien,
Sum(CASE WHEN miesiac = '10' THEN wartosc ELSE 0 END) pazdziernik,
Sum(CASE WHEN miesiac = '11' THEN wartosc ELSE 0 END) listopad,
Sum(CASE WHEN miesiac = '12' THEN wartosc ELSE 0 END) grudzien
FROM
(SELECT
To_Char(m.d_od, 'YYYY') rok,
To_Char(m.d_od, 'MM') miesiac,
Round(Sum(CASE WHEN t.d_od <= m.d_do AND t.d_do >= m.d_od THEN Least(m.d_do, t.d_do) - Greatest(m.d_od, t.d_od) + 1 ELSE 0 END * t.wartosc / (add_months(trunc(m.d_od,'year'), 12) - trunc(m.d_od,'year')))) wartosc
FROM
test_miesiace m,
test t
GROUP BY
To_Char(m.d_od, 'YYYY'),
To_Char(m.d_od, 'MM'))
GROUP BY
rok
ORDER BY
rok
PS. to (add_months(trunc(m.d_od,'year'), 12) - trunc(m.d_od,'year'))
liczy dni w roku - chodzi o lata przestępne
wynik: