Sql group and multiple oredr by...

Goran Bibic

8y851SQL

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

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sankara Krishnan Venugopal8y ago
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.
Shahriar Nikkhah8y ago
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