More Fun with Stats: 6 Staff Activity Report Queries

Happy Fall, y'all!

We received a request for a report in Ares that mimicked the "Staff Activity" web report in ILLiad, so I've come up with 6 variations to help you get started. 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. If you have questions or get stuck, send requests to support@atlas-sys.com and we'll do out best to help you out.

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 to 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

 

 

 

 

 

0

Comments

0 comments

Please sign in to leave a comment.

Didn't find what you were looking for?

New post