Hi, I will be eligible for an in-person interview starting January 4, 2025.
I’ve noticed that obtaining a Dropbox appointment is challenging and that the visa processing through Dropbox takes longer.
Should I schedule a Dropbox appointment now, or wait until January 4, 2025, for an in-person interview?
Are in-person interview dates available, and is the visa processed faster compared to Dropbox? If anyone has recently attended an in-person interview, please share your experience. Thank you!
sqlCREATE 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:
Sharepoint server upgrade
SQL Server
SharePoint Server Subscription Edition (SE) supports the following versions of SQL Server:
Here is how the updated SP_GetMissingTimesheets stored procedure might look, incorporating the overlap logic:
sqlALTER 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;sqlCREATE 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;sqlALTER 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;sqlCREATE 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;K2 workflow instances bloats the database over the time period and at some point you would want to delete the old workflow instance which are completed or in error status.
Unfortunately, there is no way to delete specific instances of workflow, in other words, you could not select specific instance and delete them. However, you can delete then in a bulk.
For instance, you would want to delete old workflow instances, which are completed and occupying the significant space in database. Instances where you are not going to report anything. Or, you just want to delete then as a part of clean up activity.
You can follow below steps to do so.
6. Check “Delete all historical (log) data for selected versions”
Be careful though, “Delete all historical (log) data for selected versions” option deletes all workflow instances including active as well. Basically, it wipes out all the instances for that version from k2 database.
0 = Available
|
The item has not been opened yet
|
1 = Open
|
The item was previously opened by the current user
|
2 = Allocated
|
The item was opened by another user
|
3 = Sleep
|
The item was set to sleep for a specified amount of time
|
4 = Completed
|
The item was already completed by this or another user
|
