Introduction to Sample Queries for Avionté DataLink

Introduction to Sample Queries for Avionté DataLink 

Avionte’s DataLink 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. GP by Week of Year for 2025- View the GP by week for 2025
9. Pipeline Counts per Office – Track the candidate pipeline count at each office, useful for assessing recruiting workload and potential hires.
10. Number of Interviews by Type in January 2025 - Number of Interviews by Type in January 2025

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

SELECT COUNT(1) AS Total_Activities FROM ACTIVITY_FACT;


2. Get Monthly Gross Profit by Company
This query retrieves the monthly gross profit for each company.

SELECT c.COMPANYNAME, d.YEAROFYEAR, d.MONTHOFYEAR, SUM(g.GROSSPROFIT) AS TOTALGROSSPROFIT
FROM GROSSPROFIT_FACT g
JOIN DIMCOMPANY c ON g.COMPANYKEY = c.COMPANYKEY
JOIN DIMDATE d ON g.ACCOUNTINGPERIODNUMERICDATE = d.NUMERICDATE
WHERE d.YEAROFYEAR = 2024
GROUP BY c.COMPANYNAME,d.YEAROFYEAR, d.MONTHOFYEAR
ORDER BY c.COMPANYNAME, d.YEAROFYEAR DESC, d.MONTHOFYEAR DESC;


3. List All Placements for a Specific Date Range
This query finds all placements within a specified date range.

SELECT d.FULLDATEKEY, c.COMPANYNAME, a.APPLASTNAME, a.APPFIRSTNAME
FROM PLACEMENT_FACT p
JOIN DIMDATE d ON p.ACTDATE = d.CALENDARDATE
JOIN DIMAPPLICANT a ON a.applicantkey = p.applicantkey
JOIN DIMCOMPANY c ON c.companyid = p.companyid
WHERE d.FULLDATEKEY BETWEEN '2024-01-01' AND '2024-12-31'
ORDER BY d.FULLDATEKEY, c.COMPANYNAME, a.APPLASTNAME, a.APPFIRSTNAME
LIMIT 100;


4. Identify Top 5 Customers by Total Billed Amount
This query lists the top 5 customers by total billed amount.

SELECT c.COMPANYNAME, SUM(i.INVOICEAMOUNT) AS TOTALBILLAMOUNT
FROM INVOICE_FACT i
JOIN DIMCOMPANY c ON i.COMPANYKEY = c.COMPANYKEY
JOIN DIMDATE d ON d.CALENDARDATE = i.INVOICEDATE
WHERE d.FULLDATEKEY BETWEEN '2024-12-01' AND '2024-12-31'
GROUP BY c.COMPANYNAME
ORDER BY TOTALBILLAMOUNT DESC
LIMIT 5;


5. Count of Activities by Office
This query counts all activities by each office.

SELECT o.OFFICENAME, COUNT(a.ACTIVITYKEY) AS ACTIVITYCOUNT
FROM ACTIVITY_FACT a
JOIN DIMRPTREQ r ON r.REQID = a.REQID
JOIN DIMOFFICE o ON o.OFFICEKEY = r.OFFICEKEY
JOIN DIMDATE d ON d.DATEKEY = a.DATEKEY
WHERE d.FULLDATEKEY BETWEEN '2025-01-12' AND '2025-01-18'
GROUP BY o.OFFICENAME;


6. Average Bill Rate by Job Requirement
This query calculates the average bill rate for each job requirement.

SELECT r.REQID, AVG(ra.BILLAMOUNT) AS AVERAGEBILLAMOUNT
FROM REVENUE_ACTIVITY_FACT ra
JOIN DIMRPTREQ r ON ra.REQID = r.REQID
WHERE ra.BILLAMOUNT IS NOT NULL
GROUP BY r.REQID
ORDER BY AVERAGEBILLAMOUNT DESC
LIMIT 10;


7. Total Hours Billed by Employee for a Given Month
This query returns the total regular, overtime, and double-time hours billed by each employee in a specific month.

SELECT d.WEEKOFYEAR, COUNT(1) AS NUMBEROFPLACEMENTSTARTS
FROM PLACEMENT_FACT p
JOIN DIMDATE d ON d.CALENDARDATE = p.ACTDATE
JOIN DIMACTIVITYTYPE a ON a.ACTIVITYTYPEKEY = p.ACTIVITYTYPEKEY
WHERE d.FULLDATEKEY BETWEEN '2025-01-01' AND '2025-12-31'
AND a.ACTIVITYNAME = 'Placement Start'
GROUP BY d.WEEKOFYEAR
ORDER BY d.WEEKOFYEAR;


8. GP by Week of Year for 2025
View the GP by week for 2025

SELECT d.WEEKOFYEAR, d.FULLDATEKEY AS ACCOUNTINGPERIODDATE, SUM( GROSSPROFIT ) AS GROSSPROFIT
FROM GROSSPROFIT_FACT gp
JOIN DIMDATE d ON d.NUMERICDATE = gp.ACCOUNTINGPERIODNUMERICDATE
WHERE d.FULLDATEKEY BETWEEN '2025-01-01' AND '2025-12-31'
GROUP BY d.WEEKOFYEAR, FULLDATEKEY
ORDER BY d.WEEKOFYEAR;


9. Pipeline Counts for Top 10 Offices

Top 10 Offices by Total Pipeline Count for January 2025

SELECT o.OFFICENAME, SUM(ra.PIPELINECOUNT) AS PIPELINECOUNT
FROM REVENUE_ACTIVITY_FACT ra
JOIN DIMOFFICE o ON ra.OFFICEID = o.OFFICEID
JOIN DIMDATE d ON d.CALENDARDATE = ra.ACCOUNTINGPERIODDATE
WHERE d.FULLDATEKEY BETWEEN '2025-01-01' AND '2025-01-31'
GROUP BY o.OFFICENAME
ORDER BY PIPELINECOUNT DESC
LIMIT 10;

10. Number of Interviews by Type in January 2025
Number of Interviews by Type in January 2025

SELECT a.ACTIVITYNAME, COUNT(1) AS NUMBEROFINTERVIEWS
FROM ACTIVITY_FACT f
JOIN DIMACTIVITYTYPE a ON a.ACTIVITYTYPEKEY = f.ACTIVITYTYPEKEY
JOIN DIMDATE d ON d.CALENDARDATE = f.ACTDATE
WHERE a.ACTIVITYNAME LIKE '%Interview%'
AND d.FULLDATEKEY BETWEEN '2025-01-01' AND '2025-01-31'
GROUP BY a.ACTIVITYNAME
ORDER BY NUMBEROFINTERVIEWS DESC;