oracle PL/Sql查询---获取迟到、早退时间

Question

表A：（考勤记录）
 
AttendanceID             SchedulingID              AttendanceBeginTime          AttendanceEndTime
12-7572TMP00000000       12-7572TMP00000015        2011-04-18 07:26:46.767      NULL
12-7572TMP00000001       12-7572TMP00000016        2011-04-18 15:26:46.767      2011-04-18 22:26:46.767
12-7572TMP00000002       12-7572TMP00000016        2011-04-19 14:26:46.767      2011-04-19 22:12:46.767
12-7572TMP00000003       12-7572TMP00000015        2011-04-20 07:56:46.767      2011-04-20 13:56:46.767
 
表S：（班次信息）
SchedulingID        SchedulingName    SchedulingBeginTime    SchedulingEndTime
12-7572TMP00000015     早班                08:00:00             14:00:00
12-7572TMP00000016     中班                15:00:00             22:00:00
 
 
我想根据以上两张表得到以下信息：（获取迟到早退的时间）
 
AttendanceID             SchedulingName        迟到时间          早退时间
12-7572TMP00000000       早班                  无                      未打卡
12-7572TMP00000001       中班                  00:26:46            无
12-7572TMP00000002       中班                  无                      无
12-7572TMP00000003       早班                  无                      00:03:14

Answer 1

oracle    
create table A
(
  attendanceid        VARCHAR2(50),
  schedulingid        VARCHAR2(50),
  attendancebegintime VARCHAR2(50),
  attendanceendtime   VARCHAR2(50)
);
create table S
(
  schedulingid        VARCHAR2(50),
  schedulingname      VARCHAR2(50),
  schedulingbegintime VARCHAR2(50),
  schedulingendtime   VARCHAR2(50)
);
insert into a (ATTENDANCEID, SCHEDULINGID, ATTENDANCEBEGINTIME, ATTENDANCEENDTIME)
values ('12-7572TMP00000000', '12-7572TMP00000015', '2011-04-18 07:26:46.767', null);insert into a (ATTENDANCEID, SCHEDULINGID, ATTENDANCEBEGINTIME, ATTENDANCEENDTIME)
values ('12-7572TMP00000001', '12-7572TMP00000016', '2011-04-18 15:26:46.767', '2011-04-18 22:26:46.767');insert into a (ATTENDANCEID, SCHEDULINGID, ATTENDANCEBEGINTIME, ATTENDANCEENDTIME)
values ('12-7572TMP00000002', '12-7572TMP00000016', '2011-04-19 14:26:46.767', '2011-04-19 22:12:46.767');insert into a (ATTENDANCEID, SCHEDULINGID, ATTENDANCEBEGINTIME, ATTENDANCEENDTIME)
values ('12-7572TMP00000003', '12-7572TMP00000015', '2011-04-20 07:56:46.767', '2011-04-20 13:56:46.767');
;
insert into s (SCHEDULINGID, SCHEDULINGNAME, SCHEDULINGBEGINTIME, SCHEDULINGENDTIME)
values ('12-7572TMP00000015', '早班', '08:00:00', '14:00:00');insert into s (SCHEDULINGID, SCHEDULINGNAME, SCHEDULINGBEGINTIME, SCHEDULINGENDTIME)
values ('12-7572TMP00000016', '中班', '15:00:00', '22:00:00');

Answer 2

select a.id ,b.name ,case when a.time<=b.time THEN '无' when a.time is null THEN '未打卡' else a.time-b.time END "迟到时间"  FROM t1 a left join t2 b on a.id=b.id大概这样 ，马上下班了 ，时间的转换自己搞下

Answer 3

select a.attendanceid,
       s.schedulingname,
       substr(a.attendancebegintime,12,8) ,
       case when substr(a.attendancebegintime,12,8) <= s.schedulingbegintime then '无'
            when a.attendancebegintime is null then '未打卡'
            else   to_char(numtodsinterval(to_date(substr(a.attendancebegintime,12,8),'hh24:mi:ss') - to_date(s.schedulingbegintime,'hh24:mi:ss'),'DAY'))
       end,
        case when substr(a.attendanceendtime,12,8) >= s.schedulingendtime then '无'
            when a.attendanceendtime is null then '未打卡'
            else   to_char(numtodsinterval(to_date(s.schedulingendtime,'hh24:mi:ss') - to_date(substr(a.attendanceendtime,12,8),'hh24:mi:ss'),'DAY'))
       end          
  from a
  left join s
    on a.schedulingid = s.schedulingid
    order by a.attendanceid

Answer 4

写几个 case when 应该能解决问题