Powershell

Updated Stored Procedure

sql
CREATE PROCEDURE SP_GetAvailableTasksForTimesheet
    @TimeSheetID INT
AS
BEGIN
    -- Declare variables to hold the WeekStartDate and WeekEndDate
    DECLARE @WeekStartDate DATE;
    DECLARE @WeekEndDate DATE;
    DECLARE @UserName NVARCHAR(100);

    -- Fetch the WeekStartDate, WeekEndDate, and UserName from the TimeSheet table
    SELECT 
        @WeekStartDate = TimeSheetStartDate,
        @WeekEndDate = TimeSheetEndDate,
        @UserName = EmployeeNWName
    FROM 
        TimeSheet
    WHERE 
        TimeSheetID = @TimeSheetID;

    -- Select tasks that overlap with the given WeekStartDate and WeekEndDate
    -- and are not already present in the TimeEntry table
    SELECT DISTINCT
        T.TaskID,
        T.TaskName,
        T.TaskStartDate,
        T.TaskEndDate,
        PR.ProjectID,
        P.ProjectName
    FROM Task T
    JOIN ProjectResource PR ON PR.ProjectID = T.ProjectID
    JOIN Project P ON PR.ProjectID = P.ProjectID
    WHERE PR.EmployeeNWName = @UserName
      AND P.Progress = 'Open'
      AND T.Active = 1
      AND (
            -- Check overlap between WeekStartDate-WeekEndDate and TaskStartDate-TaskEndDate
            @WeekStartDate <= ISNULL(T.TaskEndDate, '9999-12-31') AND @WeekEndDate >= T.TaskStartDate
            AND
            -- Check overlap between WeekStartDate-WeekEndDate and ResourceStartDate-ResourceEndDate
            @WeekStartDate <= ISNULL(PR.ResourceEndDate, '9999-12-31') AND @WeekEndDate >= PR.ResourceStartDate
            AND
            -- Check overlap between TaskStartDate-TaskEndDate and ResourceStartDate-ResourceEndDate
            T.TaskStartDate <= ISNULL(PR.ResourceEndDate, '9999-12-31') AND ISNULL(T.TaskEndDate, '9999-12-31') >= PR.ResourceStartDate
          )
      AND NOT EXISTS (
            SELECT 1
            FROM TimeEntry TE
            WHERE TE.TaskID = T.TaskID
              AND TE.TimeSheetID = @TimeSheetID
        );
END;

SharePoint Server Subscription Edition (SE) supports the following versions of Windows Server:

1. Windows Server 2019
2. Windows Server 2022

Sharepoint server upgrade

SharePoint upgrades from SharePoint 2019 to SharePoint Server Subscription Edition does not support In place upgrade.

We will need Install and configure SharePoint Server Subscription Edition on the new farm

SQL Server

SharePoint Server Subscription Edition (SE) supports the following versions of SQL Server:

1. SQL Server 2019 (Standard or Enterprise edition)
2. SQL Server 2022 (Standard or Enterprise edition)

We will need to create new SQL server instance for the 

SharePoint Server Subscription Edition (SE) farm and then backup and restore DBs from old to new SQL instance

If we already have SQL Server 2019 in place it will streamline the upgrade process, as it's supported by both SharePoint versions.

Updated Stored Procedure

Here is how the updated SP_GetMissingTimesheets stored procedure might look, incorporating the overlap logic:

sql
ALTER PROCEDURE [TT].[SP_GetMissingTimesheets]
AS
BEGIN
    DECLARE @Today DATE = GETDATE();
    DECLARE @WeekStartDate DATE;
    DECLARE @WeekEndDate DATE;

    -- Clear previous entries in the MissingTimesheets table (optional)
    -- DELETE FROM MissingTimesheets WHERE Created < DATEADD(WEEK, -5, GETDATE());

    -- Loop through the past 5 weeks
    DECLARE @WeekOffset INT = 1;

    -- Truncate the MissingTimesheets table to clear any existing data
    TRUNCATE TABLE MissingTimesheets;

    WHILE @WeekOffset < 5
    BEGIN
        -- Calculate the start and end dates of the week using the function
        SELECT @WeekStartDate = WeekStartDate, @WeekEndDate = WeekEndDate
        FROM Fun_GetWeekStartEndDate(@Today, -@WeekOffset);

        -- Insert missing timesheets into the permanent table, excluding records where date ranges do not overlap
        INSERT INTO MissingTimesheets (EmployeeEmail, WeekStartDate, WeekEndDate)
        SELECT DISTINCT E.EmployeeEmail, @WeekStartDate, @WeekEndDate
        FROM Resource E
        LEFT JOIN TimeSheet TS ON E.EmployeeNWName = TS.EmployeeNWName
            AND TS.TimeSheetStartDate = @WeekStartDate
            AND TS.TimeSheetEndDate = @WeekEndDate
        WHERE (TS.TimeSheetID IS NULL OR TS.TimeSheetStatus IN ('Not Started', 'Saved', 'Returned'))
          AND EXISTS (
              SELECT 1
              FROM Task T
              JOIN ProjectResource PR ON PR.ProjectID = T.ProjectID
              WHERE PR.EmployeeNWName = E.EmployeeNWName
                AND (
                    -- Check overlap between WeekStartDate-WeekEndDate and TaskStartDate-TaskEndDate
                    (@WeekStartDate <= ISNULL(T.TaskEndDate, '9999-12-31') AND @WeekEndDate >= T.TaskStartDate)
                    AND
                    -- Check overlap between WeekStartDate-WeekEndDate and ResourceStartDate-ResourceEndDate
                    (@WeekStartDate <= ISNULL(PR.ResourceEndDate, '9999-12-31') AND @WeekEndDate >= PR.ResourceStartDate)
                    AND
                    -- Check overlap between TaskStartDate-TaskEndDate and ResourceStartDate-ResourceEndDate
                    (T.TaskStartDate <= ISNULL(PR.ResourceEndDate, '9999-12-31') AND ISNULL(T.TaskEndDate, '9999-12-31') >= PR.ResourceStartDate)
                )
          );

        SET @WeekOffset = @WeekOffset + 1;
    END

    -- Select and format the missing timesheets
    SELECT 
        ROW_NUMBER() OVER (ORDER BY E.EmployeeEmail) AS MissingTimesheetID,
        E.EmployeeNWName,
        E.EmployeeEmail,
        E.EmployeeDisplayName,
        '<ul>' + STRING_AGG('<li>' + CONVERT(VARCHAR, M.WeekStartDate, 101) + ' - ' + CONVERT(VARCHAR, M.WeekEndDate, 101) + '</li>', '') + '</ul>' AS MissingWeeks
    FROM 
        MissingTimesheets M
    JOIN Resource E ON M.EmployeeEmail = E.EmployeeEmail
    WHERE 
        M.Created >= DATEADD(WEEK, -5, @Today)
    GROUP BY 
        E.EmployeeNWName,
        E.EmployeeEmail,
        E.EmployeeDisplayName;

END;

Stored Procedure

sql
CREATE PROCEDURE SP_AddEmployeeToGeneralProjects
    @EmployeeNWName NVARCHAR(100)
AS
BEGIN
    -- Declare a variable to hold the ProjectID
    DECLARE @ProjectID INT;

    -- Cursor to iterate through projects with Portfolio 'General'
    DECLARE ProjectCursor CURSOR FOR
    SELECT ProjectID
    FROM Project
    WHERE PortfolioID = (SELECT PortfolioID FROM Portfolio WHERE PortfolioName = 'General');

    OPEN ProjectCursor;
    FETCH NEXT FROM ProjectCursor INTO @ProjectID;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Check if EmployeeNWName is already in the ProjectResource table for the current ProjectID
        IF NOT EXISTS (
            SELECT 1
            FROM ProjectResource
            WHERE ProjectID = @ProjectID
            AND EmployeeNWName = @EmployeeNWName
        )
        BEGIN
            -- Insert the EmployeeNWName into the ProjectResource table
            INSERT INTO ProjectResource (ProjectID, EmployeeNWName, ResourceStartDate, Active)
            VALUES (@ProjectID, @EmployeeNWName, GETDATE(), 1);  -- Assuming Active and StartDate
        END

        FETCH NEXT FROM ProjectCursor INTO @ProjectID;
    END

    CLOSE ProjectCursor;
    DEALLOCATE ProjectCursor;
END;

Updated Stored Procedure

sql
ALTER PROCEDURE [TT].[SP_GetMissingTimesheets]
AS
BEGIN
    DECLARE @Today DATE = GETDATE();
    DECLARE @WeekStartDate DATE;
    DECLARE @WeekEndDate DATE;

    -- Clear previous entries in the MissingTimesheets table (optional)
    -- DELETE FROM MissingTimesheets WHERE Created < DATEADD(WEEK, -5, GETDATE());

    -- Loop through the past 5 weeks
    DECLARE @WeekOffset INT = 1;

    -- Truncate the MissingTimesheets table to clear any existing data
    TRUNCATE TABLE MissingTimesheets;

    WHILE @WeekOffset < 5
    BEGIN
        -- Calculate the start and end dates of the week using the function
        SELECT @WeekStartDate = WeekStartDate, @WeekEndDate = WeekEndDate
        FROM Fun_GetWeekStartEndDate(@Today, -@WeekOffset);

        -- Insert missing timesheets into the permanent table
        INSERT INTO MissingTimesheets (EmployeeEmail, WeekStartDate, WeekEndDate)
        SELECT E.EmployeeEmail, @WeekStartDate, @WeekEndDate
        FROM Resource E
        LEFT JOIN TimeSheet TS
        ON E.EmployeeNWName = TS.EmployeeNWName
        AND TS.TimeSheetStartDate = @WeekStartDate
        AND TS.TimeSheetEndDate = @WeekEndDate
        WHERE TS.TimeSheetID IS NULL
           OR TS.TimeSheetStatus IN ('Not Started', 'Saved', 'Returned');

        SET @WeekOffset = @WeekOffset + 1;
    END

    -- Select and format the missing timesheets
    SELECT 
        ROW_NUMBER() OVER (ORDER BY EmployeeEmail) AS MissingTimesheetID,
        E.EmployeeNWName,
        E.EmployeeEmail,
        E.EmployeeDisplayName,
        '<ul>' + STRING_AGG('<li>' + CONVERT(VARCHAR, M.WeekStartDate, 101) + ' - ' + CONVERT(VARCHAR, M.WeekEndDate, 101) + '</li>', '') + '</ul>' AS MissingWeeks
    FROM 
        MissingTimesheets M
    JOIN Resource E ON M.EmployeeEmail = E.EmployeeEmail
    WHERE 
        M.Created >= DATEADD(WEEK, -5, @Today)
    GROUP BY 
        E.EmployeeNWName,
        E.EmployeeEmail,
        E.EmployeeDisplayName;

END;

sql
CREATE PROCEDURE SP_FindMissingTimesheets
AS
BEGIN
    DECLARE @Today DATE = GETDATE();
    DECLARE @WeekStartDate DATE;
    DECLARE @WeekEndDate DATE;

    -- Clear previous entries in the MissingTimesheets table (optional)
    -- DELETE FROM MissingTimesheets WHERE Created < DATEADD(WEEK, -5, GETDATE());

    -- Loop through the past 5 weeks
    DECLARE @WeekOffset INT = 0;

    WHILE @WeekOffset < 5
    BEGIN
        -- Calculate the start and end dates of the week using the function
        SELECT @WeekStartDate = WeekStartDate, @WeekEndDate = WeekEndDate
        FROM dbo.Fun_GetWeekStartEndDate(@Today, -@WeekOffset);

        -- Insert missing timesheets into the permanent table
        INSERT INTO MissingTimesheets (EmployeeEmail, WeekStartDate, WeekEndDate)
        SELECT E.EmployeeEmail, @WeekStartDate, @WeekEndDate
        FROM Resource E
        LEFT JOIN TimeSheet TS
        ON E.EmployeeNWName = TS.EmployeeNWName
        AND TS.TimeSheetStartDate = @WeekStartDate
        AND TS.TimeSheetEndDate = @WeekEndDate
        WHERE TS.TimeSheetID IS NULL
           OR TS.Status IN ('Not Started', 'Saved');

        SET @WeekOffset = @WeekOffset + 1;
    END

    -- Select and format the missing timesheets
    SELECT 
        ROW_NUMBER() OVER (ORDER BY EmployeeEmail) AS ID,
        EmployeeEmail,
        '<ul>' + STRING_AGG('<li>' + CONVERT(VARCHAR, WeekStartDate, 101) + ' - ' + CONVERT(VARCHAR, WeekEndDate, 101) + '</li>', '') + '</ul>' AS MissingWeeks
    FROM 
        MissingTimesheets
    WHERE 
        Created >= DATEADD(WEEK, -5, @Today)
    GROUP BY 
        EmployeeEmail;
END;

__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT__PRESENT