JT

ery to Find C_Kwh Decrease (Discrepancies)

WITH Ranked AS (
    SELECT 
        Price_ID,
        Term,
        C_Kwh,
        ROW_NUMBER() OVER (PARTITION BY Price_ID ORDER BY Term ASC)  AS rn_asc,
        ROW_NUMBER() OVER (PARTITION BY Price_ID ORDER BY Term DESC) AS rn_desc
    FROM PricingTable
),
FirstLast AS (
    SELECT 
        Price_ID,
        MAX(CASE WHEN rn_asc = 1 THEN Term END) AS FirstTerm,
        MAX(CASE WHEN rn_desc = 1 THEN Term END) AS LastTerm,
        MAX(CASE WHEN rn_asc = 1 THEN C_Kwh END) AS First_C_Kwh,
        MAX(CASE WHEN rn_desc = 1 THEN C_Kwh END) AS Last_C_Kwh
    FROM Ranked
    GROUP BY Price_ID
)
SELECT
    Price_ID,
    FirstTerm,
    First_C_Kwh,
    LastTerm,
    Last_C_Kwh,
    CASE 
        WHEN Last_C_Kwh > First_C_Kwh THEN '✅ Increased'
        WHEN Last_C_Kwh = First_C_Kwh THEN '⚠️ Same Value'
        WHEN Last_C_Kwh < First_C_Kwh THEN '❌ Decreased'
        ELSE 'N/A'
    END AS ComparisonResult
FROM FirstLast
ORDER BY Price_ID;


WITH Ranked AS (
    SELECT 
        Price_ID,
        Term,
        C_Kwh,
        ROW_NUMBER() OVER (PARTITION BY Price_ID ORDER BY Term ASC)  AS rn_asc,
        ROW_NUMBER() OVER (PARTITION BY Price_ID ORDER BY Term DESC) AS rn_desc
    FROM PricingTable
)
SELECT 
    Price_ID,
    Term,
    C_Kwh,
    CASE 
        WHEN rn_asc = 1 THEN 'First Term'
        WHEN rn_desc = 1 THEN 'Last Term'
    END AS TermPosition
FROM Ranked
WHERE rn_asc = 1 OR rn_desc = 1
ORDER BY Price_ID, Term;


SELECT 
    t1.Price_ID,
    t1.Term,
    t1.C_Kwh,
    t2.C_Kwh AS Prev_C_Kwh
FROM your_table_name t1
LEFT JOIN your_table_name t2 
    ON t1.Price_ID = t2.Price_ID
    AND t1.Term = t2.Term + 1
WHERE t2.C_Kwh IS NOT NULL
  AND t1.C_Kwh < t2.C_Kwh
ORDER BY t1.Price_ID, t1.Term;




WITH OrderedData AS (
    SELECT 
        Price_ID,
        Term,
        C_Kwh,
        LAG(C_Kwh) OVER (PARTITION BY Price_ID ORDER BY Term) AS Prev_C_Kwh
    FROM your_table_name
)
SELECT 
    Price_ID,
    Term,
    C_Kwh,
    Prev_C_Kwh
FROM OrderedData
WHERE Prev_C_Kwh IS NOT NULL
  AND C_Kwh < Prev_C_Kwh
ORDER BY Price_ID, Term;

Knowledge

Subject: Request for In-Person Meeting to Discuss Promotion Consideration

Dear [Manager’s Manager’s Name],

I hope you are doing well. I will be visiting the office on [insert date(s)] and would greatly appreciate the opportunity to meet with you in person to discuss my career progression and promotion consideration.

With over 16 years of experience in process automation, system integration, and enterprise application development, I have consistently met expectations, exceeded expectations in the past year, and taken on expanded responsibilities that delivered measurable impact in the following areas:

• Enterprise Value: Delivered numerous enterprise applications end-to-end, with the New Time Tracker serving as a key example — achieving a 30x performance improvement, removing operational bottlenecks, and establishing a scalable, modernized foundation.

• Leadership & Governance: Helped the team understand K2 architecture and capabilities, re-engineered applications to align with best practices, streamlined the development process, and established governance standards for K2 forms and workflows. Recently mentored an intern on K2 and SharePoint, enabling them to quickly contribute and gain hands-on experience with enterprise platforms.

• Operational Excellence: Partnered with the Operations team during multiple K2 upgrades, fixed several complex issues, and currently leading the K2 and SharePoint upgrade to Subscription Edition, ensuring a smooth transition and alignment with modernization goals.

I would value your perspective on my contributions and your guidance on the path forward, including preparation for future enterprise-level responsibilities.

As you’ve highlighted the importance of two-way communication, I would like to meet in person during my upcoming office visit. I can adjust my schedule based on your availability.

Thank you very much for your time and consideration.

Best regards,
[Your Full Name]

__PRESENT

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

Delete k2 workflow instances in bulk

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.

1. If you have 4.7 vision installed you can go to management workspace. For older version, you can follow similar steps
2. Expand workflow server node.
3. Go to your category to find workflow for which you want to delete workflow instances.
4. Click on versions.
5. Select version you want to delete.

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.

Different ways to create smartobjects from SQL service instance

I asked the question to k2 support, What is the difference between Create and generate smartobject? and they provided below answer, thought would be helpful to share.

There are three different options available to create/manage SmartObject from a SQL Service Instance via SmartObject Service Tester tool:

a.  From service instance > Create SmartObjects
- manages the creation and updates of all SQL SmartObjects of the service instance as well as SmartObject's category as a set (no control over displayname and guid)
- allows for the appending of a string to all of the SmartObject system name (no control over the overall displayname, systemname, guid)
- can be used to update all SmartObject as a set if there are changes to the database objects schema(tables, views, sprocs)
- displayname, systemname and guid will remain the same (if targeting the same set of SmartObject)

b.  From service instance > Generate SmartObjects
- manages the creation and updates of all SQL SmartObjects of the service instance as a set (no control over displayname, systemname, category, guid)
- can be used to update all SmartObject as a set if there are changes to the database objects schema (tables, views, sprocs)
- displayname, systemname and guid will remain the same

c.  From Service Object > Create SmartObject (singular)
- allows for managing of the SystemName/DisplayName, GUID, and category SmartObject is created in
- can be used to regenerate/update the existing SmartObject by targeting the 'SystemName' of the SmartObject (with the 'Check Name' button) and returning the current GUID for this SmartObject (with the 'Get Existing Guid' button) before publishing it again
** if not returning the existing name and GUID, a new SmartObject with different value will be created

* all three options mainly used if there are NO customization with the SmartObject and the default generated functionality is desired; as regenerating the SmartObject with the same SystemName and GUID will remove any customization previously done; if SmartObject was customized, manually changes to these objects in the designer that they were designed in (K2 Designer, K2 Studio, K2 for Visual Studio) will preserve the customization.

K2 Interview Questions: Workspace, Part 1 (Intermidiate)

All K2 Interview Questions

What are Worklist Item/task statuses?

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

In a majority of cases, once a user has opened the task, the task status is Open for that user and Allocated for everyone else.

K2 Interview Questions: Workflows, Part 2 (Advance)

All K2 Interview Questions

How do you fix the process in errors?
1. Sometimes the error is because of network issues, some connection problem or no proper inputs. If you just go to the error profile in the workspace and retry the error it fixes the problem.
2. If above approach does not resolve the error you can use the K2 Process Management tool in Visual Studio.
 As shown in the figure below. you can select process clicking View->K2 Process Management tool in visual studio. Select the process in error and open the process and fix the error.

After fixing the error, go back to the k2 process management and click redeploy the process.

You can provide the label and deploy the process. It will fix that instance of the process. Then you can fix the processes with similar error hitting the retry button and selecting the fixed version of the process.

How do you debug the process if  the Visual studio is not installed on the K2 box?

http://spandk2.blogspot.com/2012/11/how-to-debug-k2-process.html

K2 Interview Questions