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

Question

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

Hi - so what have you tried and what specific issue are you facing? Please update your question with the SQL you've managed to write so far (as editable text, not images) — NickW, Jul 27 '23 at 14:54
This task consists (*at least*) of three different tasks: build validity intervals for each discount status, overlap source interval with list of zero or more validity intervals for the given key, calculate number of days in the overlap. There are a lot of questions in the SO for every of this task: https://stackoverflow.com/q/72416218/2778710 or https://stackoverflow.com/q/58487031/2778710, https://stackoverflow.com/q/325933/2778710, https://stackoverflow.com/q/117962/2778710 — astentx, Jul 28 '23 at 09:07

score 0 · Accepted Answer · answered Jul 27 '23 at 16:11

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

This forum is made up of volunteers who try to help others by donating their time and expertise. Having said that, it would be nice to know if their solutions helped solve your issue. If it didn't please explain why and add more details. If it did, please upvote and accept an answer. — Beefstu, Jul 29 '23 at 15:25

p3consulting · Answer 2 · 2023-07-27T20:09:30.740

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

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

2 Answers2