This script retrieves the most recent "add" and "view" activities performed by users on documents within a specific project, grouping the results by user and action type while ensuring that only relevant actions are considered.
SELECT
u.UserName, -- Retrieves the username of the user who performed the action
f.ProjectID, -- Retrieves the project ID associated with the file
-- Determines the most recent activity date for either 'add' (ActionID = 1) or 'view' (ActionID = 4)
MAX(CASE
WHEN a.ActionID = '1' THEN a.ActivityDate -- If ActionID is 1 (add), return ActivityDate
WHEN a.ActionID = '4' THEN a.ActivityDate -- If ActionID is 4 (view), return ActivityDate
END) AS LastActivityDate,
-- Assigns a label ('add' or 'view') based on the ActionID value
CASE
WHEN a.ActionID = '1' THEN 'add' -- If ActionID is 1, label it as 'add'
WHEN a.ActionID = '4' THEN 'view' -- If ActionID is 4, label it as 'view'
END AS action
FROM Documents d
JOIN Files f ON f.FileID = d.FileID -- Joins Files table to retrieve project-related file details
JOIN ActivityLog a ON d.DocumentID = a.EntityID -- Joins ActivityLog to track user actions on documents
JOIN Users u ON a.UserID = u.UserID -- Joins Users table to get the username of the person performing the action
WHERE f.ProjectID = '1' -- Filters results to include only activities for Project ID 1 (Replace as needed)
AND a.ActionID IN ('1', '4') -- Filters only 'add' and 'view' actions
GROUP BY u.UserName, f.ProjectID, a.ActionID -- Groups results by username, project ID, and action type
ORDER BY LastActivityDate DESC; -- Orders the results by the most recent activity date in descending order