Hi, I have these tables: 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: *select price_per_occupation.price, product_price_period.price_period, occupation_type.name <http://occupation_type.name>* *from price_per_occupation inner join product_price_period on product_price_period.id <http://product_price_period.id>= price_per_occupation.product_price_period_id* *inner join occupation_type on occupation_type.id <http://occupation_type.id> = price_per_occupation.occupation_type* *inner join product on product.id <http://product.id> = product_price_period.product_id* *where product_price_period.price_period @> '[2018-07-22, 2018-07-23]'* *and occupation_type.id <http://occupation_type.id> = 1* 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?
