Want more features on Pastebin? Sign Up, it's FREE!
Guest

monster query

By: a guest on Nov 14th, 2012  |  syntax: MySQL  |  size: 4.81 KB  |  views: 43  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  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
clone this paste RAW Paste Data