TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
Goran Bibic
482
2.9k
198.1k
Counting datetime from table for workers
Dec 21 2018 2:52 AM
Some help please
SELECT p.radnik
as
RADNIK,
sum(
case
when DATEPART(WEEKDAY, p.vrijemeprijave)
in
(5, 6) then 0
else
(DATEPART(HOUR,p.vrijemeprijave)+1)end)
as
'Vrijeme prijave'
,
sum(
case
when DATEPART(WEEKDAY,p.vrijemeodjave)
in
(5, 6) then 0
else
(DATEPART(HOUR,p.vrijemeodjave))end)
as
'Vrijeme odjave'
,
--DATE_FORMAT(p.VrijemePrijave,
' '
)
as
'DATUM'
,
isnull(convert(varchar(20), p.vrijemeprijave, 104),
''
)
as
DATUM,
sum(
case
when(convert(
char
(5),p.vrijemeprijave) between
'13:00'
and
'22:00'
) and(convert(
char
(5),p.vrijemeodjave) between
'00:00'
and
'04:00'
) then(SELECT FLOOR(DATEPART(HOUR, p.vrijemeprijave)) / 3600)
else
case
when(convert(
char
(5),p.vrijemeprijave) between
'17:00'
and
'22:00'
) and(convert(
char
(5),p.vrijemeodjave) between
'00:00'
and
'15:00'
) then 0
else
(
case
when DATEPART(WEEKDAY,p.vrijemeprijave)
in
(5, 6) then 0
else
(CASE WHEN(SELECT FLOOR((DATEDIFF(SECOND,p.vrijemeodjave, p.vrijemeprijave)/ 3600))) > 8 THEN 8
else
(SELECT FLOOR((DATEDIFF(SECOND, p.vrijemeodjave, p.vrijemeprijave))) / 3600) END) end) end end ) AS
'SATI [h]'
,
sum(
case
when(convert(
char
(5),p.vrijemeprijave) between
'13:00'
and
'22:00'
) and(convert(
char
(5),p.vrijemeodjave) between
'00:00'
and
'12:00'
) then(SELECT CASE WHEN convert(
char
(5),p.vrijemeodjave) between
'06:00'
and
'06:59'
THEN 8 ELSE(SELECT FLOOR((DATEDIFF(SECOND, p.vrijemeodjave,
'00:00'
))) / 3600) + 2 END)
else
case
when(convert(
char
(5),p.vrijemeprijave) between
'17:00'
and
'22:00'
) and(convert(
char
(5),p.vrijemeodjave) between
'00:00'
and
'15:00'
) then(SELECT CASE WHEN convert(
char
(5),p.vrijemeodjave) between
'06:00'
and
'06:59'
THEN(SELECT FLOOR((DATEPART(SECOND, (DATEDIFF(SECOND,p.vrijemeodjave,
'00:00'
)) / 3600)))) + 2 ELSE 8 END)
else
0 end end)
as
'NOCNI RAD'
from prijava_radnika p
--inner join osoba o on p.IdPrijava = p.IdPrijava
where p.radnik =
'Goran Bibic'
and day(vrijemeprijave) = 2 and MONTH(vrijemeprijave) = 12 AND YEAR(vrijemeprijave) = 2018
group by p.radnik, p.vrijemeprijave
order by p.radnik, p.vrijemeprijave
Errors line 6 and 7
Msg 130, Level 15, State 1, Line 6
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
Msg 130, Level 15, State 1, Line 6
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
Msg 130, Level 15, State 1, Line 6
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
Msg 130, Level 15, State 1, Line 7
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
Msg 130, Level 15, State 1, Line 7
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
Reply
Answers (
2
)
how to get output of below query using join
How to Calculate OverTime,Full_Day,Half_Day,Late_In,Hours,