We have a tricky situtation to find out the yearly value of a service contract and display the same amount in every month of the year based on contract starting date and ending date. the example is given below.
[code]
contract id start date end date year yearly value
34 01/06/2022 30/09/2022 2022 100
34 01/01/2023 30/09/2023 2023 500
1 01/01/2022 31/12/2022 2022 400
create table contract_det (contract_id varchar2(10), from_date date, expiry_date date, contract_year varchar2(5), revenue number)
insert into contract_det (contract_id , from_date, expiry_date, contract_year, revenue) values ('34',to_date('01-JUN-2022'),TO_DATE('30-SEP-2022'),'2022',100);
insert into contract_det (contract_id , from_date, expiry_date, contract_year, revenue) values ('34',to_date('01-JAN-2023'),TO_DATE('30-SEP-2023'),'2023',500);
insert into contract_det (contract_id , from_date, expiry_date, contract_year, revenue) values ('1',to_date('01-JAN-2022'),TO_DATE('31-DEC-2022'),'2022',400);
--desired output is
contract id jan feb mar apr may jun jul aug sep oct nov dec year
34 100 100 100 100 2022
34 500 500 500 500 500 500 500 500 500 500 500 500 2023
1 400 400 400 400 400 400 400 400 400 400 400 400 2022
-- the Criteria above is from_date and expiry_date along with year , the values should get repeated for the particular year based on yearly value of contract period.
[\code]