CODEWITHSUNDEEP
sqlgoogle-bigquery
helloworld1999
helloworld1999

Reputation: 33

Count number of holidays between 2 dates

I have a table called public_holidays with a single column called date (Date format). I have a second table called employees with employee_name (String), starting_date (Date) and ending_date (Date).

employees

| employee_name | starting_date | ending_date  | 
| joe           |  2018-01-05   | 2020-04-02   |
| adam          |  2021-06-25   | 2021-12-26   |

...

public_holidays

| date         |
| 2023-01-01   |         
| 2023-12-24   |

...

What I am trying to achieve is to achieve is to calculate for each employee the total number of days between starting_date and ending_date - every days of public holidays between those 2 dates.

Let's say starting date is 01-01-2023 and ending date is 20-01-2023 and on my table public_holidays there is 14-01-2023 and 18-01-2023, the total number of working days should be 20-2 = 18 for this employee. Seems it's quite tricky and I must use a GENERATE_DATE_ARRAY to create a temp array that is going to contains all the dates between starting_date and ending_date but after that not sure what would be the appropriate way to use it since I need a different array for every employee.

Upvotes: 1

Views: 404

Answers (2)

jepozdemir
jepozdemir

Reputation: 509

You need a query like below :

SELECT *, TotalDays-Holidays 'WorkDays' from (
SELECT [employee_name]
      ,[starting_date]
      ,[ending_date]
      ,DATEDIFF(day, starting_date, ending_date) +1 AS 'TotalDays'
      ,(SELECT COUNT(p.date) from public_holidays p where p.date < e.ending_date AND p.date >= e.starting_date) 'Holidays'
  FROM [StackOverflowTests].[dbo].[employees] e) t

OUTPUT :

enter image description here

Upvotes: 1

Jakobee
Jakobee

Reputation: 3

I might be missing something but can't you use the

DATEDIFF(day, ending_date, starting_date) AS DateDiff

And the JOIN the public holiday table on date

WHERE PublicHoliday.date < ending_date AND PublicHoliday.date >= starting_day

To make it DateDiff - COUNT (publicHoliday.date)

Upvotes: 0

Related Questions