[SQL] How to exclude holydays of two different countries from date range count in SQL

I have a table looking like this...

deals

id sub_id deal_start deal_end country_A country_B
10 1 2024-10-21 2024-10-25 USA RUS
10 2 2024-10-21 2024-10-25 USA CHN
10 3 2024-10-21 2024-10-24 RUS USA
11 1 2024-10-21 2024-10-25 CHN RUS
11 2 2024-10-21 2024-10-23 CHN USA

I need to calculate the term of the deal for each row, but excluding weekends and holidays of each of two countries.

For this purpose, I have a table of weekends and holidays for all countries on all dates looking like this.

holydays

1 - business day

2 - holyday or weekend

date country day_type
2024-10-21 RUS 2
2024-10-21 CHN 1
2024-10-21 USA 1
2024-10-22 RUS 1
2024-10-22 CHN 2
2024-10-22 USA 1
2024-10-23 RUS 1
2024-10-23 CHN 1
2024-10-23 USA 2
2024-10-24 RUS 2
2024-10-24 CHN 1
2024-10-24 USA 2

I'm using SQL Impala dialect and tried query like this, but without success. In addition, this approach does not take into account overlaping dates. Finally, I can't use UDFs at my work.

SELECT id,
sub_id,
deal_start,
deal_end,
country_A,
country_B,
DATEDIFF(d.deal_end, d.deal_start) AS dirty_term,
COUNT(hA.date) AS country_A_hd,
COUNT(hB.date) AS country_B_hd,
(DATEDIFF(d.deal_end, d.deal_start) - GREATEST(COUNT(hA.date), COUNT(hA.date))
FROM deals AS d
LEFT JOIN holydays AS hA ON hA.country=d.country_A AND hA.date BETWEEN d.deal_start AND d.deal_end AND hA.day_type = 2
LEFT JOIN holydays AS hB ON hB.country=d.country_B AND hB.date BETWEEN d.deal_start AND d.deal_end AND hB.day_type = 2
GROUP BY id,
sub_id,
deal_start,
deal_end,
country_A,
country_B


Ideally I need to get this result:

id sub_id deal_start deal_end country_A country_B datediff total_AB_hd real_term
10 1 2024-10-21 2024-10-25 USA RUS 4 3 1
10 2 2024-10-21 2024-10-25 USA CHN 4 3 1
10 3 2024-10-21 2024-10-24 RUS USA 3 2 1
11 1 2024-10-21 2024-10-25 CHN RUS 4 3 1
11 2 2024-10-21 2024-10-23 CHN USA 2 1 1

Using following logic

total_AB_hd

? ? ? ?
2024-10-21 2024-10-22 2024-10-23 2024-10-24
2 2 1 2

country_A

? ? ? ?
1 2 1 2

country_B

? ? ? ?
2 1 1 2

Please help. And big thanks for your time.

Continue reading...