none
Project Server 2019 Submitted but not approved workhours RRS feed

  • Question

  • Hello colleagues!

    Asking for your help once again.

    Look, I need to query Resource's workhours what was submitted to Status Managers  but not approved by them.

    To find transactions I'm using this query

    SELECT DISTINCT
    	wr1.RES_NAME,
    	trans.CREATED_DATE,
    	trans.ASSN_TRANS_UPDATE_DATE AS UPDATE_DATE,
    	trans.ASSN_TRANS_STATE_ENUM AS [STATE],
    	trans.ASSN_TRANS_TYPE_ENUM AS [TYPE],
    	trans.ASSN_TRANS_ACTION_ENUM AS [ACTION],
    	trans.ASSN_TRANS_ERROR_ENUM AS [ERROR],
    	wa.TASK_NAME,
    	wr2.RES_NAME AS Approval_Manager,
    	wp.PROJ_NAME,
    	wr3.RES_NAME AS Project_Owner
    FROM
    	--		dbo.MSP_ASSIGNMENTS wa
    			pjpub.MSP_ASSIGNMENTS_SUBMITTED wa
    	--		dbo.MSP_ASSIGNMENTS_SAVED wa
    	LEFT JOIN 
    			pjpub.MSP_PROJECTS wp
    		ON 
    			(wa.PROJ_UID = wp.PROJ_UID)
    	LEFT JOIN 
    			pjpub.MSP_ASSIGNMENT_TRANSACTIONS trans
    		ON 
    			(trans.ASSN_UID = wa.ASSN_UID)
    	LEFT JOIN 
    			pjpub.MSP_RESOURCES wr3
    		ON 
    			(wp.WRES_UID = wr3.RES_UID)
    	LEFT JOIN 
    			pjpub.MSP_RESOURCES wr2
    		ON 
    			(wa.WRES_UID_MANAGER = wr2.RES_UID)
    	LEFT JOIN 
    			pjpub.MSP_RESOURCES wr1
    		ON 
    			(wa.RES_UID = wr1.RES_UID)
    WHERE 
    			1=1
    	--trans.ASSN_TRANS_STATE_ENUM in (0, 1, 2) and 
    	AND wr1.RES_NAME = 'Kucherovsky Andrey'
    	--AND wr2.RES_NAME = 'Shuklin Roman'
    	--and wr3.RES_NAME = 'Dinh Hana'
    	--and wp.PROJ_NAME = '__'
    	AND trans.ASSN_TRANS_ACTION_ENUM = 0
    	--and trans.ASSN_TRANS_ERROR_ENUM > 0
    	--and trans.ASSN_TRANS_TYPE_ENUM = 4
    	--and trans.CREATED_DATE like ('%2014-10-30%')
    	--and wa.TASK_NAME like ('___')
    --order by wp.PROJ_NAME
    --order by wr2.RES_NAME = 'maiorov andrey'
    ORDER BY 
    	trans.CREATED_DATE DESC

    In result this query shows me the Tasks that was not approved by Status Manager.

    But how much hours were sent to Status Mananger - where I can find it for query?

    Please, maybe somebody knows that.


    MCSA: Security; MCTP: Microsoft ISA 2006


    Tuesday, July 7, 2020 2:38 PM

Answers

  • Hello colleagues!

    Finally, it were done!

    Total Actual Work report with TimeByDay and Grand Total.

    Ofcource, it will be good to implement Monthy Totals, but I think its more SQL task, than MSP, so I've started thread about that here https://social.technet.microsoft.com/Forums/en-US/4066fa74-ce65-4468-842d-62b71fa16095/monthly-subtotals-in-the-report?forum=sqlreportingservices.

    Now the report can show to us Timesheet Data, Submitted and Approved hours.

    Main script:

    USE WSS_Content
    --------------------------------------------------------------------------------------------------------
    -- DECLARE TABLES
    --------------------------------------------------------------------------------------------------------
    DECLARE @DataBegin DATETIME, @DataEnd DATETIME
    DECLARE @t TABLE (ASSN_TRANS_UID UNIQUEIDENTIFIER, ASSN_TRANS_ACTION_ENUM INT, ASSN_TRANS_STATE_ENUM INT, ASSN_TRANS_TYPE_ENUM INT) 
    DECLARE @m TABLE (ASSN_UID UNIQUEIDENTIFIER, RES_SAVED_DATE INT, h FLOAT, ASSN_TRANS_SUBMIT_DATE DATETIME, ASSN_TRANS_ACTION_ENUM INT, ASSN_TRANS_STATE_ENUM INT, ASSN_TRANS_TYPE_ENUM INT)
    DECLARE @s TABLE (ASSN_UID UNIQUEIDENTIFIER, RES_SAVED_DATE DATE, h FLOAT, ASSN_TRANS_SUBMIT_DATE DATETIME, ASSN_TRANS_ACTION_ENUM INT, ASSN_TRANS_STATE_ENUM INT, ASSN_TRANS_TYPE_ENUM INT)
    DECLARE @date TABLE (ASSN_UID UNIQUEIDENTIFIER, RES_SAVED_DATE DATE, ASSN_TRANS_SUBMIT_DATE DATETIME)
    DECLARE @fin TABLE (ASSN_UID UNIQUEIDENTIFIER, RES_SAVED_DATE DATE, h FLOAT, ASSN_TRANS_SUBMIT_DATE DATETIME, TASK_NAME VARCHAR(MAX), PROJ_UID UNIQUEIDENTIFIER, RES_UID UNIQUEIDENTIFIER, ASSN_TRANS_ACTION_ENUM INT, ASSN_TRANS_STATE_ENUM INT, ASSN_TRANS_TYPE_ENUM INT)
    DECLARE @report TABLE (AssignmentUID UNIQUEIDENTIFIER, TimeByDay DATE, ProjectUID UNIQUEIDENTIFIER, TaskUID UNIQUEIDENTIFIER, TaskName VARCHAR(MAX), AssignmentActualWork FLOAT, ResourceUID UNIQUEIDENTIFIER)
    DECLARE @ts TABLE (ResourceUID UNIQUEIDENTIFIER, TimesheetWork FLOAT, TimeByDay DATE, ProjectUID UNIQUEIDENTIFIER, TaskName VARCHAR(MAX))
    DECLARE @upselect TABLE (ASSN_UID UNIQUEIDENTIFIER, RES_SAVED_DATE DATE, TASK_NAME VARCHAR(MAX), PROJ_UID UNIQUEIDENTIFIER, RES_UID UNIQUEIDENTIFIER)
    
    SET DATEFORMAT dmy 
    SET @DataBegin = @DateBeginParameter 
    SET @DataEnd = @DateFinishParameter
    
    --------------------------------------------------------------------------------------------------------
    -- COLLECTING SUBMITTED DATA
    --------------------------------------------------------------------------------------------------------
    
    INSERT @t 
    SELECT 
    		at.ASSN_TRANS_UID, 
    		at.ASSN_TRANS_ACTION_ENUM,
    		at.ASSN_TRANS_STATE_ENUM,
    		at.ASSN_TRANS_TYPE_ENUM
    FROM 
    		(SELECT 
    				ASSN_UID, 
    				ASSN_TRANS_UID, 
    				ASSN_TRANS_ACTION_ENUM,
    				ASSN_TRANS_STATE_ENUM,
    				ASSN_TRANS_TYPE_ENUM
    		FROM 
    				pjpub.MSP_ASSIGNMENT_TRANSACTIONS 
    		WHERE 
    					(1=1)
    				AND
    					ASSN_TRANS_SUBMITTER_RES_UID IN (@ResourceUIDParameter)
    --				AND 
    --					ASSN_TRANS_STATE_ENUM IN ('1', '2', '3')
    		) AS at 
    	INNER JOIN 
    			(SELECT 
    					ISNULL(ass.ASSN_UID, a.ASSN_UID) [ASSN_UID] 
    			FROM 
    					pjpub.MSP_ASSIGNMENTS_SAVED ass 
    				FULL JOIN 
    						pjpub.MSP_ASSIGNMENTS a 
    					ON 
    						(ass.ASSN_UID = a.ASSN_UID) 
    			WHERE 
    					ISNULL(ass.ASSN_FINISH_DATE, a.ASSN_FINISH_DATE) >= @DataBegin 
    				AND 
    					ISNULL(ass.ASSN_START_DATE, a.ASSN_START_DATE) < @DataEnd+1 
    			) AS tm 
    		ON 
    			at.ASSN_UID = tm.ASSN_UID 
    
    --------------------------------------------------------------------------------------------------------
    -- SENDING DATA TO THE FUNCTION
    --------------------------------------------------------------------------------------------------------
    
    INSERT 
    			@m 
    SELECT 
    			f.*, 
    			t.ASSN_TRANS_ACTION_ENUM,
    			t.ASSN_TRANS_STATE_ENUM,
    			t.ASSN_TRANS_TYPE_ENUM
    FROM 
    			@t t 
    		CROSS APPLY 
    			dbo.SPK_ASSN_CHANGE_DATA (t.ASSN_TRANS_UID) f 
    WHERE
    			t.ASSN_TRANS_ACTION_ENUM IN ('0', '1')
    
    --------------------------------------------------------------------------------------------------------
    -- GETTING DATA TO COVERT RES_SAVED_DATE AND SORTING
    --------------------------------------------------------------------------------------------------------
    INSERT 
    			@s
    SELECT
    			mt.ASSN_UID,
    			DATEADD(dd,RES_SAVED_DATE, '19831231') AS RES_SAVED_DATE, 
    			h AS h, 
    			ASSN_TRANS_SUBMIT_DATE,
    			mt.ASSN_TRANS_ACTION_ENUM,
    			ASSN_TRANS_STATE_ENUM,
    			ASSN_TRANS_TYPE_ENUM
    FROM
    			@m mt
    WHERE 
    			RES_SAVED_DATE >= DATEDIFF (DD,'19831231', @DataBegin) 
    		AND 
    			RES_SAVED_DATE <= DATEDIFF (DD,'19831231', @DataEnd)
    
    --------------------------------------------------------------------------------------------------------
    -- GETTING LAST TRANSACTION (SORTING)
    --------------------------------------------------------------------------------------------------------
    INSERT @date
    SELECT
    			ASSN_UID,
    			RES_SAVED_DATE, 
    			MAX(ASSN_TRANS_SUBMIT_DATE) AS ASSN_TRANS_SUBMIT_DATE
    FROM
    			@s st
    
    GROUP BY
    			ASSN_UID,
    			RES_SAVED_DATE
    
    --------------------------------------------------------------------------------------------------------
    -- LIST SORTED DATA
    --------------------------------------------------------------------------------------------------------
    INSERT @fin
    SELECT		
    			dt.ASSN_UID,
    			dt.RES_SAVED_DATE,
    			st.h,
    			dt.ASSN_TRANS_SUBMIT_DATE,
    			Saved.TASK_NAME,
    			Saved.PROJ_UID,
    			Saved.RES_UID,
    			st.ASSN_TRANS_ACTION_ENUM,
    			ASSN_TRANS_STATE_ENUM,
    			ASSN_TRANS_TYPE_ENUM
    FROM
    			@date dt
    	INNER JOIN
    			@s st
    		ON	
    			dt.RES_SAVED_DATE = st.RES_SAVED_DATE
    		AND
    			dt.ASSN_TRANS_SUBMIT_DATE = st.ASSN_TRANS_SUBMIT_DATE
    	LEFT JOIN
    			pjpub.MSP_ASSIGNMENTS_SAVED Saved
    		ON
    			st.ASSN_UID = Saved.ASSN_UID
    WHERE 
    			dt.RES_SAVED_DATE >= @DataBegin 
    		AND 
    			dt.RES_SAVED_DATE <= @DataEnd
    GROUP BY
    			dt.ASSN_UID,
    			dt.RES_SAVED_DATE,
    			st.h,
    			dt.ASSN_TRANS_SUBMIT_DATE,
    			Saved.TASK_NAME,
    			Saved.PROJ_UID,
    			Saved.RES_UID,
    			st.ASSN_TRANS_ACTION_ENUM,
    			ASSN_TRANS_STATE_ENUM,
    			ASSN_TRANS_TYPE_ENUM
    
    --------------------------------------------------------------------------------------------------------
    -- PREPARING REPORTING DATA
    --------------------------------------------------------------------------------------------------------
    
    INSERT @report
    SELECT
    			AssView.AssignmentUID AS AssignmentUID,
    			AssView.TimeByDay AS TimeByDay, 
    			ProjView.ProjectUID AS ProjectUID,
    			TaskView.TaskUID AS TaskUID,
    			TaskView.TaskName AS TaskName,
    			AssignmentActualWork = SUM(AssView.AssignmentActualWork),
    			ResView.ResourceUID AS ResourceUID
    FROM
    			dbo.MSP_EpmAssignmentByDay_Reports_UserView AssView
    	LEFT JOIN 
    			pjrep.MSP_EpmAssignment AssTable
    		ON 
    			AssView.AssignmentUID = AssTable.AssignmentUID
    	LEFT JOIN 
    			dbo.MSP_CustomViewReports_EpmResource_UserView ResView
    		ON 
    			AssTable.ResourceUID = ResView.ResourceUID
    	LEFT JOIN 
    			dbo.MSP_CustomViewReports_EpmProject_UserView ProjView
    		ON 
    			AssTable.ProjectUID = ProjView.ProjectUID
    	LEFT JOIN 
    			dbo.MSP_CustomViewReports_EpmTask_UserView TaskView
    		ON 
    			AssTable.TaskUID = TaskView.TaskUID
    WHERE
    			(1 = 1)
    		AND
    			ResView.ResourceUID IN (@ResourceUIDParameter)
    		AND 
    			(TimeByDay > @DataBegin AND TimeByDay < @DataEnd)
    		AND 
    			(AssView.AssignmentActualWork > 0.0)
    GROUP BY 
    			AssView.AssignmentUID,
    			AssView.TimeByDay,
    			ResView.ResourceUID,
    			ProjView.ProjectUID,
    			TaskView.TaskUID,
    			TaskView.TaskName
    
    --------------------------------------------------------------------------------------------------------
    -- GETTING DATA FROM TIMESHEETS
    --------------------------------------------------------------------------------------------------------
    INSERT @ts
    SELECT 
    			TSResTable.ResourceUID AS ResourceUID,
    			SUM(TSActTable.ActualWorkBillable) 
    				+ SUM(TSActTable.ActualWorkNonBillable)
    				+ SUM(TSActTable.ActualOvertimeWorkBillable)
    				+ SUM(TSActTable.ActualOvertimeWorkNonBillable)
    				AS TimesheetWork,
    			TSActTable.TimeByDay AS TimeByDay,
    --			ISNULL(ts.TASK_PUBLISHED_UID,TSTaskTable.TaskUID) AS TaskUID,
    			TSProjTable.ProjectUID AS ProjectUID,
    			ISNULL(ts.TASK_NAME,TSTaskTable.TaskName) AS TaskName
    FROM  
    			dbo.MSP_CustomViewReports_EpmResource_UserView ResView
    	LEFT OUTER JOIN 
    			pjrep.MSP_TimesheetResource TSResTable
    	INNER JOIN 
    			pjrep.MSP_TimesheetActual TSActTable
    		ON 
    			TSResTable.ResourceNameUID = TSActTable.LastChangedResourceNameUID 
    		ON 
    			ResView.ResourceUID = TSResTable.ResourceUID 
    	INNER JOIN 
    			pjrep.MSP_TimesheetLine TSLineTable
    		ON 
    			TSActTable.TimesheetLineUID = TSLineTable.TimesheetLineUID
    	LEFT JOIN 
    			pjrep.MSP_TimesheetTask TSTaskTable
    		ON  
    			TSTaskTable.TaskNameUID = TSLineTable.TaskNameUID
    	LEFT JOIN 
    			pjpub.MSP_TASKS_SAVED AS ts
    		ON 
    			ts.TASK_UID = TSTaskTable.TaskUID 
    	LEFT JOIN 
    			pjrep.MSP_TimesheetProject TSProjTable
    		ON 
    			TSProjTable.ProjectNameUID = TSLineTable.ProjectNameUID
    WHERE  
    			TSResTable.ResourceUID IN (@ResourceUIDParameter)
    		AND 
    			(TSActTable.TimeByDay >= (@DataBegin) AND TSActTable.TimeByDay <= (@DataEnd))
    GROUP BY 
    			TSResTable.ResourceUID,
    			ResView.ResourceName,
    			TSActTable.TimeByDay,
    			TSLineTable.TaskNameUID,
    			TSLineTable.ProjectNameUID,
    			TSProjTable.ProjectUID,
    			ts.TASK_PUBLISHED_UID,
    			ts.TASK_NAME,
    			TSProjTable.ProjectName,
    			TSTaskTable.TaskUID,
    			TSTaskTable.TaskName
    ORDER BY 
    			TSActTable.TimeByDay DESC
    
    --------------------------------------------------------------------------------------------------------
    -- UNION ALL PUB AND REP UIDS
    --------------------------------------------------------------------------------------------------------
    
    INSERT @upselect
    SELECT DISTINCT
    			ASSN_UID, 
    			RES_SAVED_DATE, 
    			TASK_NAME,
    			PROJ_UID,
    			RES_UID
    FROM
    			@fin
    UNION
    --			ALL
    SELECT
    			AssignmentUID, 
    			TimeByDay, 
    			TaskName,
    			ProjectUID,
    			ResourceUID
    FROM
    			@report
    
    --------------------------------------------------------------------------------------------------------
    -- GENERATING FINAL REPORT
    --------------------------------------------------------------------------------------------------------
    SELECT --DISTINCT
    --			*
    			upsel.PROJ_UID AS P_UID,
    			ProjView.ProjectName AS ProjectName,
    			upsel.RES_UID AS R_UID,
    			ResView.ResourceName AS ResourceName,
    			upsel.RES_SAVED_DATE AS TBD,
    			upsel.ASSN_UID AS A_UID,
    			upsel.TASK_NAME AS TaskName,
    			ts.TimesheetWork AS TH,
    			tfin.h AS SH,
    			AssView.AssignmentActualWork AS AH
    FROM								
    			@upselect upsel
    	LEFT JOIN
    			@fin tfin
    		ON 
    			upsel.ASSN_UID = tfin.ASSN_UID
    		AND	
    			upsel.RES_SAVED_DATE = tfin.RES_SAVED_DATE
    	LEFT JOIN
    			@report AssView
    		ON
    			upsel.ASSN_UID = AssView.AssignmentUID
    		AND
    			upsel.RES_SAVED_DATE = AssView.TimeByDay
    	LEFT JOIN
    			@ts ts
    		ON
    			upsel.PROJ_UID = ts.ProjectUID
    		AND
    			upsel.RES_SAVED_DATE = ts.TimeByDay
    		AND
    			upsel.RES_UID = ts.ResourceUID
    		AND
    			upsel.TASK_NAME = ts.TaskName
    	LEFT JOIN
    			dbo.MSP_CustomViewReports_EpmResource_UserView ResView
    		ON
    			upsel.RES_UID = ResView.ResourceUID
    	LEFT JOIN
    			dbo.MSP_CustomViewReports_EpmProject_UserView ProjView
    		ON
    			upsel.PROJ_UID = ProjView.ProjectUID
    ORDER BY
    			upsel.RES_SAVED_DATE DESC

    As you can see, I'm using Task's Names instead of Task's UID.

    Why:

    Tasks have different UIDs in the published and reporting structures (tables)!

    The binary data decoding function (table-valued) dbo.SPK_ASSN_CHANGE_DATA

    USE [WSS_Content]
    GO
    /****** Object:  UserDefinedFunction [dbo].[SPK_ASSN_CHANGE_DATA]    Script Date: 28.07.2020 12:37:42 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
     ALTER FUNCTION [dbo].[SPK_ASSN_CHANGE_DATA] (@ASSN_TRANS_UID UNIQUEIDENTIFIER) 
     RETURNS @SPK_ASSN_CHANGE_DATA TABLE (ASSN_UID UNIQUEIDENTIFIER, RES_SAVED_DATE INT, h FLOAT, ASSN_TRANS_SUBMIT_DATE DATETIME) -- RES_SAVED_DATE INT changed to RES_SAVED_DATE DATETIME
     AS 
     BEGIN 
     DECLARE @BUFFER TABLE (NUMBER INT IDENTITY, ASSN_UID UNIQUEIDENTIFIER, RES_SAVED_DATE INT, h FLOAT, IsFact TINYINT) -- RES_SAVED_DATE INT changed to RES_SAVED_DATE DATETIME
    
     DECLARE @ASSN_UID UNIQUEIDENTIFIER, @ASSN_CHANGE_DATA VARBINARY(MAX), @i INT, @Limit INT, @Delta INT, 
     @TASK_START_DATE INT, @TASK_FINISH_DATE INT, @h FLOAT, @ASSN_TRANS_SUBMIT_DATE DATETIME 
    
      -- Data preparation 
      SELECT @ASSN_UID = ASSN_UID, @ASSN_CHANGE_DATA = ASSN_CHANGE_DATA, @ASSN_TRANS_SUBMIT_DATE = ASSN_TRANS_SUBMIT_DATE 
      FROM pjpub.MSP_ASSIGNMENT_TRANSACTIONS at 
      WHERE at.ASSN_TRANS_UID = @ASSN_TRANS_UID 
    
      -- looking for date start and finish for ASSN_UID 
     IF NOT EXISTS (SELECT top 1 ASSN_UID FROM pjpub.MSP_ASSIGNMENTS_SAVED WHERE ASSN_UID = @ASSN_UID) 
       SELECT 
       @TASK_START_DATE =  DATEDIFF(dd, '19831231', ASSN_START_DATE), 
       @TASK_FINISH_DATE = DATEDIFF(dd, '19831231', ASSN_FINISH_DATE) 
       FROM pjpub.MSP_ASSIGNMENTS WHERE ASSN_UID = @ASSN_UID 
      else 
       SELECT 
       @TASK_START_DATE =  DATEDIFF(dd, '19831231', ASSN_START_DATE), 
       @TASK_FINISH_DATE = DATEDIFF(dd, '19831231', ASSN_FINISH_DATE) 
       FROM pjpub.MSP_ASSIGNMENTS_SAVED WHERE ASSN_UID = @ASSN_UID 
    
    
      SET @i = 44 
      SET @Limit = DATALENGTH(@ASSN_CHANGE_DATA) - 4 -- decrease on 4 bytes because data type is INT = 4 
    
      
    -- binary reverse engineering 
     
      WHILE @i < @Limit 
      BEGIN 
       SET @Delta = CAST(SUBSTRING(@ASSN_CHANGE_DATA, @i+1, 1) + SUBSTRING(@ASSN_CHANGE_DATA, @i ,1) AS INT) 
    
       IF @Delta BETWEEN @TASK_START_DATE AND @TASK_FINISH_DATE 
       BEGIN 
        SET @h = dbo.fnBinaryFloat2Float(SUBSTRING(@ASSN_CHANGE_DATA, @i+17,1) + SUBSTRING(@ASSN_CHANGE_DATA, @i+16, 1)  
           + SUBSTRING(@ASSN_CHANGE_DATA, @i+15,1) + SUBSTRING(@ASSN_CHANGE_DATA, @i+14,1)  
           + SUBSTRING(@ASSN_CHANGE_DATA, @i+13,1)  + SUBSTRING(@ASSN_CHANGE_DATA, @i+12,1) 
           + SUBSTRING(@ASSN_CHANGE_DATA, @i+11,1)  + SUBSTRING(@ASSN_CHANGE_DATA, @i+10,1)) / 60000 
    
        IF (@h > 0.0001) -- exclude data with 1,85424246567143E-313 
        OR (@h = 0) -- @h = 0 if resource have deleted data before
        INSERT @BUFFER SELECT @ASSN_UID, @Delta, @h, SUBSTRING(@ASSN_CHANGE_DATA, @i-10, 1) 
       
        SET @i = @i+30 -- for fast calculations
       END -- IF @Delta between @TASK_START_DATE and @TASK_FINISH_DATE 
    
       SET @i = @i+1 
      END -- WHILE i < @Limit 
    
       INSERT @SPK_ASSN_CHANGE_DATA 
        SELECT @ASSN_UID, RES_SAVED_DATE, h, @ASSN_TRANS_SUBMIT_DATE FROM @BUFFER 
        WHERE NUMBER IN (SELECT MAX(NUMBER) FROM @BUFFER WHERE IsFact = 10 GROUP BY RES_SAVED_DATE) 
      
      --End of binary reverse engineering
      
    
      RETURN
     END -- CREATE FUNCTION SPK_ASSN_CHANGE_DATA (@ASSN_TRANS_UID UNIQUEIDENTIFIER) 

    And second function converting binaries to float (scalar-valued) dbo.fn_BinaryFloatToFloat

    USE [WSS_Content]
    GO
    /****** Object:  UserDefinedFunction [dbo].[fn_BinaryFloatToFloat]    Script Date: 28.07.2020 12:40:13 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER FUNCTION [dbo].[fn_BinaryFloatToFloat] (@BinaryFloat BINARY(8))
    RETURNS FLOAT
    AS
    BEGIN
    	RETURN SIGN(CAST(@BinaryFloat AS BIGINT))
    		* (1.0 + (CAST(@BinaryFloat AS BIGINT) & 0x000FFFFFFFFFFFFF) * POWER(CAST(2 AS FLOAT), -52)) 
           * POWER(CAST(2 AS FLOAT), (CAST(@BinaryFloat AS BIGINT) & 0x7ff0000000000000) / 0x0010000000000000 - 1023) 
     END

    I'm sure it's will help to others.


    MCSA: Security; MCTP: Microsoft ISA 2006


    Tuesday, July 28, 2020 9:42 AM

All replies

  • Hello colleagues!

    Well, finally I found that submitted resource's data contained in pjpub.MSP_ASSIGNMENT_TRANSACTIONS in the field ASSN_CHANGE_DATA.

    But it have binary type, unfortunately.

    Is any way to convert binary data to something useful?

    Hope for your help, guys.



    MCSA: Security; MCTP: Microsoft ISA 2006

    Wednesday, July 8, 2020 12:50 PM
  • Hello colleagues!

    So, finally I found answer how to convert binary data to the normal view on the Russian forum http://microsoftproject.ru/forum/viewtopic.php?t=5816

    And now my script looks like this

    DECLARE @DataBegin DATETIME, @DataEnd DATETIME
    DECLARE @t TABLE (ASSN_TRANS_UID UNIQUEIDENTIFIER, ASSN_TRANS_ACTION_ENUM INT) 
    DECLARE @m TABLE (ASSN_UID UNIQUEIDENTIFIER, RES_SAVED_DATE INT, h FLOAT, ASSN_TRANS_SUBMIT_DATE DATE, ASSN_TRANS_ACTION_ENUM INT)
    DECLARE @fin TABLE (ASSN_UID UNIQUEIDENTIFIER, RES_SAVED_DATE DATE, h FLOAT, ASSN_TRANS_SUBMIT_DATE DATE)
    DECLARE @report TABLE (AssignmentUID UNIQUEIDENTIFIER, TimeByDay DATE, ProjectUID UNIQUEIDENTIFIER, TaskUID UNIQUEIDENTIFIER, AssignmentWork FLOAT, AssignmentActualWork FLOAT, ResourceUID UNIQUEIDENTIFIER)
    
    SET DATEFORMAT dmy 
    SET @DataBegin = '15-06-2020' 
    SET @DataEnd = '20-07-2020' 
    
    --------------------------------------------------------------------------------------------------------
    --------------------------------------------------------------------------------------------------------
    INSERT @t 
    SELECT 
    		at.ASSN_TRANS_UID, 
    		at.ASSN_TRANS_ACTION_ENUM 
    FROM 
    		(SELECT 
    				ASSN_UID, 
    				ASSN_TRANS_UID, 
    				ASSN_TRANS_ACTION_ENUM 
    		FROM 
    				pjpub.MSP_ASSIGNMENT_TRANSACTIONS 
    		WHERE 
    					(1=1)
    --				AND 
    --					ASSN_TRANS_STATE_ENUM LIKE 0	-- 0 - task update
    													-- 1 - request rejection
    													-- 2 - create task
    													-- 3 - re-assign
    													-- 4 - create assing
    													-- 5 - re-assign to team
    													-- 6 - delete task
    													-- 7 - delete assigned
    		) AS at 
    	INNER JOIN 
    		(SELECT 
    				ISNULL(ass.ASSN_UID, a.ASSN_UID) [ASSN_UID] 
    		FROM 
    				pjpub.MSP_ASSIGNMENTS_SAVED ass 
    				FULL JOIN 
    						pjpub.MSP_ASSIGNMENTS a 
    					ON 
    						(ass.ASSN_UID = a.ASSN_UID) 
    		WHERE 
    				ISNULL(ass.ASSN_FINISH_DATE, a.ASSN_FINISH_DATE) >= @DataBegin 
    				AND 
    				ISNULL(ass.ASSN_START_DATE, a.ASSN_START_DATE) < @DataEnd+1 
    		) AS tm 
    	ON 
    		at.ASSN_UID = tm.ASSN_UID 
    
    --------------------------------------------------------------------------------------------------------
    --------------------------------------------------------------------------------------------------------
    
    INSERT @m 
    SELECT 
    		f.*, t.ASSN_TRANS_ACTION_ENUM	-- in results:	0 - submitted
    										--				1 - approved
    										--				2 - rejected
    FROM 
    		@t t CROSS APPLY dbo.SPK_ASSN_CHANGE_DATA (t.ASSN_TRANS_UID) f 
    WHERE
    		t.ASSN_TRANS_ACTION_ENUM = 0
    
    --------------------------------------------------------------------------------------------------------
    --------------------------------------------------------------------------------------------------------
    INSERT @fin
    SELECT
    			ASSN_UID,
    			DATEADD(dd,RES_SAVED_DATE, '19831231') AS RES_SAVED_DATE, 
    			h AS h, 
    			ASSN_TRANS_SUBMIT_DATE			
    FROM
    			@m
    WHERE 
    			RES_SAVED_DATE >= DATEDIFF (DD,'19831231', @DataBegin) 
    		AND 
    			RES_SAVED_DATE <= DATEDIFF (DD,'19831231', @DataEnd)
    
    --------------------------------------------------------------------------------------------------------
    --------------------------------------------------------------------------------------------------------
    
    INSERT @report
    SELECT
    			AssView.AssignmentUID AS AssignmentUID,
    			AssView.TimeByDay AS TimeByDay, 
    			ProjView.ProjectUID AS ProjectUID,
    			TaskView.TaskUID AS TaskUID,
    			AssignmentWork = SUM(AssView.AssignmentWork),
    			AssignmentActualWork = SUM(AssView.AssignmentActualWork),
    			ResView.ResourceUID AS ResourceUID
    FROM
    			dbo.MSP_EpmAssignmentByDay_Reports_UserView AssView
    	LEFT JOIN 
    			pjrep.MSP_EpmAssignment AssTable
    		ON 
    			AssView.AssignmentUID = AssTable.AssignmentUID
    	LEFT JOIN 
    			dbo.MSP_EpmResource_Reports_UserView ResView
    		ON 
    			AssTable.ResourceUID = ResView.ResourceUID
    	LEFT JOIN 
    			dbo.MSP_EpmProject_Reports_UserView ProjView
    		ON 
    			AssTable.ProjectUID = ProjView.ProjectUID
    	LEFT JOIN 
    			dbo.MSP_EpmTask_Reports_UserView TaskView
    		ON 
    			AssTable.TaskUID = TaskView.TaskUID
    WHERE
    			(1 = 1)
    		AND 
    			(TimeByDay > @DataBegin AND TimeByDay < @DataEnd)
    		AND 
    			(AssView.AssignmentActualWork > 0.0)
    
    GROUP BY 
    			AssView.AssignmentUID,
    			AssView.TimeByDay,
    			ResView.ResourceUID,
    			ProjView.ProjectUID,
    			TaskView.TaskUID
    SELECT
    			reporting.AssignmentUID,
    			published.ASSN_UID,
    			reporting.TimeByDay,
    			published.RES_SAVED_DATE,
    			reporting.ProjectUID,
    			reporting.TaskUID,
    			reporting.ResourceUID,
    			ProjView.ProjectName,
    			TaskView.TaskName,
    			ResView.ResourceName,
    			reporting.AssignmentWork,
    			reporting.AssignmentActualWork,
    			published.h
    FROM 
    			@report reporting
    	FULL JOIN
    			@fin published
    		ON 
    			published.ASSN_UID = reporting.AssignmentUID
    		AND 
    			published.RES_SAVED_DATE = reporting.TimeByDay
    	LEFT JOIN
    			pjpub.MSP_ASSIGNMENTS PubAssnTable
    		ON 
    			published.ASSN_UID = PubAssnTable.ASSN_UID
    	LEFT JOIN 
    			dbo.MSP_EpmResource_Reports_UserView ResView
    		ON 
    			ResView.ResourceUID = reporting.ResourceUID
    		OR
    			ResView.ResourceUID = PubAssnTable.RES_UID
    	LEFT JOIN 
    			dbo.MSP_EpmProject_Reports_UserView ProjView
    		ON 
    			ProjView.ProjectUID = reporting.ProjectUID
    		OR
    			ProjView.ProjectUID = PubAssnTable.PROJ_UID
    	LEFT JOIN 
    			dbo.MSP_EpmTask_Reports_UserView TaskView
    		ON 
    			TaskView.TaskUID = reporting.TaskUID
    		OR
    			TaskView.TaskUID = PubAssnTable.TASK_UID
    			
    GROUP BY
    			reporting.AssignmentUID,
    			published.ASSN_UID,
    			reporting.TimeByDay,
    --			published.ASSN_TRANS_SUBMIT_DATE,
    			published.RES_SAVED_DATE,
    			reporting.ResourceUID,
    			reporting.ProjectUID,
    			reporting.TaskUID,
    			reporting.AssignmentWork,
    			reporting.AssignmentActualWork,
    			published.h,
    			ProjView.ProjectName,
    			TaskView.TaskName,
    			ResView.ResourceName
    ORDER BY 
    --			reporting.AssignmentUID DESC
    			published.ASSN_UID DESC

    And what I got in result

    Link to image https://social.technet.microsoft.com/Forums/getfile/1610045

    Can somebody explain to me two things what I can't understand:

    1) how to union TimeByDay and RES_SAVED_DATE columns?

    2) why ProjectUID, TaskUID and ResourceUID columns have no data?

    I've tried UNION ALL, but every time empty or error results


    MCSA: Security; MCTP: Microsoft ISA 2006



    Monday, July 20, 2020 9:46 AM
  • Hello colleagues!

    Well, I'm still working on this task.

    Achievement is report contained submitted and actual hours by days with tasks, projects, etc

    So, now the code looks like:

    --------------------------------------------------------------------------------------------------------
    -- DECLARE TABLES
    --------------------------------------------------------------------------------------------------------
    DECLARE @DataBegin DATETIME, @DataEnd DATETIME
    DECLARE @t TABLE (ASSN_TRANS_UID UNIQUEIDENTIFIER, ASSN_TRANS_ACTION_ENUM INT) 
    DECLARE @m TABLE (ASSN_UID UNIQUEIDENTIFIER, RES_SAVED_DATE INT, h FLOAT, ASSN_TRANS_SUBMIT_DATE DATE, ASSN_TRANS_ACTION_ENUM INT)
    DECLARE @fin TABLE (ASSN_UID UNIQUEIDENTIFIER, RES_SAVED_DATE DATE, h FLOAT, ASSN_TRANS_SUBMIT_DATE DATE, TASK_UID UNIQUEIDENTIFIER, PROJ_UID UNIQUEIDENTIFIER, RES_UID UNIQUEIDENTIFIER)
    DECLARE @report TABLE (AssignmentUID UNIQUEIDENTIFIER, TimeByDay DATE, ProjectUID UNIQUEIDENTIFIER, TaskUID UNIQUEIDENTIFIER, AssignmentActualWork FLOAT, ResourceUID UNIQUEIDENTIFIER)
    DECLARE @upselect TABLE (ASSN_UID UNIQUEIDENTIFIER, RES_SAVED_DATE DATE, TASK_UID UNIQUEIDENTIFIER, PROJ_UID UNIQUEIDENTIFIER, RES_UID UNIQUEIDENTIFIER)
    DECLARE @tasksel TABLE (ASSN_UID UNIQUEIDENTIFIER, TASK_NAME VARCHAR)
    
    SET DATEFORMAT dmy 
    SET @DataBegin = '07-07-2020' 
    SET @DataEnd = '21-07-2020' 
    
    --------------------------------------------------------------------------------------------------------
    -- COLLECTING SUBMITTED DATA
    --------------------------------------------------------------------------------------------------------
    
    INSERT @t 
    SELECT 
    		at.ASSN_TRANS_UID, 
    		at.ASSN_TRANS_ACTION_ENUM 
    FROM 
    		(SELECT 
    				ASSN_UID, 
    				ASSN_TRANS_UID, 
    				ASSN_TRANS_ACTION_ENUM 
    		FROM 
    				pjpub.MSP_ASSIGNMENT_TRANSACTIONS 
    		WHERE 
    					(1=1)
    --				AND 
    --					ASSN_TRANS_STATE_ENUM LIKE 0	-- 0 - task update
    													-- 1 - request rejection
    													-- 2 - create task
    													-- 3 - re-assign
    													-- 4 - create assing
    													-- 5 - re-assign to team
    													-- 6 - delete task
    													-- 7 - delete assigned
    		) AS at 
    	INNER JOIN 
    			(SELECT 
    					ISNULL(ass.ASSN_UID, a.ASSN_UID) [ASSN_UID] 
    			FROM 
    					pjpub.MSP_ASSIGNMENTS_SAVED ass 
    				FULL JOIN 
    						pjpub.MSP_ASSIGNMENTS a 
    					ON 
    						(ass.ASSN_UID = a.ASSN_UID) 
    			WHERE 
    					ISNULL(ass.ASSN_FINISH_DATE, a.ASSN_FINISH_DATE) >= @DataBegin 
    				AND 
    					ISNULL(ass.ASSN_START_DATE, a.ASSN_START_DATE) < @DataEnd+1 
    			) AS tm 
    		ON 
    			at.ASSN_UID = tm.ASSN_UID 
    
    --------------------------------------------------------------------------------------------------------
    -- SENDING DATA TO THE FUNCTION
    --------------------------------------------------------------------------------------------------------
    
    INSERT @m 
    SELECT 
    		f.*, t.ASSN_TRANS_ACTION_ENUM	-- in results:	0 - submitted
    										--				1 - approved
    										--				2 - rejected
    FROM 
    		@t t CROSS APPLY dbo.SPK_ASSN_CHANGE_DATA (t.ASSN_TRANS_UID) f 
    WHERE
    		t.ASSN_TRANS_ACTION_ENUM IN ('0', '1')
    
    --------------------------------------------------------------------------------------------------------
    -- CONVERTING RES_SAVED_DATE
    --------------------------------------------------------------------------------------------------------
    INSERT @fin
    SELECT
    			tfin.ASSN_UID,
    			DATEADD(dd,RES_SAVED_DATE, '19831231') AS RES_SAVED_DATE, 
    			h AS h, 
    			ASSN_TRANS_SUBMIT_DATE,
    			SaveTable.TASK_UID,
    			PROJ_UID,
    			RES_UID
    FROM
    			@m tfin
    	LEFT JOIN
    			pjpub.MSP_ASSIGNMENTS_SAVED SaveTable
    		ON
    			tfin.ASSN_UID = SaveTable.ASSN_UID
    WHERE 
    			RES_SAVED_DATE >= DATEDIFF (DD,'19831231', @DataBegin) 
    		AND 
    			RES_SAVED_DATE <= DATEDIFF (DD,'19831231', @DataEnd)
    
    --------------------------------------------------------------------------------------------------------
    -- PREPARING REPORTING DATA
    --------------------------------------------------------------------------------------------------------
    
    INSERT @report
    SELECT
    			AssView.AssignmentUID AS AssignmentUID,
    			AssView.TimeByDay AS TimeByDay, 
    			ProjView.ProjectUID AS ProjectUID,
    			TaskView.TaskUID AS TaskUID,
    			AssignmentActualWork = SUM(AssView.AssignmentActualWork),
    			ResView.ResourceUID AS ResourceUID
    FROM
    			dbo.MSP_EpmAssignmentByDay_Reports_UserView AssView
    	LEFT JOIN 
    			pjrep.MSP_EpmAssignment AssTable
    		ON 
    			AssView.AssignmentUID = AssTable.AssignmentUID
    	LEFT JOIN 
    			dbo.MSP_CustomViewReports_EpmResource_UserView ResView
    		ON 
    			AssTable.ResourceUID = ResView.ResourceUID
    	LEFT JOIN 
    			dbo.MSP_CustomViewReports_EpmProject_UserView ProjView
    		ON 
    			AssTable.ProjectUID = ProjView.ProjectUID
    	LEFT JOIN 
    			dbo.MSP_CustomViewReports_EpmTask_UserView TaskView
    		ON 
    			AssTable.TaskUID = TaskView.TaskUID
    WHERE
    			(1 = 1)
    --		AND 
    --			ResourceName IN ('Shuklin Roman')
    		AND 
    			(TimeByDay > @DataBegin AND TimeByDay < @DataEnd)
    		AND 
    			(AssView.AssignmentActualWork > 0.0)
    GROUP BY 
    			AssView.AssignmentUID,
    			AssView.TimeByDay,
    			ResView.ResourceUID,
    			ProjView.ProjectUID,
    			TaskView.TaskUID
    --------------------------------------------------------------------------------------------------------
    -- UNION ALL PUB AND REP UIDS
    --------------------------------------------------------------------------------------------------------
    
    INSERT @upselect
    SELECT
    			ASSN_UID, 
    			RES_SAVED_DATE, 
    			TASK_UID, 
    			PROJ_UID,
    			RES_UID
    FROM
    			@fin
    UNION
    			ALL
    SELECT
    			AssignmentUID, 
    			TimeByDay, 
    			TaskUID, 
    			ProjectUID,
    			ResourceUID
    FROM
    			@report
    
    --------------------------------------------------------------------------------------------------------
    -- GETTING TASKS
    --------------------------------------------------------------------------------------------------------
    INSERT @tasksel
    SELECT
    			ASSN_UID,
    			TASK_NAME
    FROM
    			pjpub.MSP_ASSIGNMENTS_SAVED
    UNION
    			ALL
    SELECT
    			AssignmentUID,
    			TaskName
    FROM
    			dbo.MSP_CustomViewReports_EpmAssignment_UserView AssView
    	LEFT JOIN
    			dbo.MSP_CustomViewReports_EpmTask_UserView TaskView
    		ON	
    			AssView.TaskUID = TaskView.TaskUID
    
    
    --------------------------------------------------------------------------------------------------------
    -- GENERATING FINAL REPORT
    --------------------------------------------------------------------------------------------------------
    SELECT
    			upsel.PROJ_UID AS P_UID,
    			upsel.RES_UID AS R_UID,
    			ResView.ResourceName AS ResourceName,
    			upsel.RES_SAVED_DATE AS TBD,
    			upsel.ASSN_UID AS A_UID,
    			upsel.TASK_UID AS T_UID,
    			tasksel.TASK_NAME AS T_NAME,
    			tfin.h AS SH,
    			AssView.AssignmentActualWork AS AH
    			
    FROM
    			@upselect upsel
    	LEFT JOIN
    			@fin tfin
    		ON 
    			upsel.ASSN_UID = tfin.ASSN_UID
    	LEFT JOIN
    			@report AssView
    		ON
    			upsel.ASSN_UID = AssView.AssignmentUID 
    	LEFT JOIN
    			@tasksel tasksel
    		ON
    			upsel.ASSN_UID = tasksel.ASSN_UID
    	LEFT JOIN
    			dbo.MSP_CustomViewReports_EpmResource_UserView ResView
    		ON
    			upsel.RES_UID = ResView.ResourceUID
    WHERE
    			(1=1)
    		AND
    			ResView.ResourceName IN ('Kucherovsky Andrey')
    GROUP BY
    			upsel.PROJ_UID,
    			upsel.RES_UID,
    			ResView.ResourceName,
    			upsel.RES_SAVED_DATE,
    			upsel.ASSN_UID,
    			upsel.TASK_UID,
    			tasksel.TASK_NAME,
    			tfin.h,
    			AssView.AssignmentActualWork

    But error in result

    (4314 rows affected)
    
    (12388 rows affected)
    
    (2852 rows affected)
    
    (1342 rows affected)
    
    (4194 rows affected)
    Msg 8152, Level 16, State 30, Line 151
    String or binary data would be truncated.
    The statement has been terminated.
    
    (2 rows affected)
    
    Completion time: 2020-07-21T12:56:59.1425071+03:00

    The Line 151 it's RES_UID in this select

    INSERT @upselect
    SELECT
    			ASSN_UID, 
    			RES_SAVED_DATE, 
    			TASK_UID, 
    			PROJ_UID,
    			RES_UID
    FROM
    			@fin

    By DECLARE the tables @fin and @upselect have RES_UID column as UNIQUEIDENTIFIER type.

    Original RES_UID we're getting from pjpub.MSP_ASSIGNMENTS_SAVED in this select

    INSERT @fin
    SELECT
    			mt.ASSN_UID,
    			DATEADD(dd,RES_SAVED_DATE, '19831231') AS RES_SAVED_DATE, 
    			h AS h, 
    			ASSN_TRANS_SUBMIT_DATE,
    			SaveTable.TASK_UID,
    			PROJ_UID,
    			RES_UID
    FROM
    			@m mt
    	LEFT JOIN
    			pjpub.MSP_ASSIGNMENTS_SAVED SaveTable
    		ON
    			mt.ASSN_UID = SaveTable.ASSN_UID
    The RES_UID from pjpub.MSP_ASSIGNMENTS_SAVED is UNIQUEIDENTIFIER type too.

    So, colleagues, what's wrong now?


    MCSA: Security; MCTP: Microsoft ISA 2006

    Tuesday, July 21, 2020 10:16 AM
  • Hello colleagues!

    Well, I'm still working on the way how to get submitted and approved (published) hours in the report.

    So, I think, it's almost done... but one trouble.

    Look at the script:

    --------------------------------------------------------------------------------------------------------
    -- DECLARE TABLES
    --------------------------------------------------------------------------------------------------------
    DECLARE @DataBegin DATETIME, @DataEnd DATETIME
    DECLARE @t TABLE (ASSN_TRANS_UID UNIQUEIDENTIFIER, ASSN_TRANS_ACTION_ENUM INT) 
    DECLARE @m TABLE (ASSN_UID UNIQUEIDENTIFIER, RES_SAVED_DATE INT, h FLOAT, ASSN_TRANS_SUBMIT_DATE DATE, ASSN_TRANS_ACTION_ENUM INT)
    DECLARE @fin TABLE (ASSN_UID UNIQUEIDENTIFIER, RES_SAVED_DATE DATE, h FLOAT, ASSN_TRANS_SUBMIT_DATE DATE, TASK_NAME VARCHAR(MAX), PROJ_UID UNIQUEIDENTIFIER, RES_UID UNIQUEIDENTIFIER)
    DECLARE @report TABLE (AssignmentUID UNIQUEIDENTIFIER, TimeByDay DATE, ProjectUID UNIQUEIDENTIFIER, TaskUID UNIQUEIDENTIFIER, TaskName VARCHAR(MAX), AssignmentActualWork FLOAT, ResourceUID UNIQUEIDENTIFIER)
    DECLARE @upselect TABLE (ASSN_UID UNIQUEIDENTIFIER, RES_SAVED_DATE DATE, TASK_NAME VARCHAR(MAX), PROJ_UID UNIQUEIDENTIFIER, RES_UID UNIQUEIDENTIFIER)
    
    SET DATEFORMAT dmy 
    SET @DataBegin = (@DateBeginParameter) 
    SET @DataEnd = (@DateFinishParameter)
    
    --------------------------------------------------------------------------------------------------------
    -- COLLECTING SUBMITTED DATA
    --------------------------------------------------------------------------------------------------------
    
    INSERT @t 
    SELECT 
    		at.ASSN_TRANS_UID, 
    		at.ASSN_TRANS_ACTION_ENUM 
    FROM 
    		(SELECT 
    				ASSN_UID, 
    				ASSN_TRANS_UID, 
    				ASSN_TRANS_ACTION_ENUM 
    		FROM 
    				pjpub.MSP_ASSIGNMENT_TRANSACTIONS 
    		WHERE 
    					(1=1)
    				AND
    					ASSN_TRANS_SUBMITTER_RES_UID IN (@ResourceUIDParameter)
    --				AND 
    --					ASSN_TRANS_STATE_ENUM LIKE 0	-- 0 - task update
    													-- 1 - request rejection
    													-- 2 - create task
    													-- 3 - re-assign
    													-- 4 - create assing
    													-- 5 - re-assign to team
    													-- 6 - delete task
    													-- 7 - delete assigned
    		) AS at 
    	INNER JOIN 
    			(SELECT 
    					ISNULL(ass.ASSN_UID, a.ASSN_UID) [ASSN_UID] 
    			FROM 
    					pjpub.MSP_ASSIGNMENTS_SAVED ass 
    				FULL JOIN 
    						pjpub.MSP_ASSIGNMENTS a 
    					ON 
    						(ass.ASSN_UID = a.ASSN_UID) 
    			WHERE 
    					ISNULL(ass.ASSN_FINISH_DATE, a.ASSN_FINISH_DATE) >= @DataBegin 
    				AND 
    					ISNULL(ass.ASSN_START_DATE, a.ASSN_START_DATE) < @DataEnd+1 
    			) AS tm 
    		ON 
    			at.ASSN_UID = tm.ASSN_UID 
    
    --------------------------------------------------------------------------------------------------------
    -- SENDING DATA TO THE FUNCTION
    --------------------------------------------------------------------------------------------------------
    
    INSERT @m 
    SELECT 
    		f.*, t.ASSN_TRANS_ACTION_ENUM	-- in results:	0 - submitted
    										--				1 - approved
    										--				2 - rejected
    FROM 
    		@t t CROSS APPLY dbo.SPK_ASSN_CHANGE_DATA (t.ASSN_TRANS_UID) f 
    WHERE
    		t.ASSN_TRANS_ACTION_ENUM IN ('0', '1')
    
    --------------------------------------------------------------------------------------------------------
    -- CONVERTING RES_SAVED_DATE
    --------------------------------------------------------------------------------------------------------
    INSERT @fin
    SELECT
    			mt.ASSN_UID,
    			DATEADD(dd,RES_SAVED_DATE, '19831231') AS RES_SAVED_DATE, 
    			h AS h, 
    			ASSN_TRANS_SUBMIT_DATE,
    			TASK_NAME,
    			PROJ_UID,
    			RES_UID
    FROM
    			@m mt
    	LEFT JOIN
    			pjpub.MSP_ASSIGNMENTS_SAVED SaveTable
    		ON
    			mt.ASSN_UID = SaveTable.ASSN_UID
    WHERE 
    			RES_SAVED_DATE >= DATEDIFF (DD,'19831231', @DataBegin) 
    		AND 
    			RES_SAVED_DATE <= DATEDIFF (DD,'19831231', @DataEnd)
    
    --------------------------------------------------------------------------------------------------------
    -- PREPARING REPORTING DATA
    --------------------------------------------------------------------------------------------------------
    
    INSERT @report
    SELECT
    			AssView.AssignmentUID AS AssignmentUID,
    			AssView.TimeByDay AS TimeByDay, 
    			ProjView.ProjectUID AS ProjectUID,
    			TaskView.TaskUID AS TaskUID,
    			TaskView.TaskName AS TaskName,
    			AssignmentActualWork = SUM(AssView.AssignmentActualWork),
    			ResView.ResourceUID AS ResourceUID
    FROM
    			dbo.MSP_EpmAssignmentByDay_Reports_UserView AssView
    	LEFT JOIN 
    			pjrep.MSP_EpmAssignment AssTable
    		ON 
    			AssView.AssignmentUID = AssTable.AssignmentUID
    	LEFT JOIN 
    			dbo.MSP_CustomViewReports_EpmResource_UserView ResView
    		ON 
    			AssTable.ResourceUID = ResView.ResourceUID
    	LEFT JOIN 
    			dbo.MSP_CustomViewReports_EpmProject_UserView ProjView
    		ON 
    			AssTable.ProjectUID = ProjView.ProjectUID
    	LEFT JOIN 
    			dbo.MSP_CustomViewReports_EpmTask_UserView TaskView
    		ON 
    			AssTable.TaskUID = TaskView.TaskUID
    WHERE
    			(1 = 1)
    		AND
    			ResView.ResourceUID IN (@ResourceUIDParameter)
    		AND 
    			(TimeByDay > @DataBegin AND TimeByDay < @DataEnd)
    		AND 
    			(AssView.AssignmentActualWork > 0.0)
    GROUP BY 
    			AssView.AssignmentUID,
    			AssView.TimeByDay,
    			ResView.ResourceUID,
    			ProjView.ProjectUID,
    			TaskView.TaskUID,
    			TaskView.TaskName
    --------------------------------------------------------------------------------------------------------
    -- UNION ALL PUB AND REP UIDS
    --------------------------------------------------------------------------------------------------------
    
    INSERT @upselect
    SELECT
    			ASSN_UID, 
    			RES_SAVED_DATE, 
    			TASK_NAME,
    			PROJ_UID,
    			RES_UID
    FROM
    			@fin
    UNION
    --			ALL
    SELECT
    			AssignmentUID, 
    			TimeByDay, 
    			TaskName,
    			ProjectUID,
    			ResourceUID
    FROM
    			@report
    
    --------------------------------------------------------------------------------------------------------
    -- GENERATING FINAL REPORT
    --------------------------------------------------------------------------------------------------------
    SELECT DISTINCT
    			upsel.PROJ_UID AS P_UID,
    			ProjView.ProjectName AS ProjectName,
    			upsel.RES_UID AS R_UID,
    			ResView.ResourceName AS ResourceName,
    			upsel.RES_SAVED_DATE AS TBD,
    			upsel.ASSN_UID AS A_UID,
    			upsel.TASK_NAME AS TaskName,
    			tfin.h AS SH,
    			AssView.AssignmentActualWork AS AH,
    			ResView.[Project Group] AS [Project Group],
    			ResView.[Role] AS [Role]
    FROM								
    			@upselect upsel
    	LEFT JOIN
    			@fin tfin
    		ON 
    			upsel.ASSN_UID = tfin.ASSN_UID
    		AND	
    			upsel.RES_SAVED_DATE = tfin.RES_SAVED_DATE
    	LEFT JOIN
    			@report AssView
    		ON
    			upsel.ASSN_UID = AssView.AssignmentUID
    		AND
    			upsel.RES_SAVED_DATE = AssView.TimeByDay
    	LEFT JOIN
    			dbo.MSP_CustomViewReports_EpmResource_UserView ResView
    		ON
    			upsel.RES_UID = ResView.ResourceUID
    	LEFT JOIN
    			dbo.MSP_CustomViewReports_EpmProject_UserView ProjView
    		ON
    			upsel.PROJ_UID = ProjView.ProjectUID
    ORDER BY
    			upsel.RES_SAVED_DATE DESC

    Finally I'm getting good result - submitted to manager and approved by manager hours.

    But it makes wrong when the resource sent efforts more than one time per day (per task, of course) - hours will summarized.

    For example look at the screenshot

    Link to image: https://social.technet.microsoft.com/Forums/getfile/1611199

    Resource sent two times - 1 hour first time, than he changed it to 6 hours and sent it to manager, but got 7 hours in the report, but should be 6 hours.

    So, is any way to sort this situation?

    How to get only last transaction of assignment per day?

     


    MCSA: Security; MCTP: Microsoft ISA 2006



    Friday, July 24, 2020 7:13 AM
  • Hello colleagues!

    Finally, it were done!

    Total Actual Work report with TimeByDay and Grand Total.

    Ofcource, it will be good to implement Monthy Totals, but I think its more SQL task, than MSP, so I've started thread about that here https://social.technet.microsoft.com/Forums/en-US/4066fa74-ce65-4468-842d-62b71fa16095/monthly-subtotals-in-the-report?forum=sqlreportingservices.

    Now the report can show to us Timesheet Data, Submitted and Approved hours.

    Main script:

    USE WSS_Content
    --------------------------------------------------------------------------------------------------------
    -- DECLARE TABLES
    --------------------------------------------------------------------------------------------------------
    DECLARE @DataBegin DATETIME, @DataEnd DATETIME
    DECLARE @t TABLE (ASSN_TRANS_UID UNIQUEIDENTIFIER, ASSN_TRANS_ACTION_ENUM INT, ASSN_TRANS_STATE_ENUM INT, ASSN_TRANS_TYPE_ENUM INT) 
    DECLARE @m TABLE (ASSN_UID UNIQUEIDENTIFIER, RES_SAVED_DATE INT, h FLOAT, ASSN_TRANS_SUBMIT_DATE DATETIME, ASSN_TRANS_ACTION_ENUM INT, ASSN_TRANS_STATE_ENUM INT, ASSN_TRANS_TYPE_ENUM INT)
    DECLARE @s TABLE (ASSN_UID UNIQUEIDENTIFIER, RES_SAVED_DATE DATE, h FLOAT, ASSN_TRANS_SUBMIT_DATE DATETIME, ASSN_TRANS_ACTION_ENUM INT, ASSN_TRANS_STATE_ENUM INT, ASSN_TRANS_TYPE_ENUM INT)
    DECLARE @date TABLE (ASSN_UID UNIQUEIDENTIFIER, RES_SAVED_DATE DATE, ASSN_TRANS_SUBMIT_DATE DATETIME)
    DECLARE @fin TABLE (ASSN_UID UNIQUEIDENTIFIER, RES_SAVED_DATE DATE, h FLOAT, ASSN_TRANS_SUBMIT_DATE DATETIME, TASK_NAME VARCHAR(MAX), PROJ_UID UNIQUEIDENTIFIER, RES_UID UNIQUEIDENTIFIER, ASSN_TRANS_ACTION_ENUM INT, ASSN_TRANS_STATE_ENUM INT, ASSN_TRANS_TYPE_ENUM INT)
    DECLARE @report TABLE (AssignmentUID UNIQUEIDENTIFIER, TimeByDay DATE, ProjectUID UNIQUEIDENTIFIER, TaskUID UNIQUEIDENTIFIER, TaskName VARCHAR(MAX), AssignmentActualWork FLOAT, ResourceUID UNIQUEIDENTIFIER)
    DECLARE @ts TABLE (ResourceUID UNIQUEIDENTIFIER, TimesheetWork FLOAT, TimeByDay DATE, ProjectUID UNIQUEIDENTIFIER, TaskName VARCHAR(MAX))
    DECLARE @upselect TABLE (ASSN_UID UNIQUEIDENTIFIER, RES_SAVED_DATE DATE, TASK_NAME VARCHAR(MAX), PROJ_UID UNIQUEIDENTIFIER, RES_UID UNIQUEIDENTIFIER)
    
    SET DATEFORMAT dmy 
    SET @DataBegin = @DateBeginParameter 
    SET @DataEnd = @DateFinishParameter
    
    --------------------------------------------------------------------------------------------------------
    -- COLLECTING SUBMITTED DATA
    --------------------------------------------------------------------------------------------------------
    
    INSERT @t 
    SELECT 
    		at.ASSN_TRANS_UID, 
    		at.ASSN_TRANS_ACTION_ENUM,
    		at.ASSN_TRANS_STATE_ENUM,
    		at.ASSN_TRANS_TYPE_ENUM
    FROM 
    		(SELECT 
    				ASSN_UID, 
    				ASSN_TRANS_UID, 
    				ASSN_TRANS_ACTION_ENUM,
    				ASSN_TRANS_STATE_ENUM,
    				ASSN_TRANS_TYPE_ENUM
    		FROM 
    				pjpub.MSP_ASSIGNMENT_TRANSACTIONS 
    		WHERE 
    					(1=1)
    				AND
    					ASSN_TRANS_SUBMITTER_RES_UID IN (@ResourceUIDParameter)
    --				AND 
    --					ASSN_TRANS_STATE_ENUM IN ('1', '2', '3')
    		) AS at 
    	INNER JOIN 
    			(SELECT 
    					ISNULL(ass.ASSN_UID, a.ASSN_UID) [ASSN_UID] 
    			FROM 
    					pjpub.MSP_ASSIGNMENTS_SAVED ass 
    				FULL JOIN 
    						pjpub.MSP_ASSIGNMENTS a 
    					ON 
    						(ass.ASSN_UID = a.ASSN_UID) 
    			WHERE 
    					ISNULL(ass.ASSN_FINISH_DATE, a.ASSN_FINISH_DATE) >= @DataBegin 
    				AND 
    					ISNULL(ass.ASSN_START_DATE, a.ASSN_START_DATE) < @DataEnd+1 
    			) AS tm 
    		ON 
    			at.ASSN_UID = tm.ASSN_UID 
    
    --------------------------------------------------------------------------------------------------------
    -- SENDING DATA TO THE FUNCTION
    --------------------------------------------------------------------------------------------------------
    
    INSERT 
    			@m 
    SELECT 
    			f.*, 
    			t.ASSN_TRANS_ACTION_ENUM,
    			t.ASSN_TRANS_STATE_ENUM,
    			t.ASSN_TRANS_TYPE_ENUM
    FROM 
    			@t t 
    		CROSS APPLY 
    			dbo.SPK_ASSN_CHANGE_DATA (t.ASSN_TRANS_UID) f 
    WHERE
    			t.ASSN_TRANS_ACTION_ENUM IN ('0', '1')
    
    --------------------------------------------------------------------------------------------------------
    -- GETTING DATA TO COVERT RES_SAVED_DATE AND SORTING
    --------------------------------------------------------------------------------------------------------
    INSERT 
    			@s
    SELECT
    			mt.ASSN_UID,
    			DATEADD(dd,RES_SAVED_DATE, '19831231') AS RES_SAVED_DATE, 
    			h AS h, 
    			ASSN_TRANS_SUBMIT_DATE,
    			mt.ASSN_TRANS_ACTION_ENUM,
    			ASSN_TRANS_STATE_ENUM,
    			ASSN_TRANS_TYPE_ENUM
    FROM
    			@m mt
    WHERE 
    			RES_SAVED_DATE >= DATEDIFF (DD,'19831231', @DataBegin) 
    		AND 
    			RES_SAVED_DATE <= DATEDIFF (DD,'19831231', @DataEnd)
    
    --------------------------------------------------------------------------------------------------------
    -- GETTING LAST TRANSACTION (SORTING)
    --------------------------------------------------------------------------------------------------------
    INSERT @date
    SELECT
    			ASSN_UID,
    			RES_SAVED_DATE, 
    			MAX(ASSN_TRANS_SUBMIT_DATE) AS ASSN_TRANS_SUBMIT_DATE
    FROM
    			@s st
    
    GROUP BY
    			ASSN_UID,
    			RES_SAVED_DATE
    
    --------------------------------------------------------------------------------------------------------
    -- LIST SORTED DATA
    --------------------------------------------------------------------------------------------------------
    INSERT @fin
    SELECT		
    			dt.ASSN_UID,
    			dt.RES_SAVED_DATE,
    			st.h,
    			dt.ASSN_TRANS_SUBMIT_DATE,
    			Saved.TASK_NAME,
    			Saved.PROJ_UID,
    			Saved.RES_UID,
    			st.ASSN_TRANS_ACTION_ENUM,
    			ASSN_TRANS_STATE_ENUM,
    			ASSN_TRANS_TYPE_ENUM
    FROM
    			@date dt
    	INNER JOIN
    			@s st
    		ON	
    			dt.RES_SAVED_DATE = st.RES_SAVED_DATE
    		AND
    			dt.ASSN_TRANS_SUBMIT_DATE = st.ASSN_TRANS_SUBMIT_DATE
    	LEFT JOIN
    			pjpub.MSP_ASSIGNMENTS_SAVED Saved
    		ON
    			st.ASSN_UID = Saved.ASSN_UID
    WHERE 
    			dt.RES_SAVED_DATE >= @DataBegin 
    		AND 
    			dt.RES_SAVED_DATE <= @DataEnd
    GROUP BY
    			dt.ASSN_UID,
    			dt.RES_SAVED_DATE,
    			st.h,
    			dt.ASSN_TRANS_SUBMIT_DATE,
    			Saved.TASK_NAME,
    			Saved.PROJ_UID,
    			Saved.RES_UID,
    			st.ASSN_TRANS_ACTION_ENUM,
    			ASSN_TRANS_STATE_ENUM,
    			ASSN_TRANS_TYPE_ENUM
    
    --------------------------------------------------------------------------------------------------------
    -- PREPARING REPORTING DATA
    --------------------------------------------------------------------------------------------------------
    
    INSERT @report
    SELECT
    			AssView.AssignmentUID AS AssignmentUID,
    			AssView.TimeByDay AS TimeByDay, 
    			ProjView.ProjectUID AS ProjectUID,
    			TaskView.TaskUID AS TaskUID,
    			TaskView.TaskName AS TaskName,
    			AssignmentActualWork = SUM(AssView.AssignmentActualWork),
    			ResView.ResourceUID AS ResourceUID
    FROM
    			dbo.MSP_EpmAssignmentByDay_Reports_UserView AssView
    	LEFT JOIN 
    			pjrep.MSP_EpmAssignment AssTable
    		ON 
    			AssView.AssignmentUID = AssTable.AssignmentUID
    	LEFT JOIN 
    			dbo.MSP_CustomViewReports_EpmResource_UserView ResView
    		ON 
    			AssTable.ResourceUID = ResView.ResourceUID
    	LEFT JOIN 
    			dbo.MSP_CustomViewReports_EpmProject_UserView ProjView
    		ON 
    			AssTable.ProjectUID = ProjView.ProjectUID
    	LEFT JOIN 
    			dbo.MSP_CustomViewReports_EpmTask_UserView TaskView
    		ON 
    			AssTable.TaskUID = TaskView.TaskUID
    WHERE
    			(1 = 1)
    		AND
    			ResView.ResourceUID IN (@ResourceUIDParameter)
    		AND 
    			(TimeByDay > @DataBegin AND TimeByDay < @DataEnd)
    		AND 
    			(AssView.AssignmentActualWork > 0.0)
    GROUP BY 
    			AssView.AssignmentUID,
    			AssView.TimeByDay,
    			ResView.ResourceUID,
    			ProjView.ProjectUID,
    			TaskView.TaskUID,
    			TaskView.TaskName
    
    --------------------------------------------------------------------------------------------------------
    -- GETTING DATA FROM TIMESHEETS
    --------------------------------------------------------------------------------------------------------
    INSERT @ts
    SELECT 
    			TSResTable.ResourceUID AS ResourceUID,
    			SUM(TSActTable.ActualWorkBillable) 
    				+ SUM(TSActTable.ActualWorkNonBillable)
    				+ SUM(TSActTable.ActualOvertimeWorkBillable)
    				+ SUM(TSActTable.ActualOvertimeWorkNonBillable)
    				AS TimesheetWork,
    			TSActTable.TimeByDay AS TimeByDay,
    --			ISNULL(ts.TASK_PUBLISHED_UID,TSTaskTable.TaskUID) AS TaskUID,
    			TSProjTable.ProjectUID AS ProjectUID,
    			ISNULL(ts.TASK_NAME,TSTaskTable.TaskName) AS TaskName
    FROM  
    			dbo.MSP_CustomViewReports_EpmResource_UserView ResView
    	LEFT OUTER JOIN 
    			pjrep.MSP_TimesheetResource TSResTable
    	INNER JOIN 
    			pjrep.MSP_TimesheetActual TSActTable
    		ON 
    			TSResTable.ResourceNameUID = TSActTable.LastChangedResourceNameUID 
    		ON 
    			ResView.ResourceUID = TSResTable.ResourceUID 
    	INNER JOIN 
    			pjrep.MSP_TimesheetLine TSLineTable
    		ON 
    			TSActTable.TimesheetLineUID = TSLineTable.TimesheetLineUID
    	LEFT JOIN 
    			pjrep.MSP_TimesheetTask TSTaskTable
    		ON  
    			TSTaskTable.TaskNameUID = TSLineTable.TaskNameUID
    	LEFT JOIN 
    			pjpub.MSP_TASKS_SAVED AS ts
    		ON 
    			ts.TASK_UID = TSTaskTable.TaskUID 
    	LEFT JOIN 
    			pjrep.MSP_TimesheetProject TSProjTable
    		ON 
    			TSProjTable.ProjectNameUID = TSLineTable.ProjectNameUID
    WHERE  
    			TSResTable.ResourceUID IN (@ResourceUIDParameter)
    		AND 
    			(TSActTable.TimeByDay >= (@DataBegin) AND TSActTable.TimeByDay <= (@DataEnd))
    GROUP BY 
    			TSResTable.ResourceUID,
    			ResView.ResourceName,
    			TSActTable.TimeByDay,
    			TSLineTable.TaskNameUID,
    			TSLineTable.ProjectNameUID,
    			TSProjTable.ProjectUID,
    			ts.TASK_PUBLISHED_UID,
    			ts.TASK_NAME,
    			TSProjTable.ProjectName,
    			TSTaskTable.TaskUID,
    			TSTaskTable.TaskName
    ORDER BY 
    			TSActTable.TimeByDay DESC
    
    --------------------------------------------------------------------------------------------------------
    -- UNION ALL PUB AND REP UIDS
    --------------------------------------------------------------------------------------------------------
    
    INSERT @upselect
    SELECT DISTINCT
    			ASSN_UID, 
    			RES_SAVED_DATE, 
    			TASK_NAME,
    			PROJ_UID,
    			RES_UID
    FROM
    			@fin
    UNION
    --			ALL
    SELECT
    			AssignmentUID, 
    			TimeByDay, 
    			TaskName,
    			ProjectUID,
    			ResourceUID
    FROM
    			@report
    
    --------------------------------------------------------------------------------------------------------
    -- GENERATING FINAL REPORT
    --------------------------------------------------------------------------------------------------------
    SELECT --DISTINCT
    --			*
    			upsel.PROJ_UID AS P_UID,
    			ProjView.ProjectName AS ProjectName,
    			upsel.RES_UID AS R_UID,
    			ResView.ResourceName AS ResourceName,
    			upsel.RES_SAVED_DATE AS TBD,
    			upsel.ASSN_UID AS A_UID,
    			upsel.TASK_NAME AS TaskName,
    			ts.TimesheetWork AS TH,
    			tfin.h AS SH,
    			AssView.AssignmentActualWork AS AH
    FROM								
    			@upselect upsel
    	LEFT JOIN
    			@fin tfin
    		ON 
    			upsel.ASSN_UID = tfin.ASSN_UID
    		AND	
    			upsel.RES_SAVED_DATE = tfin.RES_SAVED_DATE
    	LEFT JOIN
    			@report AssView
    		ON
    			upsel.ASSN_UID = AssView.AssignmentUID
    		AND
    			upsel.RES_SAVED_DATE = AssView.TimeByDay
    	LEFT JOIN
    			@ts ts
    		ON
    			upsel.PROJ_UID = ts.ProjectUID
    		AND
    			upsel.RES_SAVED_DATE = ts.TimeByDay
    		AND
    			upsel.RES_UID = ts.ResourceUID
    		AND
    			upsel.TASK_NAME = ts.TaskName
    	LEFT JOIN
    			dbo.MSP_CustomViewReports_EpmResource_UserView ResView
    		ON
    			upsel.RES_UID = ResView.ResourceUID
    	LEFT JOIN
    			dbo.MSP_CustomViewReports_EpmProject_UserView ProjView
    		ON
    			upsel.PROJ_UID = ProjView.ProjectUID
    ORDER BY
    			upsel.RES_SAVED_DATE DESC

    As you can see, I'm using Task's Names instead of Task's UID.

    Why:

    Tasks have different UIDs in the published and reporting structures (tables)!

    The binary data decoding function (table-valued) dbo.SPK_ASSN_CHANGE_DATA

    USE [WSS_Content]
    GO
    /****** Object:  UserDefinedFunction [dbo].[SPK_ASSN_CHANGE_DATA]    Script Date: 28.07.2020 12:37:42 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
     ALTER FUNCTION [dbo].[SPK_ASSN_CHANGE_DATA] (@ASSN_TRANS_UID UNIQUEIDENTIFIER) 
     RETURNS @SPK_ASSN_CHANGE_DATA TABLE (ASSN_UID UNIQUEIDENTIFIER, RES_SAVED_DATE INT, h FLOAT, ASSN_TRANS_SUBMIT_DATE DATETIME) -- RES_SAVED_DATE INT changed to RES_SAVED_DATE DATETIME
     AS 
     BEGIN 
     DECLARE @BUFFER TABLE (NUMBER INT IDENTITY, ASSN_UID UNIQUEIDENTIFIER, RES_SAVED_DATE INT, h FLOAT, IsFact TINYINT) -- RES_SAVED_DATE INT changed to RES_SAVED_DATE DATETIME
    
     DECLARE @ASSN_UID UNIQUEIDENTIFIER, @ASSN_CHANGE_DATA VARBINARY(MAX), @i INT, @Limit INT, @Delta INT, 
     @TASK_START_DATE INT, @TASK_FINISH_DATE INT, @h FLOAT, @ASSN_TRANS_SUBMIT_DATE DATETIME 
    
      -- Data preparation 
      SELECT @ASSN_UID = ASSN_UID, @ASSN_CHANGE_DATA = ASSN_CHANGE_DATA, @ASSN_TRANS_SUBMIT_DATE = ASSN_TRANS_SUBMIT_DATE 
      FROM pjpub.MSP_ASSIGNMENT_TRANSACTIONS at 
      WHERE at.ASSN_TRANS_UID = @ASSN_TRANS_UID 
    
      -- looking for date start and finish for ASSN_UID 
     IF NOT EXISTS (SELECT top 1 ASSN_UID FROM pjpub.MSP_ASSIGNMENTS_SAVED WHERE ASSN_UID = @ASSN_UID) 
       SELECT 
       @TASK_START_DATE =  DATEDIFF(dd, '19831231', ASSN_START_DATE), 
       @TASK_FINISH_DATE = DATEDIFF(dd, '19831231', ASSN_FINISH_DATE) 
       FROM pjpub.MSP_ASSIGNMENTS WHERE ASSN_UID = @ASSN_UID 
      else 
       SELECT 
       @TASK_START_DATE =  DATEDIFF(dd, '19831231', ASSN_START_DATE), 
       @TASK_FINISH_DATE = DATEDIFF(dd, '19831231', ASSN_FINISH_DATE) 
       FROM pjpub.MSP_ASSIGNMENTS_SAVED WHERE ASSN_UID = @ASSN_UID 
    
    
      SET @i = 44 
      SET @Limit = DATALENGTH(@ASSN_CHANGE_DATA) - 4 -- decrease on 4 bytes because data type is INT = 4 
    
      
    -- binary reverse engineering 
     
      WHILE @i < @Limit 
      BEGIN 
       SET @Delta = CAST(SUBSTRING(@ASSN_CHANGE_DATA, @i+1, 1) + SUBSTRING(@ASSN_CHANGE_DATA, @i ,1) AS INT) 
    
       IF @Delta BETWEEN @TASK_START_DATE AND @TASK_FINISH_DATE 
       BEGIN 
        SET @h = dbo.fnBinaryFloat2Float(SUBSTRING(@ASSN_CHANGE_DATA, @i+17,1) + SUBSTRING(@ASSN_CHANGE_DATA, @i+16, 1)  
           + SUBSTRING(@ASSN_CHANGE_DATA, @i+15,1) + SUBSTRING(@ASSN_CHANGE_DATA, @i+14,1)  
           + SUBSTRING(@ASSN_CHANGE_DATA, @i+13,1)  + SUBSTRING(@ASSN_CHANGE_DATA, @i+12,1) 
           + SUBSTRING(@ASSN_CHANGE_DATA, @i+11,1)  + SUBSTRING(@ASSN_CHANGE_DATA, @i+10,1)) / 60000 
    
        IF (@h > 0.0001) -- exclude data with 1,85424246567143E-313 
        OR (@h = 0) -- @h = 0 if resource have deleted data before
        INSERT @BUFFER SELECT @ASSN_UID, @Delta, @h, SUBSTRING(@ASSN_CHANGE_DATA, @i-10, 1) 
       
        SET @i = @i+30 -- for fast calculations
       END -- IF @Delta between @TASK_START_DATE and @TASK_FINISH_DATE 
    
       SET @i = @i+1 
      END -- WHILE i < @Limit 
    
       INSERT @SPK_ASSN_CHANGE_DATA 
        SELECT @ASSN_UID, RES_SAVED_DATE, h, @ASSN_TRANS_SUBMIT_DATE FROM @BUFFER 
        WHERE NUMBER IN (SELECT MAX(NUMBER) FROM @BUFFER WHERE IsFact = 10 GROUP BY RES_SAVED_DATE) 
      
      --End of binary reverse engineering
      
    
      RETURN
     END -- CREATE FUNCTION SPK_ASSN_CHANGE_DATA (@ASSN_TRANS_UID UNIQUEIDENTIFIER) 

    And second function converting binaries to float (scalar-valued) dbo.fn_BinaryFloatToFloat

    USE [WSS_Content]
    GO
    /****** Object:  UserDefinedFunction [dbo].[fn_BinaryFloatToFloat]    Script Date: 28.07.2020 12:40:13 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER FUNCTION [dbo].[fn_BinaryFloatToFloat] (@BinaryFloat BINARY(8))
    RETURNS FLOAT
    AS
    BEGIN
    	RETURN SIGN(CAST(@BinaryFloat AS BIGINT))
    		* (1.0 + (CAST(@BinaryFloat AS BIGINT) & 0x000FFFFFFFFFFFFF) * POWER(CAST(2 AS FLOAT), -52)) 
           * POWER(CAST(2 AS FLOAT), (CAST(@BinaryFloat AS BIGINT) & 0x7ff0000000000000) / 0x0010000000000000 - 1023) 
     END

    I'm sure it's will help to others.


    MCSA: Security; MCTP: Microsoft ISA 2006


    Tuesday, July 28, 2020 9:42 AM