CLIENT SUPPORT » Bank Woori Saudara bugs

??ALTER PROCEDURE [dbo].[CBBGTMTOPYOVERTIMEBRACKETDUR]

(

	@EmployeeID VARCHAR(20)

	,@StartDate VARCHAR(20)

	,@EndDate VARCHAR(20)

	,@WSType VARCHAR(20)

)

AS

--DECLARE @EmployeeID VARCHAR(20) = '00000118'

--DECLARE @StartDate VARCHAR(20) = '20241001'

--DECLARE @EndDate VARCHAR(20) = '20241031'

--DECLARE @WSType VARCHAR(20) = ''

DECLARE @TOVT TABLE

(

	EmployeeID VARCHAR(20)

	,StartDate VARCHAR(20)

	,EndDate VARCHAR(20)

	,DayType VARCHAR(20)

	,Flag VARCHAR(20)

	,Duration DECIMAL(18,2)

	,OT150 DECIMAL(18,2)

	,OT200 DECIMAL(18,2)

	,OT300 DECIMAL(18,2)

	,OT400 DECIMAL(18,2)

)

INSERT INTO @TOVT

SELECT DISTINCT CASE WHEN pa16.EmployeeID <> '' THEN pa16.EmployeeID ELSE @EmployeeID END AS EmployeeID

				,pa16.StartDate, pa16.EndDate, pa16.DayType, daytype.Flag, pa16.Duration

				,CASE WHEN daytype.Flag = 'WORK' THEN CASE WHEN pa16.Duration >= 1 THEN 1 ELSE pa16.Duration END ELSE 0 END AS OT150--0 END AS OT150

				,CASE 

					WHEN daytype.Flag = 'WORK' THEN CASE WHEN pa16.Duration > 1 AND pa16.Duration <= 24 THEN pa16.Duration - 1 END 

					WHEN daytype.Flag = 'FREE' THEN CASE WHEN pa16.Duration < 9 THEN pa16.Duration ELSE 8  END 

					ELSE 0

				 END AS OT200

				,CASE 

					WHEN daytype.Flag = 'FREE' THEN CASE WHEN pa16.Duration > 9 THEN 1 ELSE 0 END 

					ELSE 0

				 END AS OT300

				,CASE 

					WHEN daytype.Flag = 'FREE' THEN CASE WHEN pa16.Duration > 9 THEN pa16.Duration - 9 ELSE 0 END 

					ELSE 0

				 END AS OT400

FROM dbo.PHRPA0016 AS pa16

LEFT JOIN dbo.PHRTMDAYTYP AS daytype ON daytype.DayType = pa16.DayType

WHERE (pa16.StartDate BETWEEN @StartDate AND @EndDate)

AND (pa16.EmployeeID = @EmployeeID OR @EmployeeID = '') 

AND pa16.Variant = 'OVT'

SELECT a.EmployeeID, ISNULL(b.OT150,0) AS OT150, ISNULL(b.OT200,0) AS OT200, ISNULL(b.OT300,0) AS OT300, ISNULL(b.OT400,0) AS OT400

FROM ( SELECT @EmployeeID AS EmployeeID, 0 AS OT150, 0 AS OT200, 0 AS OT300, 0 AS OT400 ) AS a

LEFT JOIN

(SELECT EmployeeID, SUM(OT150) AS OT150, SUM(OT200) AS OT200, SUM(OT300) AS OT300, SUM(OT400) AS OT400

FROM @TOVT

GROUP BY EmployeeID) AS b

ON a.EmployeeID = b.EmployeeID

/*SELECT EmployeeID, SUM(OT150) AS OT150, SUM(OT200) AS OT200, SUM(OT300) AS OT300, SUM(OT400) AS OT400

FROM @TOVT

GROUP BY EmployeeID*/