# "ListUserTimeSummary" Stored Procedure

Description:

This stored procedure retrieves multiple user summaries within a date range. The parameter @UserIDList is a comma delimited UserIDs string (i.e. "1,2,3").

Definition:

    	CREATE  PROCEDURE TT_ListUserTimeSummary
    	(
    		@ManagerUserID int,
    		@UserIDList nvarchar(512),
    		@StartDate datetime,
    		@EndDate datetime
    	)
    	AS

    	DECLARE
    		@sSqlString nvarchar(1024),
    		@sSubSql nvarchar(1024),
    		@RoleID int

    	SELECT @RoleID = RoleID
    	FROM TT_Users
    	WHERE UserID = @ManagerUserID;

    	IF (@RoleID = 1)
    		BEGIN
    		SET @sSqlString = 'SELECT Sum(EL.Duration) TotalHours, U.UserID, U.UserName'
    		SET @sSqlString = @sSqlString + ' FROM TT_EntryLog EL Inner Join TT_Users U On EL.UserID = U.UserID WHERE U.UserID IN (' + @UserIDList + ')'
    		SET @sSqlString = @sSqlString + ' and EL.EntryDate >= @1 and EL.EntryDate <= @2  GROUP BY U.UserID, U.UserName'
    		END
    	ELSE IF (@RoleID = 2)
    		BEGIN
    		SET @sSubSql = 'SELECT PM.UserID FROM TT_Projects P INNER JOIN TT_ProjectMembers PM'
    		SET @sSubSql = @sSubSql + ' ON P.ProjectID = PM.ProjectID WHERE P.ManagerUserID = @3 AND PM.UserID IN (' + @UserIDList + ')'

    		SET @sSqlString = 'SELECT Sum(EL.Duration) TotalHours, U.UserID, U.UserName'
    		SET @sSqlString = @sSqlString + ' FROM TT_EntryLog EL Inner Join TT_Users U On EL.UserID = U.UserID WHERE U.UserID IN (' + @sSubSql + ')'
    		SET @sSqlString = @sSqlString + ' AND EL.ProjectID IN (SELECT ProjectID From TT_Projects Where ManagerUserID = @3) '
    		SET @sSqlString = @sSqlString + ' and EL.EntryDate >= @1 and EL.EntryDate <= @2  GROUP BY U.UserID, U.UserName'
    		END
    	ELSE
    		SET @sSqlString = 'SELECT U.UserID AS TotalHours, U.UserID, U.UserName From TT_Users U Where 1=0'

    	EXEC sp_executesql @sSqlString, N'@1 datetime, @2 datetime, @3 int', @StartDate, @EndDate, @ManagerUserID

Database Tables Used:

The primary key in the EntryLog table is the EntryLogID identity field. The field UserID references back to the Users table's primary key.

The primary key in the Users table is the UserID identity field.