Get an end date, (n) workdays from a given date, using a calendar table

@s300570 Welcome to the Cloudera Community!

To help you get the best possible solution, I have tagged our experts @bbreak @cravani  who may be able to assist you further.

Please keep us updated on your post, and we hope you find a satisfactory solution to your query.

@JoseManuel @Shmoo Any insights here?

@s300570,

If I understood your requirement correctly, you might try something like:

WITH WorkingDaysCTE AS (
  SELECT
    ref_date,
    ROW_NUMBER() OVER (ORDER BY ref_date) AS row_num
  FROM
    cd_estruturais.calendario_datas
  WHERE
    ref_date >= CURRENT_DATE -- You can adjust the starting date as needed
    AND civil_util = 1
)

SELECT
  c1.*,
  w1.ref_date AS next_wkday,
  DATEDIFF(w1.ref_date, c1.ref_date) AS Num_Days
FROM
  cd_estruturais.calendario_datas c1
JOIN
  WorkingDaysCTE w1 ON c1.ref_date <= w1.ref_date
                    AND w1.row_num = (
                      SELECT MIN(row_num)
                      FROM WorkingDaysCTE w2
                      WHERE w2.row_num > w1.row_num
                    )
WHERE
  c1.ref_date >= CURRENT_DATE
  AND c1.civil_util = 1
LIMIT 1;

Now, just as info, as I no longer have access to an Impala/Hive system, the above query was written as a standard SQL, so you might want to double check the syntax.

In terms of explanations:

• The common table expression (CTE) named WorkingDaysCTE is used to filter the dates with civil_util = 1 and assign a row number to each date based on their order.
• The main query then joins the calendario_datas table with the CTE on the condition that the date in the calendar table is less than or equal to the date in the CTE.
• The subquery in the SELECT clause is used to find the minimum row number greater than the current row number in the CTE. This helps in getting the next working day.
• The DATEDIFF function is used to calculate the number of days between the ref_date and the next_wkday.

@s300570 Has the reply helped resolve your issue? If so, please mark the appropriate reply as the solution, as it will make it easier for others to find the answer in the future. Thanks.