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;