Crm List

SQL-Vorlage aus der reybex-Standardbibliothek – für BAS23-Exportjobs und Ad-hoc-Tests.

← Alle Beispiel-SQLs

Abfrage

SELECT
CASE WHEN (contactTypeTranslation.name is null) THEN contactType.name ELSE contactTypeTranslation.name  END AS contactTypeTranslation_name,
CASE WHEN customer.is_gross_price = true THEN false ELSE true END AS customer_isPerson,
customerGroup.name,
countryTranslations.name as country_name,
country.code,
supportLine.id,
supportLine.name,
paymcond.name,
concat(salesPerson.firstName,' ', salesPerson.surname) as salesPerson_firstname,
(CASE WHEN user.firstname is null THEN user.surname ELSE concat(user.firstname,' ',user.surname) END) as user_surname,
customerType.name,
status.name,
CASE WHEN (profitCenterTranslation.name IS NULL) THEN profitCenter.name ELSE profitCenterTranslation.name END AS profitCenter_name,
mainContact.name,
country.id,
rating.name,
commissionWarehouse.name,
customer.abc_category
FROM customer AS customer
LEFT JOIN ltt_lead_status as status on status.id = customer.lead_status_id
LEFT JOIN ltt_customer_type as customerType on customerType.id = customer.customer_type_id
LEFT JOIN user as user on user.id = customer.key_account_manager_id
LEFT JOIN lt_contact_type as contactType on contactType.id = customer.contact_type_id
LEFT JOIN lt_country AS country on country.id = customer.country_id
LEFT JOIN lt_country_trans AS countryTranslations on countryTranslations.country_id = country.id and countryTranslations.language = 'en'
LEFT JOIN customer AS mainContact on mainContact.id = customer.main_contact_id
LEFT JOIN ltt_customer_group AS customerGroup on customerGroup.id = customer.customer_group_id
LEFT JOIN ltt_paymcond AS paymcond on paymcond.id = customer.paymcond_id
LEFT JOIN customer_sales_person AS customerSalesPerson on customerSalesPerson.customer_id = customer.id AND customerSalesPerson.is_main = 1
LEFT JOIN user AS salesPerson on salesPerson.id = customerSalesPerson.sales_person_id
LEFT JOIN lt_contact_type_trans AS contactTypeTranslation on contactTypeTranslation.contact_type_id = contactType.id and contactTypeTranslation.language = 'en'
LEFT JOIN ltt_profit_center AS profitCenter on profitCenter.id = customer.profit_center_id
LEFT JOIN lt_rating_type AS rating on rating.id = customer.rating_id
LEFT JOIN ltt_profit_center_trans AS profitCenterTranslation on profitCenterTranslation.profit_center_id = profitCenter.id AND profitCenterTranslation.language = 'en'
LEFT JOIN ltt_warehouse AS commissionWarehouse on commissionWarehouse.id = customer.commission_warehouse_id
LEFT JOIN support_line AS supportLine on supportLine.id = customer.support_line_id
WHERE customer.tenant_id = :tenantId AND (customerSalesPerson.id is null or customerSalesPerson.is_main = 1)
AND customer.is_template = 0 AND customer.is_delete = 0
GROUP BY customer.id ORDER BY customer.id DESC;