Need to count how many values of a list are between two other given date values.

[rohan146]

Hi gurus, this is my problem:

I need to count how many holidays are between two dates, I have a list of events with a start_date and end_date and another list with holidays. Now the trick part is there are multiple rows with each row having different start and end date.

Resuming, for every record in the events list, I need to see how many holidays are between start_date and end_date and put this count as another column in the record.

Here is the sample input:-

Record	Start_Date	End_Date
R1	1-Jun-21	10-Jun-21
R2	5-Jun-21	8-Jun-21
R3	15-Jun-21	22-Jun-21
R4	10-Jun-21	18-Jun-21
R5	26-Jun-21	30-Jun-21

Heres the Holiday List:-

Holidays

3-Jun-21

7-Jun-21

15-Jun-21

27-Jun-21

29-Jun-21

Heres the desired output result:-

Record	Start_Date	End_Date	No. Of Holidays
R1	1-Jun-21	10-Jun-21	2
R2	5-Jun-21	8-Jun-21	1
R3	15-Jun-21	22-Jun-21	1
R4	10-Jun-21	18-Jun-21	1
R5	26-Jun-21	30-Jun-21	2

Also attaching excel sheets.

[jamielaird · Accepted solution]

Hey @rohan146 ,

Here's a solution based on appending the list of holidays to each date, counting 1 for each holiday date within the range, and summing all the counts:

[OllieClarke · Accepted solution]

Hi @rohan146 

Here's my solution:

[apathetichell · Accepted solution]

 

convert to dates, generate rows, join and summarize.