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

CREATE TABLE A
    (
      AttendanceID VARCHAR(50) ,
      SchedulingID VARCHAR(50) ,
      AttendanceBeginTime DATETIME ,
      AttendanceEndTime DATETIME
    )CREATE TABLE S
    (
      SchedulingID VARCHAR(50) ,
      SchedulingName VARCHAR(50) ,
      SchedulingBeginTime VARCHAR(50) ,
      SchedulingEndTime VARCHAR(50)
    )INSERT  INTO dbo.A
        SELECT  '12-7572TMP00000000' ,
                '12-7572TMP00000015' ,
                '2011-04-18 07:26:46.767' ,
                NULL
        UNION ALL
        SELECT  '12-7572TMP00000001' ,
                '12-7572TMP00000016' ,
                '2011-04-18 15:26:46.767' ,
                '2011-04-18 22:26:46.767'
        UNION ALL
        SELECT  '12-7572TMP00000002' ,
                '12-7572TMP00000016' ,
                '2011-04-19 14:26:46.767' ,
                '2011-04-19 22:12:46.767'
        UNION ALL
        SELECT  '12-7572TMP00000003' ,
                '12-7572TMP00000015' ,
                '2011-04-20 07:56:46.767' ,
                '2011-04-20 13:56:46.767'INSERT  INTO S
        SELECT  '12-7572TMP00000015' ,
                '早班' ,
                '08:00:00' ,
                '14:00:00'
        UNION ALL
        SELECT  '12-7572TMP00000016' ,
                '中班' ,
                '15:00:00' ,
                '22:00:00'