Suggest a feature
×

Welcome to TagMyCode

Please login or create account to add a snippet.
0
0
 
0
Language: SQL
Posted by: Károly Szalai
Added: May 23, 2021 7:17 PM
Views: 188
  1. SELECT x.plan_name, x.plan_date_from, x.plan_date_to,
  2.        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,
  3.         ROUND(x.vocabulary_incrementum * x.price_of_one_wordpair_in_cents * x.max_count_of_wordpairs_in_vocabulary, 2) full_price_in_cents,
  4.        x.discount_percent,
  5.        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
  6.  
  7. FROM (
  8.          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,
  9.                 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,
  10.                 bp.base_discount_percent, bp.base_based_on,
  11.                 COALESCE(pp.max_vocabulary - bp.base_max_vocabulary, 0) vocabulary_incrementum,
  12.                 COALESCE(pp.max_count_of_wordpairs_in_vocabulary * (pp.max_vocabulary - bp.base_max_vocabulary), 0) wordpair_incrementum
  13.          FROM
  14.              (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,
  15.                     s.discount_percent, s.based_on
  16.               FROM trs_subscription_db.pricing_plan P
  17.                        LEFT JOIN trs_subscription_db.pricing_plan_setup S ON p.id = s.plan_id) PP
  18.  
  19.                  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,
  20.                                       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,
  21.                                       s.discount_percent base_discount_percent, s.based_on base_based_on
  22.                                FROM trs_subscription_db.pricing_plan P
  23.                                         LEFT JOIN trs_subscription_db.pricing_plan_setup S ON p.id = s.plan_id) BP ON pp.based_on = bp.base_plan_id
  24.          ORDER BY plan_id, plan_date_from, plan_date_to) X
  25. WHERE UNIX_TIMESTAMP() BETWEEN plan_date_from AND plan_date_to
  26. AND UNIX_TIMESTAMP() BETWEEN setup_date_from AND setup_date_to
  27. ORDER BY end_price_in_cents
  28.  
  29.