Skip to main content

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.

tip

If you have any questions or need assistance, please contact Atlas Support at support@atlas-sys.com.

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​

SELECTDISTINCT k.Status, k.Username, COUNT(DISTINCT i.ItemID) AS NumberOfItems, CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, k.TrackingDateTime), 0)AS DATE)FROMItems iINNER JOIN ItemTracking k ON i.ItemID = k.ItemIDINNER JOIN Staff s ON k.Username = s.UsernameWHEREk.TrackingDateTime >= '7/1/2019'AND k.TrackingDateTime < '7/1/2020'GROUP BYk.Username, k.Status, CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, k.TrackingDateTime), 0)AS DATE)ORDER BYk.Username, CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, k.TrackingDateTime), 0)AS DATE), k.Status

Staff Action by Day by Status​

SELECTDISTINCT k.Status, k.Username, COUNT(DISTINCT i.ItemID) AS NumberOfItems, CAST(dateadd(DAY,0, datediff(day,0, k.TrackingDateTime))AS DATE)FROMItems iINNER JOIN ItemTracking k ON i.ItemID = k.ItemIDINNER JOIN Staff s ON k.Username = s.UsernameWHEREk.TrackingDateTime >= '7/1/2019'AND k.TrackingDateTime < '7/1/2020'GROUP BYk.Username, k.Status, CAST(dateadd(DAY,0, datediff(DAY,0, k.TrackingDateTime))AS DATE)ORDER BYk.Username, CAST(dateadd(DAY,0, datediff(DAY,0, k.TrackingDateTime))AS DATE), k.Status

Staff Activity by Month for All Statuses​

SELECTDISTINCT k.Username, COUNT(DISTINCT i.ItemID) AS NumberOfItems, CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, k.TrackingDateTime), 0)AS DATE)FROMItems iINNER JOIN ItemTracking k ON i.ItemID = k.ItemIDINNER JOIN Staff s ON k.Username = s.UsernameWHEREk.TrackingDateTime >= '7/1/2019'AND k.TrackingDateTime < '7/1/2020'GROUP BYk.Username, CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, k.TrackingDateTime), 0)AS DATE)ORDER BYk.Username, CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, k.TrackingDateTime), 0)AS DATE)

Staff Activity by Day for All Statuses​

SELECTDISTINCT k.Username, COUNT(DISTINCT i.ItemID) AS NumberOfItems, CAST(dateadd(DAY,0, datediff(DAY,0, k.TrackingDateTime))AS DATE)FROMItems iINNER JOIN ItemTracking k ON i.ItemID = k.ItemIDINNER JOIN Staff s ON k.Username = s.UsernameWHEREk.TrackingDateTime >= '7/1/2019'AND k.TrackingDateTime < '7/1/2020'GROUP BYk.Username, CAST(dateadd(DAY,0, datediff(DAY,0, k.TrackingDateTime))AS DATE)ORDER BYk.Username, CAST(dateadd(DAY,0, datediff(DAY,0, k.TrackingDateTime))AS DATE)

Staff Total Activity by Month by Status​

SELECTDISTINCT k.Status, COUNT(DISTINCT i.ItemID) AS NumberOfItems, CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, k.TrackingDateTime), 0)AS DATE)FROMItems iINNER JOIN ItemTracking k ON i.ItemID = k.ItemIDINNER JOIN Staff s ON k.Username = s.UsernameWHEREk.TrackingDateTime >= '7/1/2019'AND k.TrackingDateTime < '7/1/2020'GROUP BYk.Status, CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, k.TrackingDateTime), 0)AS DATE)ORDER BYCAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, k.TrackingDateTime), 0)AS DATE), k.Status

Staff Total Activity by Day by Status​

SELECTDISTINCT k.Status, COUNT(DISTINCT i.ItemID) AS NumberOfItems, CAST(dateadd(DAY,0, datediff(day,0, k.TrackingDateTime))AS DATE)FROMItems iINNER JOIN ItemTracking k ON i.ItemID = k.ItemIDINNER JOIN Staff s ON k.Username = s.UsernameWHEREk.TrackingDateTime >= '7/1/2019'AND k.TrackingDateTime < '7/1/2020'GROUP BYk.Status, CAST(dateadd(DAY,0, datediff(DAY,0, k.TrackingDateTime))AS DATE)ORDER BYCAST(dateadd(DAY,0, datediff(DAY,0, k.TrackingDateTime))AS DATE), k.Status