Example SQL queries for reports

Technical staff at your organization can refer to easy examples of SQL queries to know which types of ezyVet data they can get from ezyVet Data Lake.

SQL query for an invoice report

The SQL query that follows gives:

The ID of each invoice
The date that a person made each invoice
The total quantity of each invoice
The unpaid quantity of each invoice
The number of payments made foe each invoice
The date of the most recent payment of each invoice
The number of invoice line items of the invoice
The first name and last name of the client of each invoice

SELECT
  invoice.invoice_id AS 'Invoice ID',
  FROM_UNIXTIME(invoice.invoicedata_date) AS 'Created At',
  invoice.invoicedata_amount AS 'Invoice Amount',
  invoice.invoicedata_due AS 'Invoice Amount Due',
  COUNT(DISTINCT(financialallocations_credit.financialallocation_amount)) AS 'Payments Made',
  FROM_UNIXTIME(MAX(financialallocations_credit.financialallocation_time)) AS 'Last Payment',
  COUNT(DISTINCT(invoiceexpense.invoiceexpense_id)) AS 'Line Items',
  contact.contactdata_first AS 'Customer First Name',
  contact.contactdata_last AS 'Customer Last Name'
FROM invoice
LEFT JOIN financial AS financial_invoice
  ON financial_invoice.financialdata_ownertable = 'invoice'
  AND financial_invoice.financialdata_ownerid = invoice.invoice_id
  AND financial_invoice.financialdata_active = 1
LEFT JOIN financialallocations AS financialallocations_credit
  ON financialallocations_credit.financialallocation_financial = financial_invoice.financial_id
LEFT JOIN contact
  ON contact.contact_id = financial_invoice.financialdata_clientcontact
  AND contact.contactdata_active = 1
LEFT JOIN invoiceexpense
  ON invoiceexpense.invoiceexpensedata_invoice = invoice.invoice_id
  AND invoiceexpense.invoiceexpensedata_active = 1
  AND invoiceexpense.invoiceexpensedata_isheader = 0
WHERE invoice.invoicedata_approvedat IS NOT NULL
  AND invoice.invoicedata_active = 1
  AND invoice.invoicedata_amount != 0.00
GROUP BY invoice.invoice_id;

SQL query for a user accounts report

The SQL query that follows gives:

Each user account
The name of each person of each user account
The username of each person of each user account
The permissions role of each user account

SELECT 
	userdata_name AS 'User Name',
	userdata_login AS 'User Login',
	GROUP_CONCAT(permissiongroupdata_name) AS 'Role Name'
FROM user
LEFT JOIN userpermissiongroup 
	ON userpermissiongroupdata_groupuser = user_id
LEFT JOIN permissiongroup 
	ON permissiongroup_id = userpermissiongroupdata_permissiongroup
WHERE userdata_active = 1
	AND userpermissiongroupdata_active = 1
	AND permissiongroupdata_active = 1
	AND permissiongroupdata_name != 'ezyVet Support'
GROUP BY user_id ORDER BY permissiongroupdata_name ASC;

SQL query for a clinical records report

The SQL query that follows gives:

Each clinical record and its code
The date the a person made each clinical record
Each patient and breed of each clinical record
The primary veterinarian of each clinical record
The date of birth of each patient of each clinical record
If the patient of each clinical record is deceased
The total quantity that the organization invoiced for the procedures and products of each clinical record

SELECT 
	consult_number AS 'Consult Code',
	CONVERT_TZ(FROM_UNIXTIME(consult_time), 'UTC', 'America/Denver') AS 'Consult Created',
	animaldata_name AS 'Animal Name',
	breeddata_name AS 'Animal Breed',
	userdata_name AS 'Vet',
	CONVERT_TZ(FROM_UNIXTIME(animaldata_dateofbirth), 'UTC', 'America/Denver') AS 'Animal DoB',
	IF(animaldata_dead, 'Yes', 'No') AS 'Animal Deceased',
	invoicedata_amount AS 'Invoice Total'
FROM consult
LEFT JOIN animal
	ON animal_id = consultdata_animal
LEFT JOIN invoice
	ON invoicedata_consult = consult_id
LEFT JOIN breed
	ON breed_id = animaldata_breed
LEFT JOIN user
	ON user_id = consultdata_caseowner
WHERE consultdata_active = 1
	AND invoicedata_active = 1
	AND breeddata_active = 1
ORDER BY consult_time DESC;

ezyVet Knowledge Center

Get access to documentation, training, and other high-quality resources.

Example SQL queries for reports

SQL query for an invoice report

SQL query for a user accounts report

SQL query for a clinical records report

ezyVet-footer

Products

Solutions

Resources

Company

ezyVet Knowledge Center

Get access to documentation, training, and other high-quality resources.

Example SQL queries for reports

SQL query for an invoice report

SQL query for a user accounts report

SQL query for a clinical records report

ezyVet-footer

Products

Solutions

Resources

Company

We’re here for you.