Find if between dates having any date from a list of dates?

user_11752307 0
Viewed 66

I am trying to find if (select dates from public_holidays) exist in dates between start_date and end_date.

It will be look like this:

select
id, name, start_date, end_date, 
case when (public_holidays = true) then number - 1 else number end as find_real_number
from my_table

Sample Data:

ID Name Start_date End_Date Numbers
1  Mike  3/9/2020  4/9/2020   67
2  Rick  3/1/2020  3/6/2020   34
3  Simm  3/24/2020 3/28/2020   98
4  Lisa  3/27/2020  4/5/2020   103
5  Rosy  3/9/2020  4/9/2020   23

And some sample expected results:

ID Name Start_date End_Date Numbers
1  Mike  3/9/2020  4/9/2020   66
2  Rick  3/1/2020  3/6/2020   34
3  Simm  3/24/2020 3/28/2020   98
4  Lisa  3/27/2020  4/5/2020   102
5  Rosy  3/9/2020  4/9/2020   23

Because we assume the 1st of April is a public holiday, so number of row 1st and 4th got minus by 1.

And sample public holidays view I created:

Public_holidays    Dates
April fools     04/01/2020
Labour Day      05/01/2020
Random Day      07/24/2020

However, because I am building query on the Metabase, it does not allow me to create a table. All I did was create a view where has 2 columns that are 'Public Holidays' and 'Dates'

Anyone possibly could give me a suggestion of how to do this? Thanks.

postgresql
2 Answers

Try something like this:

SELECT id, name, start_date, end_date,
       numbers - ( SELECT COUNT(*)  FROM holidays
                   WHERE dates BETWEEN t.start_date AND t.end_date ) AS numbers
FROM my_table AS t

This assumes that your holidays are in a table/view named holidays. Also it counts the holidays between start and end dates and subtract it from numbers of my_table.

user_48991930

I think you want to check public holiday falls or not between start and end Date.

so you should compare dates like below:

select
id, name, start_date, end_date, 
case when ((CAST(start_date as date) < CAST(public_holiday_date as date) 
             and CAST(public_holiday_date as date) < CAST(end_date as date)) 
     then number - 1 else number end as find_real_number
from my_table

or 

select
id, name, start_date, end_date, 
case when CAST(public_holiday_date as date) 
          between (CAST(start_date as date) and CAST(end_date as date) 
     then number - 1 else number end as find_real_number
from my_table
user_92532970
Related