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*/