Introduction to Sample Queries for Avionte Enterprise Data Feed

Introduction to Sample Queries for Avionte Enterprise Data Feed

Avionte’s Enterprise Data Feed provides a rich data environment, enabling users to pull specific insights directly from their staffing and recruiting data. Through SQL, users can explore, aggregate, and analyze key metrics across various themes like gross profit, invoicing, applicant status, and employee activities. The following examples show how you can start building reports and dashboards that provide valuable information to improve operations, track placements, and optimize financial performance.
 

Overview of Queries

1. Total Activity Count – Get a high-level view of all recorded activities within your environment to understand volume and scale.
2. Monthly Gross Profit by Company – Track gross profit by company to analyze which clients are most profitable month-over-month.
3. Placements by Date Range – Filter placements within a specific time frame, ideal for period-based reporting on successful hires or placements.
4. Top Customers by Total Billed Amount – Identify your top revenue-generating clients based on invoiced amounts, which helps in focusing account management efforts.
5. Activity Counts by Office – Monitor activity levels across offices to assess workload and regional performance.
6. Average Bill Rate by Job Requirement – Calculate the average billing rate for each job requirement to understand pricing trends.
7. Hours Billed by Employee for a Given Month – Review billed hours by employees, breaking down into regular, overtime, and double-time categories for a complete picture of work allocation and billing.
8. Active Applicants with Status and Branch – View all currently active applicants with their respective branch, useful for talent and applicant status tracking.
9. Pipeline Counts per Office – Track the candidate pipeline count at each office, useful for assessing recruiting workload and potential hires.
10. Average Pay Rate by Applicant Role – Calculate average pay rates by role to understand pay structures and ensure competitive compensation.

1. Count Total Activities
Table: FCTACTIVITY
This query gives the total number of activities (applicant, job, and contact).

SELECT COUNT(*) AS Total_Activities FROM FCTACTIVITY;
 
2. Get Monthly Gross Profit by Company
Tables: GROSSPROFIT_FACT, DIMCOMPANY, DIMDATE
This query retrieves the monthly gross profit for each company.
 
SELECT c.COMPANYNAME, d.MONTH, SUM(g.GROSSPROFIT) AS Total_Gross_Profit
FROM GROSSPROFIT_FACT g
JOIN DIMCOMPANY c ON g.COMPANYKEY = c.COMPANYKEY
JOIN DIMDATE d ON g.ACCOUNTING_PERIOD_NUMERICDATE = d.DATEKEY
GROUP BY c.COMPANYNAME, d.MONTH;
 
3. List All Placements for a Specific Date Range
Table: PLACEMENT_FACT
This query finds all placements within a specified date range.
 
SELECT * FROM PLACEMENT_FACT
WHERE ACTIVITYDATEKEY BETWEEN '20230101' AND '20231231';
 
4. Identify Top 5 Customers by Total Billed Amount
Tables: INVOICE_FACT, DIMCOMPANY
This query lists the top 5 customers by total billed amount.
 
SELECT c.COMPANYNAME, SUM(i.INVOICEAMOUNT) AS Total_Billed_Amount
FROM INVOICE_FACT i
JOIN DIMCOMPANY c ON i.COMPANYKEY = c.COMPANYKEY
GROUP BY c.COMPANYNAME
ORDER BY Total_Billed_Amount DESC
LIMIT 5;
 
5. Count of Activities by Office
Tables: FCTACTIVITY, DIMOFFICE
This query counts all activities by each office.
 
SELECT o.OFFICENAME, COUNT(a.ACTIVITYKEY) AS Activity_Count
FROM FCTACTIVITY a
JOIN DIMOFFICE o ON a.OFFICEKEY = o.OFFICEKEY
GROUP BY o.OFFICENAME;
 
6. Average Bill Rate by Job Requirement
Tables: REVENUE_ACTIVITY_FACT, DIMREQ
This query calculates the average bill rate for each job requirement.
 
SELECT r.REQID, AVG(ra.BILLRATE) AS Avg_Bill_Rate
FROM REVENUE_ACTIVITY_FACT ra
JOIN DIMREQ r ON ra.REQKEY = r.REQKEY
GROUP BY r.REQID;
 
7. Total Hours Billed by Employee for a Given Month
Table: FCTACTIVITY
This query returns the total regular, overtime, and double-time hours billed by each employee in a specific month.
 
SELECT EMPLOYEEID,
SUM(FINREGHRS) AS Regular_Hours,
SUM(FINOTHRS) AS Overtime_Hours,
SUM(FINDTHRS) AS Double_Time_Hours
FROM FCTACTIVITY
WHERE DATEKEY BETWEEN '20230801' AND '20230831'
GROUP BY EMPLOYEEID;
 
8. List All Active Applicants with Status and Location
Tables: DIMAPPLICANT, DIMCOMPANYBRANCH
This query lists all active applicants, including their current status and branch.
 
SELECT a.APPFIRSTNAME, a.APPLASTNAME, a.APPSTATUS, b.BRANCHNAME
FROM DIMAPPLICANT a
JOIN DIMCOMPANYBRANCH b ON a.COMPANYKEY = b.COMPANYKEY
WHERE a.APPSTATUS = 'Active';
 
9. Pipeline Counts for Each Office
Tables: REVENUE_ACTIVITY_FACT, DIMOFFICE
This query provides the pipeline count of candidates at each office.
 
SELECT o.OFFICENAME, SUM(ra.PIPELINE_COUNT) AS Pipeline_Count
FROM REVENUE_ACTIVITY_FACT ra
JOIN DIMOFFICE o ON ra.OFFICEKEY = o.OFFICEKEY
GROUP BY o.OFFICENAME;
 
10. Calculate Average Pay Rate per Applicant Role
Tables: TALENT_FACT, DIMAPPLICANT
This query calculates the average pay rate for each role.
 
SELECT a.ROLE, AVG(t.PAYRATE) AS Avg_Pay_Rate
FROM TALENT_FACT t
JOIN DIMAPPLICANT a ON t.APPLICANTKEY = a.APPLICANTKEY
GROUP BY a.ROLE;