工人SQL查询的工作时间 [英] Work hours for workers SQL query

问题描述

一些帮助



我在这里发布错误





错误行6和7

Some help

I thinhk its here error


Error 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.

我尝试过：

What I have tried:

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 Bibić' and day(vrijemeprijave) = 2 and MONTH(vrijemeprijave) = 12 AND YEAR(vrijemeprijave) = 2018 
group by p.radnik, p.vrijemeprijave 
order by p.radnik, p.vrijemeprijave

推荐答案

从第5行和第7行中删除 SELECT 一词。



你在这些计算中使用当前行，它不是一个真正的子查询，因此不需要单词 SELECT

Remove the word SELECT from lines 5 and 7.

You are using the current row in those calculations, it's not a genuine sub-query, so there is no need for the word SELECT

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(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(FLOOR((DATEDIFF(SECOND,p.vrijemeodjave, p.vrijemeprijave)/ 3600))) > 8 THEN 8 else (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(CASE WHEN convert(char(5),p.vrijemeodjave) between '06:00' and '06:59' THEN 8 ELSE(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( CASE WHEN convert(char(5),p.vrijemeodjave) between '06:00' and '06:59' THEN( FLOOR((DATEPART(SECOND, (DATEDIFF(SECOND,p.vrijemeodjave, '00:00')) / 3600)))) + 2 ELSE 8 END) else 0 end end) as 'NOCNI RAD' 

这篇关于工人SQL查询的工作时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！