Reports » Orders by Assigned User - Last 90 Days
http://crm.myserver.com/espocrm/#Report/view/5a4ceca9371e5f1e6 


****** Query without teams field in filter
|| 223011 | bn_espocrm | localhost          | bitnami_espocrm | Query   |    0 | Sending data | SELECT DATE_FORMAT(account.orderdate, '%Y-%m-%d') AS `DAY:orderdate`, TRIM(CONCAT(assignedUser.first_name, ' ', assignedUser.last_name)) AS `assignedUserName`, account.assigned_user_id AS `assignedUserId`, SUM(account.newvoicepotline) AS `SUM:newvoicepotline`, SUM(account.voicemigrationtovoip) AS `SUM:voicemigrationtovoip` FROM `account` LEFT JOIN `entity_email_address` AS `emailAddressesMiddle` ON account.id = emailAddressesMiddle.entity_id AND emailAddressesMiddle.deleted = '0' AND emailAddressesMiddle.primary = '1' AND emailAddressesMiddle.entity_type = 'Account' LEFT JOIN `email_address` AS `emailAddresses` ON emailAddresses.id = emailAddressesMiddle.email_address_id AND emailAddresses.deleted = '0' LEFT JOIN `entity_phone_number` AS `phoneNumbersMiddle` ON account.id = phoneNumbersMiddle.entity_id AND phoneNumbersMiddle.deleted = '0' AND phoneNumbersMiddle.primary = '1' AND phoneNumbersMiddle.entity_type = 'Account' LEFT JOIN `phone_number` AS `phoneNumbers` ON phoneNumbers.id = phoneNumbersMiddle.phone_number_id AND phoneNumbers.deleted = '0' LEFT JOIN `currency` AS `mrcCurrencyRate` ON mrcCurrencyRate.id = account.mrc_currency LEFT JOIN `currency` AS `ccmlmrcCurrencyRate` ON ccmlmrcCurrencyRate.id = account.ccmlmrc_currency LEFT JOIN `currency` AS `ccfflmrcCurrencyRate` ON ccfflmrcCurrencyRate.id = account.ccfflmrc_currency LEFT JOIN `currency` AS `ccblmrcCurrencyRate` ON ccblmrcCurrencyRate.id = account.ccblmrc_currency LEFT JOIN `currency` AS `ccimrcCurrencyRate` ON ccimrcCurrencyRate.id = account.ccimrc_currency LEFT JOIN `currency` AS `ccvmrcCurrencyRate` ON ccvmrcCurrencyRate.id = account.ccvmrc_currency LEFT JOIN `currency` AS `discountvideoCurrencyRate` ON discountvideoCurrencyRate.id = account.discountvideo_currency LEFT JOIN `currency` AS `discountvoiceCurrencyRate` ON discountvoiceCurrencyRate.id = account.discountvoice_currency LEFT JOIN `currency` AS `discountinternetCurrencyRate` ON discountinternetCurrencyRate.id = account.discountinternet_currency LEFT JOIN `currency` AS `tccCurrencyRate` ON tccCurrencyRate.id = account.tcc_currency LEFT JOIN `currency` AS `tcrcCurrencyRate` ON tcrcCurrencyRate.id = account.tcrc_currency LEFT JOIN `currency` AS `commissionCurrencyRate` ON commissionCurrencyRate.id = account.commission_currency LEFT JOIN `currency` AS `phonesystemcommissionCurrencyRate` ON phonesystemcommissionCurrencyRate.id = account.phonesystemcommission_currency LEFT JOIN `currency` AS `tollfreemrcCurrencyRate` ON tollfreemrcCurrencyRate.id = account.tollfreemrc_currency LEFT JOIN `currency` AS `staticmrcCurrencyRate` ON staticmrcCurrencyRate.id = account.staticmrc_currency LEFT JOIN `currency` AS `phonesystemlpCurrencyRate` ON phonesystemlpCurrencyRate.id = account.phonesystemlp_currency LEFT JOIN `currency` AS `mrc01CurrencyRate` ON mrc01CurrencyRate.id = account.mrc01_currency LEFT JOIN `user` AS `assignedUser` ON account.assigned_user_id = assignedUser.id WHERE ((account.orderdate >= '2018-10-27' AND account.orderdate <= '2019-01-25')) AND (account.id NOT IN (SELECT account.id AS `id` FROM `account` LEFT JOIN `entity_email_address` AS `emailAddressesMiddle` ON account.id = emailAddressesMiddle.entity_id AND emailAddressesMiddle.deleted = '0' AND emailAddressesMiddle.primary = '1' AND emailAddressesMiddle.entity_type = 'Account' LEFT JOIN `email_address` AS `emailAddresses` ON emailAddresses.id = emailAddressesMiddle.email_address_id AND emailAddresses.deleted = '0' LEFT JOIN `entity_phone_number` AS `phoneNumbersMiddle` ON account.id = phoneNumbersMiddle.entity_id AND phoneNumbersMiddle.deleted = '0' AND phoneNumbersMiddle.primary = '1' AND phoneNumbersMiddle.entity_type = 'Account' LEFT JOIN `phone_number` AS `phoneNumbers` ON phoneNumbers.id = phoneNumbersMiddle.phone_number_id AND phoneNumbers.deleted = '0' LEFT JOIN `currency` AS `mrcCurrencyRate` ON mrcCurrencyRate.id = account.mrc_currency LEFT JOIN `currency` AS `ccmlmrcCurrencyRate` ON ccmlmrcCurrencyRate.id = account.ccmlmrc_currency LEFT JOIN `currency` AS `ccfflmrcCurrencyRate` ON ccfflmrcCurrencyRate.id = account.ccfflmrc_currency LEFT JOIN `currency` AS `ccblmrcCurrencyRate` ON ccblmrcCurrencyRate.id = account.ccblmrc_currency LEFT JOIN `currency` AS `ccimrcCurrencyRate` ON ccimrcCurrencyRate.id = account.ccimrc_currency LEFT JOIN `currency` AS `ccvmrcCurrencyRate` ON ccvmrcCurrencyRate.id = account.ccvmrc_currency LEFT JOIN `currency` AS `discountvideoCurrencyRate` ON discountvideoCurrencyRate.id = account.discountvideo_currency LEFT JOIN `currency` AS `discountvoiceCurrencyRate` ON discountvoiceCurrencyRate.id = account.discountvoice_currency LEFT JOIN `currency` AS `discountinternetCurrencyRate` ON discountinternetCurrencyRate.id = account.discountinternet_currency LEFT JOIN `currency` AS `tccCurrencyRate` ON tccCurrencyRate.id = account.tcc_currency LEFT JOIN `currency` AS `tcrcCurrencyRate` ON tcrcCurrencyRate.id = account.tcrc_currency LEFT JOIN `currency` AS `commissionCurrencyRate` ON commissionCurrencyRate.id = account.commission_currency LEFT JOIN `currency` AS `phonesystemcommissionCurrencyRate` ON phonesystemcommissionCurrencyRate.id = account.phonesystemcommission_currency LEFT JOIN `currency` AS `tollfreemrcCurrencyRate` ON tollfreemrcCurrencyRate.id = account.tollfreemrc_currency LEFT JOIN `currency` AS `staticmrcCurrencyRate` ON staticmrcCurrencyRate.id = account.staticmrc_currency LEFT JOIN `currency` AS `phonesystemlpCurrencyRate` ON phonesystemlpCurrencyRate.id = account.phonesystemlp_currency LEFT JOIN `currency` AS `mrc01CurrencyRate` ON mrc01CurrencyRate.id = account.mrc01_currency LEFT JOIN `user` AS `assignedUser` ON account.assigned_user_id = assignedUser.id WHERE (account.cclines IN ('10','23','46')) AND account.deleted = '0')) AND account.deleted = '0' GROUP BY DATE_FORMAT(account.orderdate, '%Y-%m-%d'), account.assigned_user_id |

****** Query with teams field in filter 
| 225260 | bn_espocrm | localhost          | bitnami_espocrm | Query   |    2 | Sending data | SELECT DATE_FORMAT(account.orderdate, '%Y-%m-%d') AS `DAY:orderdate`, TRIM(CONCAT(assignedUser.first_name, ' ', assignedUser.last_name)) AS `assignedUserName`, account.assigned_user_id AS `assignedUserId`, SUM(account.newvoicepotline) AS `SUM:newvoicepotline`, SUM(account.voicemigrationtovoip) AS `SUM:voicemigrationtovoip` FROM `account` LEFT JOIN `entity_email_address` AS `emailAddressesMiddle` ON account.id = emailAddressesMiddle.entity_id AND emailAddressesMiddle.deleted = '0' AND emailAddressesMiddle.primary = '1' AND emailAddressesMiddle.entity_type = 'Account' LEFT JOIN `email_address` AS `emailAddresses` ON emailAddresses.id = emailAddressesMiddle.email_address_id AND emailAddresses.deleted = '0' LEFT JOIN `entity_phone_number` AS `phoneNumbersMiddle` ON account.id = phoneNumbersMiddle.entity_id AND phoneNumbersMiddle.deleted = '0' AND phoneNumbersMiddle.primary = '1' AND phoneNumbersMiddle.entity_type = 'Account' LEFT JOIN `phone_number` AS `phoneNumbers` ON phoneNumbers.id = phoneNumbersMiddle.phone_number_id AND phoneNumbers.deleted = '0' LEFT JOIN `currency` AS `mrcCurrencyRate` ON mrcCurrencyRate.id = account.mrc_currency LEFT JOIN `currency` AS `ccmlmrcCurrencyRate` ON ccmlmrcCurrencyRate.id = account.ccmlmrc_currency LEFT JOIN `currency` AS `ccfflmrcCurrencyRate` ON ccfflmrcCurrencyRate.id = account.ccfflmrc_currency LEFT JOIN `currency` AS `ccblmrcCurrencyRate` ON ccblmrcCurrencyRate.id = account.ccblmrc_currency LEFT JOIN `currency` AS `ccimrcCurrencyRate` ON ccimrcCurrencyRate.id = account.ccimrc_currency LEFT JOIN `currency` AS `ccvmrcCurrencyRate` ON ccvmrcCurrencyRate.id = account.ccvmrc_currency LEFT JOIN `currency` AS `discountvideoCurrencyRate` ON discountvideoCurrencyRate.id = account.discountvideo_currency LEFT JOIN `currency` AS `discountvoiceCurrencyRate` ON discountvoiceCurrencyRate.id = account.discountvoice_currency LEFT JOIN `currency` AS `discountinternetCurrencyRate` ON discountinternetCurrencyRate.id = account.discountinternet_currency LEFT JOIN `currency` AS `tccCurrencyRate` ON tccCurrencyRate.id = account.tcc_currency LEFT JOIN `currency` AS `tcrcCurrencyRate` ON tcrcCurrencyRate.id = account.tcrc_currency LEFT JOIN `currency` AS `commissionCurrencyRate` ON commissionCurrencyRate.id = account.commission_currency LEFT JOIN `currency` AS `phonesystemcommissionCurrencyRate` ON phonesystemcommissionCurrencyRate.id = account.phonesystemcommission_currency LEFT JOIN `currency` AS `tollfreemrcCurrencyRate` ON tollfreemrcCurrencyRate.id = account.tollfreemrc_currency LEFT JOIN `currency` AS `staticmrcCurrencyRate` ON staticmrcCurrencyRate.id = account.staticmrc_currency LEFT JOIN `currency` AS `phonesystemlpCurrencyRate` ON phonesystemlpCurrencyRate.id = account.phonesystemlp_currency LEFT JOIN `currency` AS `mrc01CurrencyRate` ON mrc01CurrencyRate.id = account.mrc01_currency LEFT JOIN `user` AS `assignedUser` ON account.assigned_user_id = assignedUser.id WHERE (account.id IN (SELECT DISTINCT account.id AS `id` FROM `account` LEFT JOIN `entity_email_address` AS `emailAddressesMiddle` ON account.id = emailAddressesMiddle.entity_id AND emailAddressesMiddle.deleted = '0' AND emailAddressesMiddle.primary = '1' AND emailAddressesMiddle.entity_type = 'Account' LEFT JOIN `email_address` AS `emailAddresses` ON emailAddresses.id = emailAddressesMiddle.email_address_id AND emailAddresses.deleted = '0' LEFT JOIN `entity_phone_number` AS `phoneNumbersMiddle` ON account.id = phoneNumbersMiddle.entity_id AND phoneNumbersMiddle.deleted = '0' AND phoneNumbersMiddle.primary = '1' AND phoneNumbersMiddle.entity_type = 'Account' LEFT JOIN `phone_number` AS `phoneNumbers` ON phoneNumbers.id = phoneNumbersMiddle.phone_number_id AND phoneNumbers.deleted = '0' LEFT JOIN `currency` AS `mrcCurrencyRate` ON mrcCurrencyRate.id = account.mrc_currency LEFT JOIN `currency` AS `ccmlmrcCurrencyRate` ON ccmlmrcCurrencyRate.id = account.ccmlmrc_currency LEFT JOIN `currency` AS `ccfflmrcCurrencyRate` ON ccfflmrcCurrencyRate.id = account.ccfflmrc_currency LEFT JOIN `currency` AS `ccblmrcCurrencyRate` ON ccblmrcCurrencyRate.id = account.ccblmrc_currency LEFT JOIN `currency` AS `ccimrcCurrencyRate` ON ccimrcCurrencyRate.id = account.ccimrc_currency LEFT JOIN `currency` AS `ccvmrcCurrencyRate` ON ccvmrcCurrencyRate.id = account.ccvmrc_currency LEFT JOIN `currency` AS `discountvideoCurrencyRate` ON discountvideoCurrencyRate.id = account.discountvideo_currency LEFT JOIN `currency` AS `discountvoiceCurrencyRate` ON discountvoiceCurrencyRate.id = account.discountvoice_currency LEFT JOIN `currency` AS `discountinternetCurrencyRate` ON discountinternetCurrencyRate.id = account.discountinternet_currency LEFT JOIN `currency` AS `tccCurrencyRate` ON tccCurrencyRate.id = account.tcc_currency LEFT JOIN `currency` AS `tcrcCurrencyRate` ON tcrcCurrencyRate.id = account.tcrc_currency LEFT JOIN `currency` AS `commissionCurrencyRate` ON commissionCurrencyRate.id = account.commission_currency LEFT JOIN `currency` AS `phonesystemcommissionCurrencyRate` ON phonesystemcommissionCurrencyRate.id = account.phonesystemcommission_currency LEFT JOIN `currency` AS `tollfreemrcCurrencyRate` ON tollfreemrcCurrencyRate.id = account.tollfreemrc_currency LEFT JOIN `currency` AS `staticmrcCurrencyRate` ON staticmrcCurrencyRate.id = account.staticmrc_currency LEFT JOIN `currency` AS `phonesystemlpCurrencyRate` ON phonesystemlpCurrencyRate.id = account.phonesystemlp_currency LEFT JOIN `currency` AS `mrc01CurrencyRate` ON mrc01CurrencyRate.id = account.mrc01_currency LEFT JOIN `user` AS `assignedUser` ON account.assigned_user_id = assignedUser.id LEFT JOIN `entity_team` AS `teamsFilter16456Middle` ON account.id = teamsFilter16456Middle.entity_id AND teamsFilter16456Middle.deleted = '0' AND teamsFilter16456Middle.entity_type = 'Account' LEFT JOIN `team` AS `teamsFilter16456` ON teamsFilter16456.id = teamsFilter16456Middle.team_id AND teamsFilter16456.deleted = '0' WHERE ((account.orderdate >= '2018-10-27' AND account.orderdate <= '2019-01-25')) AND (account.id NOT IN (SELECT account.id AS `id` FROM `account` LEFT JOIN `entity_email_address` AS `emailAddressesMiddle` ON account.id = emailAddressesMiddle.entity_id AND emailAddressesMiddle.deleted = '0' AND emailAddressesMiddle.primary = '1' AND emailAddressesMiddle.entity_type = 'Account' LEFT JOIN `email_address` AS `emailAddresses` ON emailAddresses.id = emailAddressesMiddle.email_address_id AND emailAddresses.deleted = '0' LEFT JOIN `entity_phone_number` AS `phoneNumbersMiddle` ON account.id = phoneNumbersMiddle.entity_id AND phoneNumbersMiddle.deleted = '0' AND phoneNumbersMiddle.primary = '1' AND phoneNumbersMiddle.entity_type = 'Account' LEFT JOIN `phone_number` AS `phoneNumbers` ON phoneNumbers.id = phoneNumbersMiddle.phone_number_id AND phoneNumbers.deleted = '0' LEFT JOIN `currency` AS `mrcCurrencyRate` ON mrcCurrencyRate.id = account.mrc_currency LEFT JOIN `currency` AS `ccmlmrcCurrencyRate` ON ccmlmrcCurrencyRate.id = account.ccmlmrc_currency LEFT JOIN `currency` AS `ccfflmrcCurrencyRate` ON ccfflmrcCurrencyRate.id = account.ccfflmrc_currency LEFT JOIN `currency` AS `ccblmrcCurrencyRate` ON ccblmrcCurrencyRate.id = account.ccblmrc_currency LEFT JOIN `currency` AS `ccimrcCurrencyRate` ON ccimrcCurrencyRate.id = account.ccimrc_currency LEFT JOIN `currency` AS `ccvmrcCurrencyRate` ON ccvmrcCurrencyRate.id = account.ccvmrc_currency LEFT JOIN `currency` AS `discountvideoCurrencyRate` ON discountvideoCurrencyRate.id = account.discountvideo_currency LEFT JOIN `currency` AS `discountvoiceCurrencyRate` ON discountvoiceCurrencyRate.id = account.discountvoice_currency LEFT JOIN `currency` AS `discountinternetCurrencyRate` ON discountinternetCurrencyRate.id = account.discountinternet_currency LEFT JOIN `currency` AS `tccCurrencyRate` ON tccCurrencyRate.id = account.tcc_currency LEFT JOIN `currency` AS `tcrcCurrencyRate` ON tcrcCurrencyRate.id = account.tcrc_currency LEFT JOIN `currency` AS `commissionCurrencyRate` ON commissionCurrencyRate.id = account.commission_currency LEFT JOIN `currency` AS `phonesystemcommissionCurrencyRate` ON phonesystemcommissionCurrencyRate.id = account.phonesystemcommission_currency LEFT JOIN `currency` AS `tollfreemrcCurrencyRate` ON tollfreemrcCurrencyRate.id = account.tollfreemrc_currency LEFT JOIN `currency` AS `staticmrcCurrencyRate` ON staticmrcCurrencyRate.id = account.staticmrc_currency LEFT JOIN `currency` AS `phonesystemlpCurrencyRate` ON phonesystemlpCurrencyRate.id = account.phonesystemlp_currency LEFT JOIN `currency` AS `mrc01CurrencyRate` ON mrc01CurrencyRate.id = account.mrc01_currency LEFT JOIN `user` AS `assignedUser` ON account.assigned_user_id = assignedUser.id LEFT JOIN `entity_team` AS `teamsFilter16456Middle` ON account.id = teamsFilter16456Middle.entity_id AND teamsFilter16456Middle.deleted = '0' AND teamsFilter16456Middle.entity_type = 'Account' LEFT JOIN `team` AS `teamsFilter16456` ON teamsFilter16456.id = teamsFilter16456Middle.team_id AND teamsFilter16456.deleted = '0' WHERE (account.cclines IN ('10','23','46')) AND account.deleted = '0')) AND (teamsFilter16456Middle.team_id IN ('5973e6888bbe1a263')) AND account.deleted = '0')) AND account.deleted = '0' GROUP BY DATE_FORMAT(account.orderdate, '%Y-%m-%d'), account.assigned_user_id |