Search code examples
sqloracledatelogiccalculation

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 enter image description here

Here the discount history

enter image description here

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

Solution

  • 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