8 Replies Latest reply: Mar 19, 2014 10:29 AM by Barry Harmsen RSS

    How can I manipulate a variable date I set in the query on the report sheet

      Hello ,

      Please I have two variable dates in my query and when I run it it gives me a blank report even after I set the dates to a default. My question is please can I have like a text box to change these dates (Like a date range) from the report sheet.

       

       

      LET vtotal_date_from = '2013-11-30';

      LET vtotal_date_to = '2013-12-31';

       

      SELECT

      customer.entity_id AS customer_id,

      firstname.value AS firstname,

      lastname.value AS lastname,

      customer.`email` AS email,

      phone.value AS telephone,

      pu.created_at AS total_verify_date

      FROM customer_entity customer

      LEFT JOIN `customer_entity_varchar` firstname ON (firstname.entity_id = customer.entity_id AND firstname.attribute_id = 5)

      LEFT JOIN `customer_entity_varchar` lastname ON (lastname.entity_id = customer.entity_id AND lastname.attribute_id = 7)

      LEFT JOIN `customer_address_entity_varchar` phone ON (phone.entity_id = customer.entity_id AND phone.attribute_id = 31)

      INNER JOIN (SELECT customer_id , sales_flat_order_status_history.`created_at`,

      MIN(sales_flat_order_status_history.`entity_id`) AS entity_id

      FROM sales_flat_order

      LEFT JOIN sales_flat_order_status_history ON sales_flat_order_status_history.parent_id = sales_flat_order.entity_id

      WHERE ((sales_flat_order_status_history.status LIKE '%verified%' AND sales_flat_order_status_history.status NOT LIKE '%canceled%'

      AND sales_flat_order.created_at > '2013-11-01')

      OR (sales_flat_order.status LIKE '%complete%' AND sales_flat_order.created_at < '2013-11-01'))

      AND sales_flat_order_status_history.`created_at` >= $(vtotal_date_from)

      AND sales_flat_order_status_history.created_at <= $(vtotal_date_from)

      AND sales_flat_order.customer_id IS NOT NULL

      GROUP BY sales_flat_order_status_history.`parent_id`) AS pu ON pu.customer_id = customer.entity_id

       

       

      ;