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 cd_estruturais.calendario_datas
        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

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