")"附近的语法不正确。---.SQL

Question

第一组显示错误

select  
attnDate, workLoc, swipeType, fromTime, toTime, timeInterval,  
sum (ECount1 + ECount2) as EmpCount, 
EmpCode 
from
(select
attn_dt as attnDate, att.work_loc as workLoc, 
vEmpDtl.emp_Code as EmpCode,  
case 
when convert(time, in_Ts) between '7:00' and '10:30' then 'IN'   
when convert(time, in_Ts2) between '7:00' and '10:30' then 'IN'  
else ' ' 
end as swipeType, 
count(in_Ts) as ECount1, count(in_Ts2) as ECount2,
td.from_time as fromTime, td.to_time as toTime, 
td.time_interval as timeInterval,  
in_Ts as inTime, out_ts as OutTime  
from 
emp_attn att 
cross join 
time_details td  
inner join 
v_emp_dtls vEmpDtl on att.emp_code = vEmpDtl.emp_code
join 
emp_mst mst with (nolock) on mst.emp_code = vEmpDtl.emp_code  
join 
(select distinct empRep.emp_code  
from emp_reporting_structure empRep  
where empRep.report_to = '18501') o on o.emp_code = vEmpDtl.emp_code 
where
attn_dt between '2023-02-01' and '2023-02-28'  
and vEmpDtl.emp_type = 'COMMON'  
and vEmpDtl.emp_loc_type = 'KSACORP' 
and vEmpDtl.work_loc_id = '300000001672100'  
and vEmpDtl.nationality = 'Sri Lanka'  
and vEmpDtl.payroll_code = 'KSA Retail Payroll'  
and vEmpDtl.country_code = '6486' 
and vEmpDtl.region = 'RIY' 
and (convert(time, in_Ts) between convert(time, td.from_time) and convert(time, td.to_time) or
convert(time, in_Ts2) between convert(time, td.from_time) and convert(time, td.to_time))  
and (convert(time, in_Ts) between '7:00' and '10:30' or  
convert(time, in_Ts2) between '7:00' and '10:30') 
and vEmpDtl.emp_code not in (select distinct emp_code  
from exclude_emp 
where exclusion_type = 'NO_ATTENDANCE')
and mst.contract is null  
and vEmpDtl.employment_status = 'All'  
---  group BY att.attn_dt desc  ,att.emp_code 
) AnyNameYouLikeButYouHaveToGiveOne 
group by 
attn_dt, att.work_loc, in_Ts, out_ts, in_Ts2, out_ts2,
from_time, to_time, time_interval, vEmpDtl.emp_Code) aaa 
group by 
attnDate, timeInterval, workLoc, swipeType, fromTime, toTime, 
ECount1, ECount2, EmpCode 

请建议修改此查询的代码

Answer 1

查询在第一个GROUP BY子句中出现错误。在swipeType之后和FROM之前有额外的逗号。请删除这些逗号，查询应该可以正常运行。

SELECT attnDate, workLoc, swipeType, fromTime, toTime, timeInterval, SUM(ECount1+ECount2) AS EmpCount, EmpCode 
FROM (
SELECT attn_dt AS attnDate, att.work_loc AS workLoc, vEmpDtl.emp_Code AS EmpCode,
CASE WHEN CONVERT(time, in_Ts) BETWEEN '7:00' AND '10:30' THEN 'IN' 
WHEN CONVERT(time, in_Ts2) BETWEEN '7:00' AND '10:30' THEN 'IN' 
ELSE ' ' 
END AS swipeType, 
COUNT(in_Ts) AS ECount1, 
COUNT(in_Ts2) AS ECount2, 
td.from_time AS fromTime, 
td.to_time AS toTime, 
td.time_interval AS timeInterval, 
in_Ts AS inTime, 
out_ts AS OutTime
FROM emp_attn att 
CROSS JOIN time_details td 
INNER JOIN v_emp_dtls vEmpDtl ON att.emp_code = vEmpDtl.emp_code 
JOIN emp_mst mst WITH (NOLOCK) ON mst.emp_code = vEmpDtl.emp_code 
JOIN (
SELECT DISTINCT empRep.emp_code 
FROM emp_reporting_structure empRep 
WHERE empRep.report_to = '18501'
) o ON o.emp_code = vEmpDtl.emp_code 
WHERE attn_dt BETWEEN '2023-02-01' AND '2023-02-28'
AND vEmpDtl.emp_type = 'COMMON' 
AND vEmpDtl.emp_loc_type = 'KSACORP' 
AND vEmpDtl.work_loc_id = '300000001672100' 
AND vEmpDtl.nationality = 'Sri Lanka' 
AND vEmpDtl.payroll_code = 'KSA Retail Payroll' 
AND vEmpDtl.country_code = '6486' 
AND vEmpDtl.region = 'RIY' 
AND (
CONVERT(time, in_Ts) BETWEEN CONVERT(time, td.from_time) AND CONVERT(time, td.to_time) 
OR CONVERT(time, in_Ts2) BETWEEN CONVERT(time, td.from_time) AND CONVERT(time, td.to_time)
) 
AND (
CONVERT(time, in_Ts) BETWEEN '7:00' AND '10:30' 
OR CONVERT(time, in_Ts2) BETWEEN '7:00' AND '10:30'
) 
AND vEmpDtl.emp_code NOT IN (
SELECT DISTINCT emp_code 
FROM exclude_emp 
WHERE exclusion_type ='NO_ATTENDANCE'
) 
AND mst.contract IS NULL 
AND vEmpDtl.employment_status = 'All'
GROUP BY attn_dt, att.work_loc, in_Ts, out_ts, in_Ts2, out_ts2, td.from_time, td.to_time, td.time_interval, vEmpDtl.emp_Code 
) aaa 
GROUP BY attnDate, timeInterval, workLoc, swipeType, fromTime, toTime, EmpCode, ECount1, ECount2