I have app for workers work time...
I need if is Saturday od Sunday to work with my code
Without
CASE WHEN WEEKDAY(a.vrijemeodjave) in (1,7) then 0 else
CODE WORKING FINE
Statement is Microsoft SQL 2016
What I have tried:
SELECT a.id, a.redni_broj , a.radnik, isnull(convert(varchar(20), a.vrijemeodjave, 113), '') as vrijemeodjave, convert(varchar(20), b.vrijemeprijave, 113) as vrijemeprijave,
CASE WHEN 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 WEEKDAY(a.vrijemeodjave) in (1,7) then 0 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
FROM(SELECT id, redni_broj, radnik, vrijemeodjave, ROW_Number() OVER(Partition By id ORDER BY vrijemeprijave) as RowNum
FROM dbo.prijava_radnika) a INNER JOIN(SELECT id, redni_broj, radnik, vrijemeprijave, (ROW_Number() OVER(Partition By id ORDER BY vrijemeprijave) - 1) as RowNumMinusOne FROM dbo.prijava_radnika) b ON a.id = b.id where 1 = 1