secret
Created

Super query. Not running on CakePHP

  • Download Gist
query.sql
SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80
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

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.