Click here to Skip to main content
15,887,585 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I'm trying to select data from table using Pivot (max())
this is my code:

SQL
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
    --group by overtimedate,
   -- and month(t1.OvertimeDate)= t2.wmonth
    where EnrollNumber=27 and MONTH(overtimedate)=2


it gives me incorrect syntax near '(' the proble is in the line
SQL
PIVOT( MAX( cast(whour as char(4))+':'++cast(wMinute as varchar(2))+':'++cast(wSecond as varchar(2)) )


Any solution please ?
Posted
Comments
Andy Lanng 25-Jun-15 12:30pm    
why are you using '++' to concatenate. I have never see that syntax before. Are you sure it shouldn't be single '+' ?
Leila Toumi 25-Jun-15 12:35pm    
Thanks for your reply, I eliminate the second + but i get the same error !!
the query: MAX( cast(whour as char(4))+':'++cast(wMinute as varchar(2))+':'++cast(wSecond as varchar(2)) ) works successfully but when i put it in the Pivot it gives me the previous error !! any idea please?
Leila Toumi 25-Jun-15 13:07pm    
thanks mathi for your reply, can you give me an example please ?
Mathi Mani 25-Jun-15 15:22pm    
Can you update your question with some sample data for the tables used in the query? It may help to determine what might be causing the issue?
j snooze 25-Jun-15 16:48pm    
instead of doing the concatenating in the max() pivot area. Do it in your select as a field
So SELECT whour, wDay, InOutMode, EnrollNumber, wMonth, wYear, wMinute, wsecond,cast(whour as char(4))+':'++cast(wMinute as varchar(2))+':'++cast(wSecond as varchar(2)) as ConcatTime

Then
Pivot(Max(concatTime)

Thats my suggestion other than making sure with all those parentheses you aren't missing one somewhere.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900