FUNCTION LAST WORKING DATE

 USE [SystemTracker]

GO

/****** Object:  UserDefinedFunction [dbo].[FN_LAST_WORKINGDATE]    Script Date: 17-11-2021 15:31:30 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/*---------------------------------------------------------------------------------------------------------

FUNCTION NAME       : FN_LAST_WORKINGDATE

FUNCTION / PURPOSE : FUNCTION CALCULATES THE LAST WORKING DAY OF THE USER

AUTHOR NAME : PANKAJ SAINI(2470)

CREATED DATE : 06-NOV-2017

REVISION HISTORY 1 : BY: PANKAJ SAINI -- ON: 06-NOV-2017 -- REVISION DETAILS: NEW FUNCTION CREATED.

-----------------------------------------------------------------------------------------------------------

--EXECUTION STATEMENT

 

 DECLARE @RET DATE

 EXEC @RET =  FN_LAST_WORKINGDATE @EMP_USER_ID = 3299, @Nth_DATE = 5;

 SELECT @RET

    

-----------------------------------------------------------------------------------------------------------*/

ALTER FUNCTION [dbo].[FN_LAST_WORKINGDATE](@EMP_USER_ID INT, @Nth_DATE INT = 0)

RETURNS DATE AS

BEGIN

---------------------------------------------------------------------------------------------

-- DEFINED PARAMETER(S)    | VARIABLE DATA TYPE | DEFAULT VAL -- COMMENTS

---------------------------------------------------------------------------------------------

DECLARE @LAST_WRKNG_DAY DATE 

--SET @LAST_WRKNG_DAY =  (SELECT MAX(CONVERT(DATE,LAST_UPDATE_DATE))

-- FROM ST_APPLICATION_LOGS

-- WHERE CAST(LAST_UPDATE_DATE AS DATE) < ( SELECT MAX(CONVERT(DATE,LAST_UPDATE_DATE))

-- FROM ST_APPLICATION_LOGS WHERE  USERID = @EMP_USER_ID) AND USERID = @EMP_USER_ID AND PROCESS_NAME NOT IN ('IDLE','LOCKAPP','LOCKAPPHOST'))

IF @Nth_DATE = 0

BEGIN

SET @LAST_WRKNG_DAY =  (SELECT MAX(CONVERT(DATE,LAST_UPDATE_DATE))

FROM ST_APPLICATION_LOGS_FULL_DATA

WHERE CAST(LAST_UPDATE_DATE AS DATE) < CAST(getdate() AS DATE)

AND DATENAME(WEEKDAY, LAST_UPDATE_DATE) NOT IN ('SATURDAY', 'SUNDAY')

-- ( SELECT MAX(CONVERT(DATE,LAST_UPDATE_DATE)) FROM ST_APPLICATION_LOGS WHERE  USERID = @EMP_USER_ID) 

AND EMP_USER_ID = @EMP_USER_ID AND PROCESS_NAME NOT IN (SELECT WEB_DESCRIPTION 

FROM ST_WEB_PRODUCTIVITY_MST 

WHERE PRODUCTIVITY_INDEX IN (10)))

END

ELSE

BEGIN

WITH CTE AS

(

SELECT DISTINCT TOP (@Nth_DATE) CONVERT(DATE, LAST_UPDATE_DATE) AS LAST_UPDATE_DATE

FROM ST_APPLICATION_LOGS_FULL_DATA

WHERE CAST(LAST_UPDATE_DATE AS DATE) <= CAST(getdate() AS DATE)

AND DATENAME(WEEKDAY, LAST_UPDATE_DATE) NOT IN ('SATURDAY', 'SUNDAY')

AND EMP_USER_ID = @EMP_USER_ID AND PROCESS_NAME NOT IN (SELECT WEB_DESCRIPTION 

FROM ST_WEB_PRODUCTIVITY_MST 

WHERE PRODUCTIVITY_INDEX IN (10))

ORDER BY CONVERT(DATE, LAST_UPDATE_DATE) DESC

)

SELECT TOP (1) @LAST_WRKNG_DAY =  LAST_UPDATE_DATE

FROM CTE

ORDER BY LAST_UPDATE_DATE

END

RETURN @LAST_WRKNG_DAY; --II MAXIMUM SALARY LOGIC

END