SELECT x.plan_name, x.plan_date_from, x.plan_date_to,
x.max_vocabulary, x.setup_date_from, x.setup_date_to, x.max_vocabulary, x.max_count_of_wordpairs_in_vocabulary, x.vocabulary_incrementum, x.wordpair_incrementum,
ROUND(x.vocabulary_incrementum * x.price_of_one_wordpair_in_cents * x.max_count_of_wordpairs_in_vocabulary, 2) full_price_in_cents,
x.discount_percent,
ROUND(x.vocabulary_incrementum * x.price_of_one_wordpair_in_cents * x.max_count_of_wordpairs_in_vocabulary * (100 - x.discount_percent) / 100, 0) end_price_in_cents
FROM (
SELECT pp.plan_id, pp.plan_name, pp.plan_date_from, pp.plan_date_to, pp.id, pp.setup_date_from, pp.setup_date_to, pp.max_vocabulary, pp.max_count_of_wordpairs_in_vocabulary, pp.price_of_one_wordpair_in_cents, pp.discount_percent, pp.based_on,
bp.base_plan_id, bp.base_plan_name, bp.base_plan_date_from, bp.base_plan_date_to, bp.base_id, bp.setup_base_date_from, bp.setup_base_date_to, bp.base_max_vocabulary, bp.base_max_count_of_wordpairs_in_vocabulary, bp.base_price_of_one_wordpair_in_cents,
bp.base_discount_percent, bp.base_based_on,
COALESCE(pp.max_vocabulary - bp.base_max_vocabulary, 0) vocabulary_incrementum,
COALESCE(pp.max_count_of_wordpairs_in_vocabulary * (pp.max_vocabulary - bp.base_max_vocabulary), 0) wordpair_incrementum
FROM
(SELECT p.id plan_id, p.name plan_name, p.date_from plan_date_from, p.date_to plan_date_to, s.id, s.date_from setup_date_from, s.date_to setup_date_to, s.max_vocabulary, s.max_count_of_wordpairs_in_vocabulary, s.price_of_one_wordpair_in_cents,
s.discount_percent, s.based_on
FROM trs_subscription_db.pricing_plan P
LEFT JOIN trs_subscription_db.pricing_plan_setup S ON p.id = s.plan_id) PP
LEFT JOIN (SELECT p.id base_plan_id, p.name base_plan_name, p.date_from base_plan_date_from, p.date_to base_plan_date_to, s.id base_id, s.date_from setup_base_date_from, s.date_to setup_base_date_to,
s.max_vocabulary base_max_vocabulary, s.max_count_of_wordpairs_in_vocabulary base_max_count_of_wordpairs_in_vocabulary, s.price_of_one_wordpair_in_cents base_price_of_one_wordpair_in_cents,
s.discount_percent base_discount_percent, s.based_on base_based_on
FROM trs_subscription_db.pricing_plan P
LEFT JOIN trs_subscription_db.pricing_plan_setup S ON p.id = s.plan_id) BP ON pp.based_on = bp.base_plan_id
ORDER BY plan_id, plan_date_from, plan_date_to) X
WHERE UNIX_TIMESTAMP() BETWEEN plan_date_from AND plan_date_to
AND UNIX_TIMESTAMP() BETWEEN setup_date_from AND setup_date_to
ORDER BY end_price_in_cents