amedinata of SQL
1/26/2017 - 2:42 PM

Listados de clientes con información sobre la promoción de bolsitas higiénicas de por vida: - Listado de clientes dentro de la promo: list

Listados de clientes con información sobre la promoción de bolsitas higiénicas de por vida:

  • Listado de clientes dentro de la promo: listado_clientes_con_bolsas_caca.sql
  • Listados de clientes que se han dado de baja: listado_clientes_dados_de_baja_bolsas_caca.sql
listado_clientes_dados_de_baja_bolsas_caca.sql
content_copyfile_download
SELECT
    c.customers_id, c.customers_email_address
FROM
    customers c
        INNER JOIN
    orders o ON o.customers_id = c.customers_id
        INNER JOIN
    discount_coupons_to_orders dc2o ON dc2o.orders_id = o.orders_id
WHERE
    coupons_id IN (SELECT
            CONCAT(dc.coupons_id, '_DISABLED')
        FROM
            discount_coupons dc
                INNER JOIN
            discount_coupons_automatic_gifts ag ON dc.coupons_id = ag.coupons_id
        WHERE
            CONVERT( ag.product_id , UNSIGNED) = 8912
                AND dc.coupons_discount_type = 'gift-automatic'
                AND (ag.date_end IS NULL
                OR ag.date_end >= NOW()))
GROUP BY c.customers_id
listado_clientes_con_bolsas_caca.sql
content_copyfile_download
SELECT
  c.customers_id, 
  c.customers_email_address
FROM
  customers c
  JOIN orders o ON o.customers_id = c.customers_id
  JOIN discount_coupons_to_orders dc2o ON dc2o.orders_id = o.orders_id
  JOIN discount_coupons dc ON dc.coupons_id = dc2o.coupons_id
  JOIN discount_coupons_automatic_gifts ag ON dc.coupons_id = ag.coupons_id
WHERE
  CONVERT(ag.product_id, UNSIGNED) = 8912 
  AND dc.coupons_discount_type = 'gift-automatic' 
  AND (ag.date_end IS NULL OR ag.date_end >= NOW()) -- mysql tiene una horas menos que php
GROUP BY
  c.customers_id