Find the eligible days of a customer's invoice based on its discount history

Here is the below invoice and having corresponding datefrom and dateto

Here the discount history

Now I need a sql query logic to find out number of days the invoiceid on the first table is eligible, based on the discount history table.

calculation as follows:
invoiceid 229 datefrom and dateto breakdown
--> 01-01-23 to 10-01-2023 = 10 days eligible
and 19-01-23 to 27-01-2023 = 9 days eligible

so total eligible days of the discount = 19 days

You really shouldn't post screen shots as people that want to help would prefer not to enter your data too. In the future please provide the necessary data where others can just cut and paste it into SQLPLUS

Having said that, I believe data should mean that the account is eligible from 2022-12-27 until 2023-01-09; on 2023-01-10 the account becomes ineligible.

Here is something you can work with and adjust as needed.

CREATE TABLE invoices(customer_id, invoice_id, date_from, date_to) AS
SELECT 123, 229,DATE '2023-01-01', DATE '2023-01-30' FROM DUAL 


CREATE TABLE discount_history(customer_id, discount_date, discount_status) AS
SELECT 123, DATE '2022-12-27', 'ELIGIBLE' FROM DUAL UNION ALL 
SELECT 123, DATE '2023-01-10', 'INELIGIBLE' FROM DUAL UNION ALL 
SELECT 123, DATE '2023-01-19', 'ELIGIBLE' FROM DUAL UNION ALL 
SELECT 123, DATE '2023-01-27', 'INELIGIBLE' FROM DUAL

with
  prep (customer_id, discount_date, discount_status, discount_end_date) as (
    select customer_id, discount_date, discount_status, 
           lead(discount_date, 1, date '2999-12-31')
                over (partition by customer_id order by discount_date) - 1
    from   discount_history
  )
select i.customer_id,
       sum(least(i.date_to, p.discount_end_date) - greatest(i.date_from, p.discount_date) + 1)
         as discount_days
 from   invoices i join prep p
         on    i.customer_id = p.customer_id
           and p.discount_status = 'ELIGIBLE'
           and i.date_from <= p.discount_end_date 
           and i.date_to   >= p.discount_date
group   by i.customer_id
;


customer_id  discount_days
123                  17

If you need to take into account possible data quality issues like several identical discount_status consecutive:

with merged_history(customer_id, start_dat, end_dat) as (
	select * from discount_history
	match_recognize (
		partition by customer_id
		order by discount_date,  discount_status
		measures first(eligible.discount_date) as start_eligible, 
			first(ineligible.discount_date) as end_inineligible
		pattern( eligible+ ineligible+ )
		define
			eligible as discount_status = 'ELIGIBLE',
			ineligible as discount_status = 'INELIGIBLE'
	)
)
select inv.customer_id,
	sum(least(inv.date_to, hist.end_dat) - greatest(inv.date_from, hist.start_dat)) as discount_days
from invoices inv
join merged_history hist on inv.customer_id = hist.customer_id;



123	17