Hello Experts,
I have one strange problem of finding the number of months in current year based on values from table which has from date and to date, this is for calculating invoices during that particular period.
[code]
create table periodic_invoice (INV_NO VARCHAR2(6),FROM_DT DATE ,TO_DT DATE);
INSERT INTO PERIODIC_INVOICE(INV_NO,FROM_DT,TO_DT) VALUES ('1', TO_DATE('01/01/2018','DD/MM/RRRR'),TO_DATE('31/12/2018','DD/MM/RRRR'));
INSERT INTO PERIODIC_INVOICE(INV_NO,FROM_DT,TO_DT) VALUES ('2', TO_DATE('01/04/2018','DD/MM/RRRR'),TO_DATE('31/12/2018','DD/MM/RRRR'));
INSERT INTO PERIODIC_INVOICE(INV_NO,FROM_DT,TO_DT) VALUES ('3', TO_DATE('01/05/2017','DD/MM/RRRR'),TO_DATE('31/05/2018','DD/MM/RRRR'));
INSERT INTO PERIODIC_INVOICE(INV_NO,FROM_DT,TO_DT) VALUES ('4', TO_DATE('01/06/2018','DD/MM/RRRR'),TO_DATE('31/05/2019','DD/MM/RRRR'));
--desired output is
for first row it must return 12 months as it is starting from JAN 2018 till DEC 2018
for second row it must return 9 months as it should consider the months of 2018 starting from April 2018 till December 2018.
for third row it must not take any month of year 2017 and it should return 5 months as starting from JAN 2018 till May 2018.
for fourth row it must return 7 months as starting from June 2018 till December 2018 only.
[/code]