SHARE
TWEET

monster query

a guest Nov 14th, 2012 76 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. EXPLAIN  SELECT
  2.   sales_date
  3.   ,  merchant
  4.   , merchant_name
  5.   , package_class AS package__class
  6.   , rate_type AS rate__type
  7.   , package_desc AS package__desc
  8.   , SUM(order_grouped_shipping) + SUM(ticket_shipping) AS net_shipping
  9.   , SUM(ticket_fee) AS fee_amount
  10.   , SUM(order_grouped_cart_adjustment) AS net_cadj
  11.   , (
  12.     SUM(order_grouped_shipping) + SUM(ticket_shipping) + SUM(ticket_fee) + SUM(order_grouped_fee) + SUM(order_grouped_cart_adjustment) + SUM(ticket_tax) + SUM(ticket_retail)
  13.   ) AS order_total
  14.   , SUM(ticket_count) AS ticket_count
  15.   , SUM(ticket_tax) AS tax_amount
  16.   , SUM(ticket_retail) AS retail
  17.   , SUM(order_grouped_fee) AS refund_adjustment
  18. FROM
  19.   (
  20. SELECT
  21.   DATE_FORMAT(o.local_order_date,'%m-%d-%Y')AS sales_date
  22.   , merchant
  23.   , merchant_name
  24.   , package_class
  25.   , rate_type
  26.   , package_desc
  27.   , oi.master_ticket_id
  28.   , oi.ticket_id
  29.   , SUM(
  30.     IF(
  31.       oi.type = 'T'
  32.       , IF(oi.action = 'R', - 1, 1)
  33.       , 0
  34.     )
  35.   ) AS ticket_count
  36.   , SUM(
  37.     IF(
  38.       oi.type = 'S'
  39.       AND ISNULL(oi.ticket_id)
  40.       , oi.retail_amount
  41.       , 0
  42.     )
  43.   ) * (
  44.     order_stats.package_tickets / order_stats.order_tickets
  45.   ) AS order_grouped_shipping
  46.   , SUM(
  47.     IF(
  48.       oi.type = 'S'
  49.       AND NOT ISNULL(oi.ticket_id)
  50.       , oi.retail_amount
  51.       , 0
  52.     )
  53.   ) AS ticket_shipping
  54.   , SUM(
  55.     IF(
  56.       oi.type = 'F'
  57.       AND ISNULL(oi.ticket_id)
  58.       , oi.retail_amount
  59.       , 0
  60.     )
  61.   ) * (
  62.     order_stats.package_tickets / order_stats.order_tickets
  63.   ) AS order_grouped_fee
  64.   , SUM(
  65.     IF(
  66.       oi.type = 'F'
  67.       AND NOT ISNULL(oi.ticket_id)
  68.       , oi.retail_amount
  69.       , 0
  70.     )
  71.   ) AS ticket_fee
  72.   , 0 * SUM(
  73.     IF(
  74.       oi.type = 'A'
  75.       AND ISNULL(oi.ticket_id)
  76.       , oi.retail_amount
  77.       , 0
  78.     )
  79.   ) * (
  80.     order_stats.package_tickets / order_stats.order_tickets
  81.   ) AS order_grouped_cart_adjustment
  82.   , 0 * SUM(
  83.     IF(
  84.       oi.type = 'A'
  85.       AND NOT ISNULL(oi.ticket_id)
  86.       , oi.retail_amount
  87.       , 0
  88.     )
  89.   ) AS ticket_cart_adjustment
  90.   , SUM(
  91.     IF(
  92.       oi.type = 'T'
  93.       AND NOT ISNULL(oi.ticket_id)
  94.       , th.tax_amount * IF(oi.action = 'R', - 1, 1)
  95.       , 0
  96.     )
  97.   ) AS ticket_tax
  98.   , SUM(
  99.     IF(
  100.       oi.type = 'T'
  101.       , th.retail_amount * IF(oi.action = 'R', - 1, 1)
  102.       , 0
  103.     )
  104.   ) AS ticket_retail
  105. FROM
  106.   order_items oi
  107.   INNER JOIN ticket_order_header o
  108.     ON oi.master_ticket_id = o.master_ticket_id
  109.   inner join merchant m1 on o.merchant_id = m1.merchant_id
  110.   LEFT JOIN ticket_header th
  111.     ON (oi.ticket_id = th.ticket_id OR oi.type = 'A')
  112.     AND oi.master_ticket_id = th.master_ticket_id
  113.   INNER JOIN
  114.     (SELECT
  115.       o2.master_ticket_id
  116.         , th2.ticket_id
  117.       , IF(
  118.         get_ticket_count_by_order (o2.master_ticket_id) = 0
  119.         , COUNT(DISTINCT th2.ticket_id)
  120.         , COUNT(
  121.           DISTINCT IF(
  122.             EXISTS
  123.             (SELECT
  124.               *
  125.             FROM
  126.               merchant_package_characs mpc
  127.             WHERE mpc.merchant_id = th2.merchant_id
  128.               AND mpc.package_id = th2.package_id
  129.               AND mpc.name = 'package_type'
  130.               AND mpc.value = 'Merchandise')
  131.             , NULL
  132.             , th2.ticket_id
  133.           )
  134.         )
  135.       ) AS package_tickets
  136.       , IF(
  137.         get_ticket_count_by_order (o2.master_ticket_id) = 0
  138.         , COUNT(DISTINCT th2.ticket_id)
  139.         , get_ticket_count_by_order (th2.master_ticket_id)
  140.       ) AS order_tickets
  141.       , o2.merchant_id AS merchant
  142.       , m.name AS merchant_name
  143.       , package_class
  144.       , customer_type_name AS rate_type
  145.       , package_desc
  146.     FROM
  147.       ticket_order_header o2
  148.       INNER JOIN ticket_header th2
  149.         ON o2.master_ticket_id = th2.master_ticket_id
  150.       INNER JOIN package_header
  151.         ON th2.package_id = package_header.package_id
  152.       INNER JOIN customer_type
  153.         ON th2.customer_type = customer_type.customer_type
  154.         INNER JOIN merchant m ON o2.merchant_id = m.merchant_id
  155.     WHERE (o2.merchant_id IN (6234) or m.parent_econsignment_merchant_id IN (0) )
  156.        AND order_date >= '2012-01-01'
  157.       AND order_date < DATE_ADD('2012-10-31', INTERVAL 1 DAY)
  158.     GROUP BY o2.master_ticket_id, th2.ticket_id) order_stats
  159.       ON order_stats.master_ticket_id = o.master_ticket_id
  160.       AND (
  161.         (
  162.           ISNULL(order_stats.ticket_id)
  163.           AND ISNULL(th.ticket_id)
  164.         )
  165.         OR order_stats.ticket_id = th.ticket_id
  166.       )
  167.   WHERE (o.merchant_id IN (6234) or m1.parent_econsignment_merchant_id IN (0))
  168.   AND o.local_order_date >= '2012-01-01'
  169.   AND o.local_order_date < DATE_ADD('2012-10-31', INTERVAL 1 DAY)
  170. GROUP BY oi.master_ticket_id
  171.   , oi.ticket_id
  172.   ) AS main
  173.   GROUP BY main.merchant
  174.   , main.package_class
  175.   , main.rate_type
  176.   , main.package_desc
  177.  LIMIT 0, 1500
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top