price_per_occupation: id (integer), product_price_period_id(integer), occupation_type(integer), price (numeric)
product_price_period; id(integer), product_id(integer), is_monday(boolean), is_tuesday(boolean), is_wednesday(boolean), is_thursday(boolean), is_friday(boolean), is_saturday(boolean), is_sunday(boolean), price_period(daterange)
occupation_type: id(integer), name(varchar)
product: id(integer), name(varchar)
I run this query:
This query returns all the products with an occupation_type = 1 and have the period_price between 2018-07-22 and 2018-07-23.
However I need to verify if the boolean values verified.
E.g if is_monday = true the date corresponding to Monday will be eliminated from the period.
if is_sunday = true and is_friday = true the dates corresponding to Sunday and Friday will be eliminated from the period interval.
If I will choose all the products in the interval [2018-04-07,2018-04-14] and is_monday = true and is_thursday= true
the date of monday is 09/04/2018 and date of friday is 13/04/2018.
I have to get all products contained in [2018-04-07,2018-04-08] U [2018-04-10, 2018-04-12] U [2018-04-14, 2018-04-14]
In order to get the date of truthy columns I should execute a function which contains a query like that:
select *
from generate_series(date '2018-04-07', date '2018-04-14', interval '1 day') the day
where extract ('dow', the_day) = 1
I'm face many problems with this schema;
I should verify the boolean values.
I should extract the corresponding dates based to the values.
I decompose the period into different periods then run the query to fetch the products.
How can I enhance the query? or is there any proposition for the schema table to enhance it?
