On 04/12/2018 05:59 PM, hmidi slim wrote:
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?
My 2 cents eliminate the is_* fields and create a single field:
dow_verified integer[]
Then assuming non-iso week day number Sunday(0) --> Saturday(6) in the
array enter the day of week(dow) numbers for verified days e.g.:
ARRAY[0, 1, 4]
for Sunday, Monday, Thursday.
Then to get the days that are not verified over a period something like:
WITH dp AS (
SELECT
extract('dow' FROM generate_series('04/01/18'::date,
'04/14/18'::date, '1 day'))
AS dow,
generate_series('04/01/18'::date, '04/14/18'::date, '1 day')
AS dt
)
SELECT
dp.*
FROM
dp
WHERE
dp.dow NOT IN (
(
SELECT
*
FROM
unnest(ARRAY [ 0, 1, 4 ]) AS dow_dt)
)
ORDER BY
dt;
dow | dt
-----+------------------------
2 | 2018-04-03 00:00:00-07
3 | 2018-04-04 00:00:00-07
5 | 2018-04-06 00:00:00-07
6 | 2018-04-07 00:00:00-07
2 | 2018-04-10 00:00:00-07
3 | 2018-04-11 00:00:00-07
5 | 2018-04-13 00:00:00-07
6 | 2018-04-14 00:00:00-07
(8 rows)
|
||
||
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
