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),

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>=

*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> =

*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?

Reply via email to