SELECT sunday||'~'||saturday AS weekday,staff_name,SUM(effort) sum
FROM (
SELECT CASE
WHEN to_char(work_date,'D')=1 THEN
to_char(trunc(work_date-7-1),'YYYY-MM-DD')
ELSE
to_char(trunc(next_day(work_date,2)-7-1),'YYYY-MM-DD')
END AS sunday,
CASE
WHEN to_char(work_date,'D')=1 THEN
to_char(trunc(work_date-1),'YYYY-MM-DD')
ELSE
to_char(trunc(next_day(work_date,1)-1),'YYYY-MM-DD')
END
AS
saturday,staff_info.staff_name staff_name,effort
FROM task_info,staff_info
where Task_Info.Staff_Sril=staff_info.staff_sril
and work_date >= to_date('
2007-04-01
','yyyy-mm-dd')
and work_date<=to_date('
2007-05-06
','yyyy-mm-dd')
)
GROUP BY cube(sunday||'~'||saturday,staff_name)
按周分组统计
最新推荐文章于 2024-07-11 17:19:24 发布