Counting datetime from table for workers

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.

2 Replies

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

Piyush Pansuriya7y ago
Hello,
You are using aggregate function into aggregate like:
SUM() is a aggregage function and you are using DATEPART(), CONVERT() aggregate function into this.
So, it gives error.

You have to think other way like:
if you use select query into nested select you can achive your requirement.
Kalyani Shevale7y ago
if u have use group by then all column added in group by list