Customer Package Custom Reports

[sonja]

I'm using a the script below for a custom report. I need to add a column to the report that includes the package(s) each customer has. I figured out how to add the service.id. However this service.id does not equate to a package id.

Here is the script I'm running:

 

SELECT `contacts`.`client_id`, `contacts`.`id` AS `contact_id`, `contacts`.`first_name`, `contacts`.`last_name`, `contacts`.`email`, contacts.address1, contacts.city, contacts.zip, contacts.country, service.id, `services`.`status`, services.date_added, `services`.`date_canceled`, COUNT(`services`.`id`) AS `number_of_services`

FROM `contacts`

INNER JOIN `services` ON `services`.`client_id` = `contacts`.`client_id`

GROUP BY `contacts`.`id`

ORDER BY `contacts`.`client_id`, `contacts`.`id`

[Tyson]

Try this:

SELECT `contacts`.*, `services`.`id` AS `service_id`, `services`.`date_renews`, `services`.`date_canceled`, `packages`.`id` AS `package_id`, `packages`.`name` AS `package_name`

FROM `contacts`

INNER JOIN `services` ON `services`.`client_id` = `contacts`.`client_id`

INNER JOIN `package_pricing` ON `package_pricing`.`pricing_id` = `services`.`pricing_id`

INNER JOIN `packages` ON `packages`.`id` = `package_pricing`.`package_id`

WHERE `contacts`.`contact_type` = 'primary'

GROUP BY `services`.`id`

ORDER BY `contacts`.`last_name` DESC;

[Tyson]

"service.id" does not exist, so your query would lead to an error.

 

The query you're using is also rather ambiguous. I'm unsure what data you're intending to retrieve. Even if you have the package ID, it would only be for one of the contact's services at random, which doesn't seem all that useful. It would be helpful to know the requirements for your report.

SELECT `contacts`.`client_id`, `contacts`.`id` AS `contact_id`, `contacts`.`first_name`, `contacts`.`last_name`, `contacts`.`email`, `contacts`.`address1`, `contacts`.`city`, `contacts`.`zip`, `contacts`.`country`, `services`.`id`, `services`.`status`, `services`.`date_added`, `services`.`date_canceled`, COUNT(`services`.`id`) AS `number_of_services`, `package_pricing`.`package_id`
FROM `contacts`
INNER JOIN `services` ON `services`.`client_id` = `contacts`.`client_id`
INNER JOIN `package_pricing` ON `package_pricing`.`pricing_id` = `services`.`pricing_id`
GROUP BY `contacts`.`id`
ORDER BY `contacts`.`client_id`, `contacts`.`id`

[sonja]

Hi Tyson, after playing with it, I found the same error. Removing the services id code fixed the problem, and the result is a report listing all our customers, their contact information, status, and if they canceled service, when that occurred.

What I need from this report, or another one is the customer's name, address, email, status, and the package they are using.

For California reporting we must list addresses with download and upload speeds. 

[Tyson]

Do you only need the primary customer's name, address, etc., or do you need that information for every contact under that client (e.g. billing contacts)?

Do you need a list of all services as well?

What information about the package do you need? Just the package ID? The package name?

Do you need to filter by a date range?

 

I'm thinking you may want a list of every service for every client, so the export would contain every primary contact (multiple times for every service they have) along with the service/package information you want. Does that sound right?

[sonja]

I need only the primary customer's name and info, and a list of all the services by id and name. I planned on filtering by a combo of status and date services started and were cancelled. 

[sonja]

Thank you!