Skip to content

@patrickmaciel /query.sql secret
Created

Embed URL

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
Super query. Not running on CakePHP
select
split_num
,sequence_num
,order_num
,drink_num
,SUM(quantity) as quantity
,price
,original_price
,custom_price
from (
select
0 as split_num
,odk.sequence_num
,odk.order_num
,odk.drink_num
,odk.quantity - coalesce(odpro.quantity,0) as quantity
,odk.price
,odk.original_price
,prodk.custom_price as custom_price
,'true'::text as pass_filter
from
checks cks
inner join
orders ods
on
ods.check_num = cks.check_num
inner join
order_drinks odk
on
odk.order_num = ods.order_num
inner join
promotions_places propla
on
propla.promo_num = 6
and
propla.place = ods.place
inner join
promotions_drinks prodk
on
prodk.promo_num = 6
and
prodk.drink_num = odk.drink_num
inner join
scheduled_promo prosch
on
prosch.promo_num = 6
and
(
(
(prosch.date_start <= ods.date::date and prosch.date_end >= ods.date::date)
or
(prosch.date_start is null and prosch.date_end is null)
)
and
(
(prosch.dow_start <= EXTRACT(DOW from ods.date) and prosch.dow_end >= EXTRACT(DOW from ods.date))
or
(prosch.dow_start is null and prosch.dow_end is null)
)
and
(
(prosch.shift = 'C' and prosch.time_start <= ods.date::time and prosch.time_end >= ods.date::time)
or
(prosch.shift = 'B' and prosch.time_start is null and prosch.time_end is null)
or
(prosch.shift = ods.shift and prosch.time_start is null and prosch.time_end is null)
)
)
left join
orders_drinks_promotions odpro
on
odpro.sequence_num = odk.sequence_num
and
odpro.order_num = odk.order_num
and
odpro.drink_num = odk.drink_num
) res
where pass_filter = 'true'
group by split_num,sequence_num,order_num,drink_num,price,original_price,custom_price
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.