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
11. Example showing how to get rows either inserted or updated in the last day

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

SELECT COUNT(1) 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.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.ACCOUNTING_PERIOD_NUMERICDATE = 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
Table: PLACEMENT_FACT
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.ACTIVITYDATEKEY = d.DATEKEY
JOIN DIMAPPLICANT a ON a.applicantkey = p.applicantkey
JOIN DIMCOMPANY c ON c.companykey = p.companykey
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
Tables: INVOICE_FACT, DIMCOMPANY
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.DATEKEY = i.DATEKEY
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
Tables: FCTACTIVITY, DIMOFFICE
This query counts all activities by each office.
 
SELECT o.OFFICENAME, COUNT(a.ACTIVITYKEY) AS ACTIVITYCOUNT
FROM FCTACTIVITY a
JOIN DIMREQ r ON r.REQKEY = a.REQKEY
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
Tables: REVENUE_ACTIVITY_FACT, DIMREQ
This query calculates the average bill rate for each job requirement.
 
SELECT r.REQID, AVG(ra.BILLAMOUNT) AS Avg_Bill_Amount
FROM REVENUE_ACTIVITY_FACT ra
JOIN DIMREQ r ON ra.REQKEY = r.REQKEY
GROUP BY r.REQID
LIMIT 10;
 
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 d.WEEKOFYEAR, COUNT(1) AS NUMBER_OF_PLACEMENT_STARTS
FROM PLACEMENT_FACT p
JOIN DIMDATE d ON d.DATEKEY = p.ACTIVITYDATEKEY
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 ACCOUNTING_PERIOD_DATE, SUM( GROSSPROFIT)
FROM GROSSPROFIT_FACT gp
JOIN DIMDATE d ON d.NUMERICDATE = gp.ACCOUNTING_PERIOD_NUMERICDATE
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
Tables: REVENUE_ACTIVITY_FACT, DIMOFFICE
Top 10 Offices by Total Pipeline Count for January 2025
 
SELECT a.ACTIVITYNAME, COUNT(1) AS NUMBER_OF_INTERVIEWS
FROM FCTACTIVITY f
JOIN DIMACTIVITYTYPE a ON a.ACTIVITYTYPEKEY = f.ACTIVITYTYPEKEY
JOIN DIMDATE d ON d.DATEKEY = f.DATEKEY
WHERE a.ACTIVITYNAME LIKE '%Interview%'
AND d.FULLDATEKEY BETWEEN '2025-01-01' AND '2025-01-31'
GROUP BY a.ACTIVITYNAME
ORDER BY NUMBER_OF_INTERVIEWS DESC
 
10. Number of Interviews by Type in January 2025
Number of Interviews by Type in January 2025
 
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;

11. Example showing how to get rows either inserted or updated in the last day

SELECT a.*
FROM DIMAPPLICANT a
WHERE a.DW_INSERTEDDATE >= DATEADD(day, -1, CURRENT_TIMESTAMP())
OR a.DW_UPDATEDDATE >= DATEADD(day, -1, CURRENT_TIMESTAMP())