Sql group and multiple oredr by...

Question

Like this...
error is Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'group'.

SELECT a.radnik, isnull(convert(varchar(20), a.vrijemeodjave, 113), '') as vrijemeodjave, convert(varchar(20), b.vrijemeprijave, 113) as vrijemeprijave,
--CASE WHEN DATEPART(WEEKDAY, b.vrijemeprijave) in (1,7) then 0 else (CASE WHEN (DATEDIFF(SECOND, b.vrijemeprijave, a.vrijemeodjave) / 3600) > 8 THEN 8 ELSE(DATEDIFF(SECOND, b.vrijemeprijave, a.vrijemeodjave) / 3600) END) end AS radni_sati,
--CASE WHEN DATEPART(WEEKDAY, a.vrijemeodjave) in (1,7) then (DATEDIFF(SECOND, b.vrijemeprijave, a.vrijemeodjave) / 3600) else(case when(DATEDIFF(SECOND, b.vrijemeprijave, a.vrijemeodjave) / 3600) > 8 then ((DATEDIFF(SECOND, b.vrijemeprijave, a.vrijemeodjave) / 3600)-8) ELSE NULL END) end AS prekovremeni
SUM(cast((CASE WHEN DATEPART(WEEKDAY, 01) in (1, 7) then 0 else (CASE WHEN(DATEDIFF(SECOND, 01, 3) / 3600) > 8 THEN 8 ELSE(DATEDIFF(SECOND, 01, 3) / 3600) END) end)as float)) AS ukupno_radni_sati,
SUM(cast((CASE WHEN DATEPART(WEEKDAY, 3) in (1, 7) then(DATEDIFF(SECOND, 01, 3) / 3600) else(case when(DATEDIFF(SECOND, 01, 3) / 3600) > 8 then((DATEDIFF(SECOND, 01, 3) / 3600) - 8) ELSE NULL END) end)as float)) AS 'Ukupno rekovremeni'
FROM(SELECT radnik, vrijemeodjave, ROW_Number() OVER(Partition By IdPrijava ORDER BY vrijemeprijave) as RowNum
FROM dbo.prijava_radnika) a INNER JOIN(SELECT radnik, vrijemeprijave, (ROW_Number() OVER(Partition By IdPrijava ORDER BY vrijemeprijave) - 1) as RowNumMinusOne FROM dbo.prijava_radnika)
group by a.radnik

Shahriar Nikkhah · Answer

The problem is the script is not orgenized, not readable You have INNER JOIN with no ON clause?????? you are missing the ON clause of the INNER JOIN Check line 17 Good luck SELECT a.radnik , isnull ( convert ( varchar (20), a.vrijemeodjave, 113), '' ) as vrijemeodjave , convert ( varchar (20), b.vrijemeprijave, 113) as vrijemeprijave --CASE WHEN DATEPART(WEEKDAY, b.vrijemeprijave) in (1,7) then 0 else (CASE WHEN (DATEDIFF(SECOND, b.vrijemeprijave, a.vrijemeodjave) / 3600) > 8 THEN 8 ELSE(DATEDIFF(SECOND, b.vrijemeprijave, a.vrijemeodjave) / 3600) END) end AS radni_sati, --CASE WHEN DATEPART(WEEKDAY, a.vrijemeodjave) in (1,7) then (DATEDIFF(SECOND, b.vrijemeprijave, a.vrijemeodjave) / 3600) else(case when(DATEDIFF(SECOND, b.vrijemeprijave, a.vrijemeodjave) / 3600) > 8 then ((DATEDIFF(SECOND, b.vrijemeprijave, a.vrijemeodjave) / 3600)-8) ELSE NULL END) end AS prekovremeni , SUM ( cast (( CASE WHEN DATEPART(WEEKDAY, 01) in (1, 7) then 0 else ( CASE WHEN (DATEDIFF( SECOND , 01, 3) / 3600) > 8 THEN 8 ELSE (DATEDIFF( SECOND , 01, 3) / 3600) END ) end ) as float )) AS ukupno_radni_sati , SUM ( cast (( CASE WHEN DATEPART(WEEKDAY, 3) in (1, 7) then (DATEDIFF( SECOND , 01, 3) / 3600) else ( case when (DATEDIFF( SECOND , 01, 3) / 3600) > 8 then ((DATEDIFF( SECOND , 01, 3) / 3600) - 8) ELSE NULL END ) end ) as float )) AS 'Ukupno rekovremeni' FROM ( SELECT radnik , vrijemeodjave , ROW_Number() OVER(Partition By IdPrijava ORDER BY vrijemeprijave) as RowNum FROM dbo.prijava_radnika ) a INNER JOIN ( SELECT radnik , vrijemeprijave , (ROW_Number() OVER(Partition By IdPrijava ORDER BY vrijemeprijave) - 1) as RowNumMinusOne FROM dbo.prijava_radnika ) group by a.radnik

Sankara Krishnan Venugopal · Answer

HI, You sould follow the SQL structure to build your query. 1. You should include the "ON" clause after INNER JION 2. You should not use the open and close brace after the Inner join. This will work. Thanks.