I am trying to get a report of our New Customers with First Shipment based on specific Order Types IF the Shipment is between two dates. Can move the condition to the LEFT JOIN, but don't know how to ensure that it's the FIRST instance in the sales_shipment table AND matches on conditions. If there's a sales_shipment.created_at record BEFORE my dates AND inside of my dates - the record should NOT be returned.
Here's my example - Let's say I want to return New Registered Customers since 1/1/2022 that have an Order that Shipped Last Week, but I want only the EARLIEST Shipment Record that matches (in the event that there could be multiple shipments) from my BETWEEN
sales_shipment.created_at
dates. If there is a Shipment BEFORE my BETWEEN Dates, it shouldn't be returned in this report.
I've removed some table-specific data to make them more general for this example (so you can ignore if some of my "company.field_name" in the report are FROM "company" - they aren't actually in my real tables.
Haven't been able to get
MIN(sales_shipment.created_at)
to work.
This is my SQL Query that returns Shipments BEFORE my sales shipment created at BETWEEN date ranges.
SELECT DISTINCT company.entity_id, company_name, company.status, customer_entity_datetime.value AS "Creation Date", legal_name AS 'Company Legal Name', company_email, vat_tax_id, reseller_id, comment, company.telephone AS 'Phone Number', company.country_id AS 'Country', directory_country_region.default_name AS 'State/Province', postcode AS 'Zip', city, company.customer_group_id AS 'Group/Shared Catalog', street AS 'Street Address', super_user_id, customer_entity.firstname, customer_entity.lastname, customer_entity_varchar.value AS "Job Title", customer_entity.email, customer_industry.industry, customer_industry.segment, customer_industry.category, company.accountmanager_id, accountmanager.name, company.accountcoordinator_id, account_coordinator.name, company_credit.currency_code AS 'Credit Currency', company_credit.balance AS 'Outstanding Balance', company_credit.credit_limit, sales_order.created_at AS 'First Order Date', sales_order.increment_id, sales_order.quote_id, quote.order_type, sales_shipment.created_at as 'Ship Date'
FROM company
LEFT JOIN company
ON company.company_id = company.entity_id
LEFT JOIN customer_entity
ON customer_entity.entity_id = company.super_user_id
LEFT JOIN company_credit
ON company_credit.company_id = company.entity_id
LEFT JOIN company_payment
ON company_payment.company_id = company.entity_id
LEFT JOIN customer_industry
ON customer_industry.company_id = company.entity_id
LEFT JOIN directory_country_region
ON directory_country_region.region_id = company.region_id
LEFT JOIN accountmanager
ON accountmanager.accountmanager_id = company.accountmanager_id
LEFT JOIN account_coordinator
ON account_coordinator.id = company.accountcoordinator_id
LEFT JOIN customer_entity_datetime
ON customer_entity_datetime.entity_id = customer_entity.entity_id AND customer_entity_datetime.attribute_id = '495'
LEFT JOIN customer_entity_varchar
ON customer_entity_varchar.entity_id = customer_entity.entity_id AND customer_entity_varchar.attribute_id = '395'
LEFT JOIN sales_order
ON sales_order.customer_id = company.super_user_id
LEFT JOIN quote
ON quote.quote_id = sales_order.quote_id
LEFT JOIN sales_shipment
ON sales_shipment.customer_id = customer_entity.entity_id
WHERE customer_entity_datetime.value BETWEEN '2022-01-01' AND '2023-04-22'
AND sales_shipment.created_at BETWEEN '2023-04-16' AND '2023-04-22'
AND quote.order_type = 'order' OR 'stock' OR 'order_fulfillment'
ORDER BY company.company_name;
This is my query that limits my customer created at to the same range as the shipment, which means I could be losing customer in this report that have a first shipment from last week if their account was created before last week.
SELECT DISTINCT company.entity_id, company_name, company.status, customer_entity_datetime.value AS "Creation Date", legal_name AS 'Company Legal Name', company_email, vat_tax_id, reseller_id, comment, company.telephone AS 'Phone Number', company.country_id AS 'Country', directory_country_region.default_name AS 'State/Province', postcode AS 'Zip', city, company.customer_group_id AS 'Group/Shared Catalog', street AS 'Street Address', super_user_id, customer_entity.firstname, customer_entity.lastname, customer_entity_varchar.value AS "Job Title", customer_entity.email, customer_industry.industry, customer_industry.segment, customer_industry.category, company.accountmanager_id, accountmanager.name, company.accountcoordinator_id, account_coordinator.name, company_credit.currency_code AS 'Credit Currency', company_credit.balance AS 'Outstanding Balance', company_credit.credit_limit, sales_order.created_at AS 'First Order Date', sales_order.increment_id, sales_order.quote_id, quote.order_type, sales_shipment.created_at as 'Ship Date'
FROM company
LEFT JOIN company
ON company.company_id = company.entity_id
LEFT JOIN customer_entity
ON customer_entity.entity_id = company.super_user_id
LEFT JOIN company_credit
ON company_credit.company_id = company.entity_id
LEFT JOIN company_payment
ON company_payment.company_id = company.entity_id
LEFT JOIN customer_industry
ON customer_industry.company_id = company.entity_id
LEFT JOIN directory_country_region
ON directory_country_region.region_id = company.region_id
LEFT JOIN accountmanager
ON accountmanager.accountmanager_id = company.accountmanager_id
LEFT JOIN account_coordinator
ON account_coordinator.id = company.accountcoordinator_id
LEFT JOIN customer_entity_datetime
ON customer_entity_datetime.entity_id = customer_entity.entity_id AND customer_entity_datetime.attribute_id = '495'
LEFT JOIN customer_entity_varchar
ON customer_entity_varchar.entity_id = customer_entity.entity_id AND customer_entity_varchar.attribute_id = '395'
LEFT JOIN sales_order
ON sales_order.customer_id = company.super_user_id
LEFT JOIN quote
ON quote.quote_id = sales_order.quote_id
LEFT JOIN sales_shipment
ON sales_shipment.customer_id = customer_entity.entity_id
WHERE customer_entity_datetime.value BETWEEN '2023-04-16' AND '2023-04-22'
AND sales_shipment.created_at BETWEEN '2023-04-16' AND '2023-04-22'
AND quote.order_type = 'order' OR 'stock' OR 'order_fulfillment'
ORDER BY company.company_name;
