FUNCTION CALCULATES THE TOTAL WORKING TIME ON THE LAST WORKING DAY OF CERTAIN USER
USE [SystemTracker]
GO
/****** Object: UserDefinedFunction [dbo].[FN_TTL_WRKNG_TM_LST_DAY] Script Date: 17-11-2021 15:33:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*---------------------------------------------------------------------------------------------------------
FUNCTION NAME : FN_TTL_WRKNG_TM_LST_DAY_02AUG
FUNCTION / PURPOSE : FUNCTION CALCULATES THE TOTAL WORKING TIME ON THE LAST WORKING DAY OF CERTAIN USER
AUTHOR NAME : PANKAJ SAINI(2470)
CREATED DATE : 08-FEB-2018
REVISION HISTORY 1 : BY: PANKAJ SAINI -- ON: 08-FEB-2018 -- REVISION DETAILS: NEW FUNCTION CREATED.
-----------------------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------------
EXECUTION STATEMENT-->
--------------------
DECLARE @RET FLOAT
EXEC @RET = [FN_TTL_WRKNG_TM_LST_DAY] @EMP_USER_ID = 2323 ,@DATE = '2019-11-29', @OPTION = 1;
SELECT @RET AS [TOTAL TIME]
SELECT(@RET / 60)
-----------------------------------------------------------------------------------------------------------*/
ALTER FUNCTION [dbo].[FN_TTL_WRKNG_TM_LST_DAY](@EMP_USER_ID INT, @DATE DATE, @OPTION INT)
RETURNS INT AS
BEGIN
-- option 0-> 8.75 (525)
-- option 1-> actual time
DECLARE @TTL_TM INT,@TTL_TM_TEMP INT ,@LASTLOGOUT DATETIME ,@FIRSTLOGIN DATETIME
SET @FIRSTLOGIN = (SELECT MIN(ACTIVITY_TIME)
FROM ST_SYSTEM_ACTIVITIES
WHERE EMP_USER_ID = @EMP_USER_ID
AND CAST(ACTIVITY_TIME AS DATE) = @DATE)
SET @LASTLOGOUT = (SELECT MAX(ACTIVITY_TIME)
FROM ST_SYSTEM_ACTIVITIES
WHERE EMP_USER_ID = @EMP_USER_ID
AND CAST(ACTIVITY_TIME AS DATE) = @DATE)
SET @TTL_TM = DATEDIFF(MINUTE, @FIRSTLOGIN, @LASTLOGOUT)
IF @OPTION = 0 -- IF OTION SELECTED AS STANDARD TIME
BEGIN
IF @TTL_TM >= 555
BEGIN
SET @TTL_TM = @TTL_TM
END
END
--IF @TTL_TM >= 555
--BEGIN
-- SET @TTL_TM = 525
--END
--IF @TTL_TM < 555
--BEGIN
--IF DATEPART(HOUR, @LASTLOGOUT) > 13 AND DATEPART(HOUR, @FIRSTLOGIN) < 15
--BEGIN
-- SET @TTL_TM = @TTL_TM-30
--END
--ELSE
--BEGIN
-- SET @TTL_TM = @TTL_TM ;
--END
--END
IF @TTL_TM = 0
BEGIN
SET @TTL_TM_TEMP = (SELECT DATEDIFF(MINUTE, MIN(LAST_UPDATE_DATE), MAX(LAST_UPDATE_DATE))
FROM ST_APPLICATION_LOGS
WHERE EMP_USER_ID = @EMP_USER_ID
AND CAST(LAST_UPDATE_DATE AS DATE) = @DATE
AND PROCESS_DESCRIPTION NOT IN (SELECT WEB_DESCRIPTION
FROM ST_WEB_PRODUCTIVITY_MST
WHERE PRODUCTIVITY_INDEX IN (10)))
IF @TTL_TM_TEMP > 0
BEGIN
SET @TTL_TM = @TTL_TM_TEMP;
END
END
RETURN @TTL_TM;
END
Comments
Post a Comment