I'm trying to select data from table using Pivot (max())
this is my code:
SELECT t1.EmployeeID, t1.OvertimeDate, t1.Duration, t1.AchievedDuration, t2.TimeOverTimeIn, t2.TimeOverTimeOut
FROM Emp_OverTimeScheduler AS t1 INNER JOIN (
SELECT enrollnumber, [wday],[wmonth],[wYear], [4] as [TimeOverTimeIn], [5] as [TimeOverTimeOut]
FROM (
SELECT whour, wDay, InOutMode, EnrollNumber, wMonth, wYear, wMinute, wsecond
FROM Emp_AttendanceByDevice where InOutMode in (4,5)
) AS DT
PIVOT( MAX( cast(whour as char(4))+':'++cast(wMinute as varchar(2))+':'++cast(wSecond as varchar(2)) )
FOR InOutMode IN([4],[5]))
AS DT
) AS t2 ON t1.EmployeeID = t2.enrollnumber AND day(t1.OvertimeDate) = t2.wDay and MONTH(t1.OvertimeDate)= t2.wmonth
and YEAR (t1.OvertimeDate)=wYear
where EnrollNumber=27 and MONTH(overtimedate)=2
it gives me incorrect syntax near '(' the proble is in the line
PIVOT( MAX( cast(whour as char(4))+':'++cast(wMinute as varchar(2))+':'++cast(wSecond as varchar(2)) )
Any solution please ?