数据晒选

with EffectData as (
    select t.no, max(t.name) as name, max(t.dept) as dept, t.tickdate as tickdate, min(t.ticktime) as tickin, max(t.ticktime) as tickout
    from originaldata t group by t.no, t.tickdate order by t.no, t.tickdate
), UpperData as (
    select t.*,
    round(to_number(substr(t.tickout,1,2))-to_number(substr(t.tickin,1,2))+(to_number(substr(t.tickout,3,2))-to_number(substr(t.tickin,3,2)))/60, 2) as tickLength,
    to_char(to_date(t.tickdate, 'yyMMdd'),'d')-1 as week from EffectData t
)   
select t.No as 工号, t.Name as 姓名, t.dept as 部门, t.tickdate as 日期, t.tickin as 上班时间, t.tickout as 下班时间,
    t.week as 星期, t.tickLength 上班时长, (case when t.week>=1 and t.week<=5 then 9 else 0 end) as 应上班时长,
    null as 加班时长
    from UpperData t