Calculate (n) workdays from a given date, using a calendar table

Question

As shown on below image, I have two tables;

• Customer : with about 4 million records; and 3 columns

[Customer] : Customer ID

[Date] : Creation date

[Num_Days] : Number of working days to calculate the [next_wkday] in the query result.

• Calendar : with all calendar days; and 3 columns

[ref_date] : dates

[civil_util] : when (1) -> workday; when (0) -> non working day

[year]

I need to get the query_result with the three columns of Customer Table and a calculated date [next_wkday], representing the number of working days [Num_Days] after each customer Creation date, jumping the zeros (non working days) in the [civil_util] column.

I've created the query below to calculate the [next_wkday] using the Lead() function. But is not a solution because the offset parameter must be a constant, and we need to use the [Num_Days] value for each Creation Date:

select *,
            Lead (to_date(ref_date),5) OVER (ORDER BY to_date(ref_date)) AS next_wkday,
            datediff(Lead (to_date(ref_date),5) OVER (ORDER BY 
 to_date(ref_date)),ref_date) as days_diff
        from calendar
        where
            ref_date >= to_date(now())
            and civil_util = 1
            limit 1

Basicaly I need to solve two problems: 1 - Must be a query to perform the calculation Because I don't have the profile to create functions in the database.

2 - I need to design a query that achive the Query result, shown in the image, that will able to join both, the Customer table and the calculation query.

So, I need to find another solution. And that solution must work in two engines:

1. Impala version : impalad version 2.12.0-cdh5.16.2
2. Oracle 11g

I need to reinforce, for performance puposes, that the Customer table has 4 billion records approximatly.

Can anyone help please? My best regards

Answer 1

On Oracle, you could do this using the following query:

with       cal as (select ref_date from calendar where civil_util = 1),
     cust_dets as (select cust.customer_id,
                          cust.create_date,
                          cust.num_days,
                          cal.ref_date,
                          row_number() over (partition by cust.customer_id order by cal.ref_date) rn
                   from   customer cust
                          inner join cal on cust.create_date < cal.ref_date)
select customer_id,
       create_date,
       num_days,
       ref_date next_wkday
from   cust_dets
where  rn = num_days;

See this db<>fiddle for details based on your sample data (N.B. I'm not sure how you came up with a value of 4th October for customer N3; my query brings back 6th October. Was that a mistake on your part?)

The above query works by first joining the calendar table to the customer table such that the rows from the calendar table are a) working days and b) the ref_date is greater than the customer's create_date. This is assuming you want to exclude the initial date from the num_days calculation (i.e. 1st Oct + 3 = 4th Oct, not 3rd Oct or, in other words, the 3 days from 1st Oct are 2nd, 3rd and 4th of Oct, not 1st, 2nd, 3rd of Oct) - if you want to include the initial day in the calculation, you would make the join "greater than or equal to" instead of just "greater than".

Then we assign a row number to the resultant rows (this is assuming that customer_id is a unique column in the customer table; if it's not, you would need to amend the partition by clause to include all the columns that indicate uniqueness), and finally you filter the result to only return the row number that corresponds to the num_days value.

In future, when you're supplying sample data for your question, please, please add it as text and not a picture. Not all of use have access to see the image because of firewall restrictions, plus it's less than ideal that you expect us to help you by making us type out your data.