Sample Staff Activity Report Queries

This article contains 6 sample query variations to help you create a "Staff Activity" report for Ares. You can use these to help construct additional reports in AtlasBI or use an ODBC link to run your own queries in Microsoft Access or other BI software.

Some important notes:

• The NumberofItems column is calculated using the sum of individual status entries in the ItemTracking table. You may view these entries from any Item record's History tab in the Ares Client.
• The begin date is inclusive and should be changed to reflect the earliest date of the period you'd like to query.
• The end date is NOT inclusive and should be changed to reflect the day after the last date of the period you'd like to query.
• "By Month" reports have the date entry rounded to the first day of each month for easier sorting.
• Consider adding additional filters to identify activity by Processing Site, Course Department, and more.

Staff Activity by Month by Status

SELECT
DISTINCT k.Status, k.Username, COUNT(DISTINCT i.ItemID) AS NumberOfItems, CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, k.TrackingDateTime), 0)AS DATE)
FROM
Items i
INNER JOIN ItemTracking k ON i.ItemID = k.ItemID
INNER JOIN Staff s ON k.Username = s.Username
WHERE
k.TrackingDateTime >= '7/1/2019'
AND k.TrackingDateTime < '7/1/2020'
GROUP BY
k.Username, k.Status, CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, k.TrackingDateTime), 0)AS DATE)
ORDER BY
k.Username, CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, k.TrackingDateTime), 0)AS DATE), k.Status

Staff Action by Day by Status

SELECT
DISTINCT k.Status, k.Username, COUNT(DISTINCT i.ItemID) AS NumberOfItems, CAST(dateadd(DAY,0, datediff(day,0, k.TrackingDateTime))AS DATE)
FROM
Items i
INNER JOIN ItemTracking k ON i.ItemID = k.ItemID
INNER JOIN Staff s ON k.Username = s.Username
WHERE
k.TrackingDateTime >= '7/1/2019'
AND k.TrackingDateTime < '7/1/2020'
GROUP BY
k.Username, k.Status, CAST(dateadd(DAY,0, datediff(DAY,0, k.TrackingDateTime))AS DATE)
ORDER BY
k.Username, CAST(dateadd(DAY,0, datediff(DAY,0, k.TrackingDateTime))AS DATE), k.Status

Staff Activity by Month for All Statuses

SELECT
DISTINCT k.Username, COUNT(DISTINCT i.ItemID) AS NumberOfItems, CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, k.TrackingDateTime), 0)AS DATE)
FROM
Items i
INNER JOIN ItemTracking k ON i.ItemID = k.ItemID
INNER JOIN Staff s ON k.Username = s.Username
WHERE
k.TrackingDateTime >= '7/1/2019'
AND k.TrackingDateTime < '7/1/2020'
GROUP BY
k.Username, CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, k.TrackingDateTime), 0)AS DATE)
ORDER BY
k.Username, CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, k.TrackingDateTime), 0)AS DATE)

Staff Activity by Day for All Statuses

SELECT
DISTINCT k.Username, COUNT(DISTINCT i.ItemID) AS NumberOfItems, CAST(dateadd(DAY,0, datediff(DAY,0, k.TrackingDateTime))AS DATE)
FROM
Items i
INNER JOIN ItemTracking k ON i.ItemID = k.ItemID
INNER JOIN Staff s ON k.Username = s.Username
WHERE
k.TrackingDateTime >= '7/1/2019'
AND k.TrackingDateTime < '7/1/2020'
GROUP BY
k.Username, CAST(dateadd(DAY,0, datediff(DAY,0, k.TrackingDateTime))AS DATE)
ORDER BY
k.Username, CAST(dateadd(DAY,0, datediff(DAY,0, k.TrackingDateTime))AS DATE)

Staff Total Activity by Month by Status

SELECT
DISTINCT k.Status, COUNT(DISTINCT i.ItemID) AS NumberOfItems, CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, k.TrackingDateTime), 0)AS DATE)
FROM
Items i
INNER JOIN ItemTracking k ON i.ItemID = k.ItemID
INNER JOIN Staff s ON k.Username = s.Username
WHERE
k.TrackingDateTime >= '7/1/2019'
AND k.TrackingDateTime < '7/1/2020'
GROUP BY
k.Status, CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, k.TrackingDateTime), 0)AS DATE)
ORDER BY
CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, k.TrackingDateTime), 0)AS DATE), k.Status

Staff Total Activity by Day by Status

SELECT
DISTINCT k.Status, COUNT(DISTINCT i.ItemID) AS NumberOfItems, CAST(dateadd(DAY,0, datediff(day,0, k.TrackingDateTime))AS DATE)
FROM
Items i
INNER JOIN ItemTracking k ON i.ItemID = k.ItemID
INNER JOIN Staff s ON k.Username = s.Username
WHERE
k.TrackingDateTime >= '7/1/2019'
AND k.TrackingDateTime < '7/1/2020'
GROUP BY
k.Status, CAST(dateadd(DAY,0, datediff(DAY,0, k.TrackingDateTime))AS DATE)
ORDER BY
CAST(dateadd(DAY,0, datediff(DAY,0, k.TrackingDateTime))AS DATE), k.Status