# "ListTimeEntries" Stored Procedure

Description:

This stored procedure retrieves all time entries for a given user within a set date range. The input parameters include the id of the user making the inquiry, the id of the user whose records are to be retrieved, and the Start and End dates.

Definition:

    	CREATE PROCEDURE TT_ListTimeEntries
    	(
    		@QueryUserID int,
    		@UserID int,
    		@StartDate datetime,
    		@EndDate datetime
    	)
    	AS
    	DECLARE @@QueryUserRoleID int

    	SELECT @@QueryUserRoleID = TT_Users.RoleID FROM TT_Users WHERE TT_Users.UserID = @QueryUserID

    	IF @@QueryUserRoleID = 2
    		BEGIN
    			SELECT
    				EntryLogID, TT_EntryLog.Description, Duration, EntryDate, TT_EntryLog.ProjectID AS ProjectID,
    				TT_EntryLog.CategoryID AS CategoryID, TT_Categories.Abbreviation AS CategoryName, TT_Projects.Name AS ProjectName,
    				ManagerUserID, TT_Categories.Abbreviation AS CatShortName
    			FROM
    				TT_EntryLog
    					INNER JOIN
    					TT_Categories
    					ON
    					TT_EntryLog.CategoryID = TT_Categories.CategoryID
    					INNER JOIN
    					TT_Projects
    					ON
    					TT_EntryLog.ProjectID = TT_Projects.ProjectID
    			WHERE
    				UserID = @UserID
    				AND
    				Convert(nvarchar, EntryDate, 1) >= Convert(nvarchar, @StartDate, 1)
    				AND
    				Convert(nvarchar, EntryDate, 1) <= Convert(nvarchar, @EndDate, 1)
    				AND
    				ManagerUserID = @QueryUserID
    		END

    	ELSE IF @@QueryUserRoleID = 1 or @QueryUserID = @UserID
    		BEGIN
    			SELECT
    				EntryLogID, TT_EntryLog.Description, Duration, EntryDate, TT_EntryLog.ProjectID AS ProjectID,
    				TT_EntryLog.CategoryID AS CategoryID, TT_Categories.Abbreviation AS CategoryName, TT_Projects.Name AS ProjectName,
    				ManagerUserID, TT_Categories.Abbreviation AS CatShortName
    			FROM
    				TT_EntryLog
    					INNER JOIN
    					TT_Categories
    					ON
    					TT_EntryLog.CategoryID = TT_Categories.CategoryID
    					INNER JOIN
    					TT_Projects
    					ON
    					TT_EntryLog.ProjectID = TT_Projects.ProjectID
    			WHERE
    				UserID = @UserID
    				AND
    				Convert(nvarchar, EntryDate, 1) >= Convert(nvarchar, @StartDate, 1)
    				AND
    				Convert(nvarchar, EntryDate, 1) <= Convert(nvarchar, @EndDate, 1)
    		END

Database Tables Used:

The primary key in the EntryLog table is the EntryLogID identity field. There is a foreign key relationship between the field CategoryID and the Category table's primary key, which will force referential integrity upon inserts and updates. Also, the field ProjectID references the Projects table's primary key.

The primary key in the Projects table is the ProjectID identity field.

The primary key in the Categories table is the CategoryID identity field.