데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

의도에 맞는지 모르겠으나, 아래 쿼리를 참고하세요.

근무했다는 기준이 그 시간대에 걸쳐 있으면 되는건지 애매하네요..

일자별, 시간대별로 기준집합을 만들고 거기에 출근시간과 퇴근시간이 속하는지 체크하는 방식입니다.

세세한 건 가다듬어야 할 것 같네요..

WITH DILIGENCE

(

SELECT A.*

,TRUNC(A.GO_DATETIME, 'HH24') FR_TIME

,TRUNC(A.LEAV_DATETIME, 'HH24') TO_TIME

FROM (

SELECT RTRIM(USER_UID ) USER_UID

,TO_DATE(RTRIM(GO_DATETIME ), 'YYYYMMDDHH24MISS') GO_DATETIME

,TO_DATE(RTRIM(LEAV_DATETIME), 'YYYYMMDDHH24MISS') LEAV_DATETIME

FROM (

SELECT '90021' USER_UID, '20120901055834' GO_DATETIME, '20120901150206' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90020' USER_UID, '20120901055942' GO_DATETIME, '20120901101747' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90019' USER_UID, '20120901060222' GO_DATETIME, '20120901065643' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90019' USER_UID, '20120901065647' GO_DATETIME, '20120901123742' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90002' USER_UID, '20120901071248' GO_DATETIME, ' ' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90001' USER_UID, '20120901071635' GO_DATETIME, ' ' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90006' USER_UID, '20120901083815' GO_DATETIME, '20120901094510' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '99996' USER_UID, '20120901085642' GO_DATETIME, ' ' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90007' USER_UID, '20120901085723' GO_DATETIME, '20120901180125' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90001' USER_UID, '20120901085914' GO_DATETIME, ' ' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90009' USER_UID, '20120901085925' GO_DATETIME, '20120901180227' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90001' USER_UID, '20120901090104' GO_DATETIME, '20120901090124' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90008' USER_UID, '20120901090126' GO_DATETIME, '20120901172326' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90001' USER_UID, '20120901090128' GO_DATETIME, '20120901090150' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90001' USER_UID, '20120901090253' GO_DATETIME, '20120901092249' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90001' USER_UID, '20120901092253' GO_DATETIME, ' ' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90006' USER_UID, '20120901094515' GO_DATETIME, '20120901162142' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90022' USER_UID, '20120901101754' GO_DATETIME, '20120901101802' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90018' USER_UID, '20120901101811' GO_DATETIME, '20120901101823' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90020' USER_UID, '20120901101827' GO_DATETIME, '20120901150040' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90002' USER_UID, '20120901123140' GO_DATETIME, '20120901123155' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90002' USER_UID, '20120901123158' GO_DATETIME, '20120901125013' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90019' USER_UID, '20120901123752' GO_DATETIME, '20120901123906' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90019' USER_UID, '20120901123914' GO_DATETIME, '20120901123935' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90019' USER_UID, '20120901123944' GO_DATETIME, ' ' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90002' USER_UID, '20120901125359' GO_DATETIME, '20120901125440' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90019' USER_UID, '20120901125441' GO_DATETIME, ' ' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90013' USER_UID, '20120901125750' GO_DATETIME, '20120901220034' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90011' USER_UID, '20120901125830' GO_DATETIME, ' ' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90002' USER_UID, '20120901130024' GO_DATETIME, '20120901130042' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90010' USER_UID, '20120901130036' GO_DATETIME, '20120901220013' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90011' USER_UID, '20120901130048' GO_DATETIME, '20120901154439' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90002' USER_UID, '20120901130055' GO_DATETIME, '20120901140135' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90019' USER_UID, '20120901130118' GO_DATETIME, '20120901150029' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90012' USER_UID, '20120901130204' GO_DATETIME, '20120901220005' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90001' USER_UID, '20120901130839' GO_DATETIME, '20120901140630' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90018' USER_UID, '20120901134711' GO_DATETIME, ' ' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90002' USER_UID, '20120901140527' GO_DATETIME, '20120901152652' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90018' USER_UID, '20120901140617' GO_DATETIME, '20120901150110' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90001' USER_UID, '20120901150225' GO_DATETIME, '20120901154518' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90006' USER_UID, '20120901162151' GO_DATETIME, '20120901180219' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90011' USER_UID, '20120901163615' GO_DATETIME, '20120901185102' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90008' USER_UID, '20120901172330' GO_DATETIME, '20120901175946' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90014' USER_UID, '20120901175740' GO_DATETIME, '20120901190413' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90015' USER_UID, '20120901175904' GO_DATETIME, '20120902020924' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90016' USER_UID, '20120901175945' GO_DATETIME, ' ' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90017' USER_UID, '20120901175953' GO_DATETIME, '20120901181748' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90017' USER_UID, '20120901181753' GO_DATETIME, '20120902090008' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90011' USER_UID, '20120901185107' GO_DATETIME, '20120901220003' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90014' USER_UID, '20120901190424' GO_DATETIME, '20120902020248' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '99999' USER_UID, '20120901225016' GO_DATETIME, '20120901225047' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90014' USER_UID, '20120902020252' GO_DATETIME, '20120902044904' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90015' USER_UID, '20120902020928' GO_DATETIME, '20120902090014' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90014' USER_UID, '20120902045059' GO_DATETIME, '20120902090024' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90021' USER_UID, '20120902065825' GO_DATETIME, '20120902160024' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90020' USER_UID, '20120902070023' GO_DATETIME, '20120902160009' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90019' USER_UID, '20120902070107' GO_DATETIME, '20120902135133' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90018' USER_UID, '20120902070335' GO_DATETIME, ' ' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90002' USER_UID, '20120902071414' GO_DATETIME, '20120902071507' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90018' USER_UID, '20120902071525' GO_DATETIME, '20120902160048' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90006' USER_UID, '20120902085844' GO_DATETIME, '20120902092631' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90012' USER_UID, '20120902090020' GO_DATETIME, '20120902180106' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90002' USER_UID, '20120902090025' GO_DATETIME, '20120902090048' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90009' USER_UID, '20120902090037' GO_DATETIME, '20120902180124' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90016' USER_UID, '20120902090055' GO_DATETIME, '20120902090101' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90007' USER_UID, '20120902090134' GO_DATETIME, '20120902180020' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90006' USER_UID, '20120902092635' GO_DATETIME, '20120902100352' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90006' USER_UID, '20120902100356' GO_DATETIME, '20120902102335' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90006' USER_UID, '20120902102338' GO_DATETIME, '20120902102845' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90006' USER_UID, '20120902102849' GO_DATETIME, '20120902103514' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90006' USER_UID, '20120902103518' GO_DATETIME, '20120902105405' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90006' USER_UID, '20120902105408' GO_DATETIME, '20120902180124' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90011' USER_UID, '20120902125202' GO_DATETIME, '20120902133942' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90013' USER_UID, '20120902125922' GO_DATETIME, '20120902220041' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90010' USER_UID, '20120902130009' GO_DATETIME, '20120902164322' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90008' USER_UID, '20120902130241' GO_DATETIME, '20120902220026' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90011' USER_UID, '20120902134117' GO_DATETIME, '20120902141614' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90019' USER_UID, '20120902135139' GO_DATETIME, '20120902160023' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90011' USER_UID, '20120902141624' GO_DATETIME, '20120902220003' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '99999' USER_UID, '20120902161849' GO_DATETIME, '20120902162003' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90010' USER_UID, '20120902164325' GO_DATETIME, '20120902164330' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90010' USER_UID, '20120902164336' GO_DATETIME, '20120902164353' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90010' USER_UID, '20120902164356' GO_DATETIME, '20120902164406' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90002' USER_UID, '20120902164412' GO_DATETIME, '20120902164416' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90010' USER_UID, '20120902164424' GO_DATETIME, '20120902164432' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90010' USER_UID, '20120902164438' GO_DATETIME, '20120902164443' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90010' USER_UID, '20120902164455' GO_DATETIME, '20120902164503' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90010' USER_UID, '20120902164604' GO_DATETIME, '20120902164609' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90010' USER_UID, '20120902164614' GO_DATETIME, '20120902220049' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90016' USER_UID, '20120902175747' GO_DATETIME, '20120903003619' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90014' USER_UID, '20120902175830' GO_DATETIME, '20120903090018' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90017' USER_UID, '20120902180011' GO_DATETIME, '20120903090040' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90015' USER_UID, '20120902180015' GO_DATETIME, '20120902214101' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '99999' USER_UID, '20120902193038' GO_DATETIME, '20120902193251' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90015' USER_UID, '20120902214104' GO_DATETIME, '20120902214214' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90015' USER_UID, '20120902214219' GO_DATETIME, '20120902214518' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90015' USER_UID, '20120902214521' GO_DATETIME, '20120903090006' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90016' USER_UID, '20120903003718' GO_DATETIME, '20120903090017' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90003' USER_UID, '20120903065744' GO_DATETIME, '20120903160457' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90021' USER_UID, '20120903065929' GO_DATETIME, '20120903160033' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90004' USER_UID, '20120903065955' GO_DATETIME, '20120903160053' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90022' USER_UID, '20120903085857' GO_DATETIME, '20120903180125' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90007' USER_UID, '20120903085919' GO_DATETIME, '20120903093429' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90024' USER_UID, '20120903085943' GO_DATETIME, '20120903180115' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90023' USER_UID, '20120903090920' GO_DATETIME, '20120903180023' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '99998' USER_UID, '20120903092251' GO_DATETIME, '20120903093846' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '99999' USER_UID, '20120903092641' GO_DATETIME, ' ' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90025' USER_UID, '20120903093445' GO_DATETIME, '20120903180118' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90001' USER_UID, '20120903093816' GO_DATETIME, '20120903155419' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '99998' USER_UID, '20120903093941' GO_DATETIME, '20120903094446' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90019' USER_UID, '20120903125619' GO_DATETIME, '20120903204131' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90005' USER_UID, '20120903130038' GO_DATETIME, '20120903220106' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90020' USER_UID, '20120903130107' GO_DATETIME, ' ' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90018' USER_UID, '20120903130208' GO_DATETIME, '20120903155627' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90002' USER_UID, '20120903132757' GO_DATETIME, ' ' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '99998' USER_UID, '20120903141524' GO_DATETIME, '20120903142058' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '77777' USER_UID, '20120903143318' GO_DATETIME, '20120903144446' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90020' USER_UID, '20120903144400' GO_DATETIME, ' ' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '77777' USER_UID, '20120903144453' GO_DATETIME, '20120903145302' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '99999' USER_UID, '20120903145336' GO_DATETIME, '20120903193808' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '99998' USER_UID, '20120903145434' GO_DATETIME, ' ' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90020' USER_UID, '20120903150253' GO_DATETIME, '20120903190649' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '99998' USER_UID, '20120903151140' GO_DATETIME, ' ' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '99998' USER_UID, '20120903151657' GO_DATETIME, '20120903154558' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '77777' USER_UID, '20120903154603' GO_DATETIME, ' ' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90018' USER_UID, '20120903155631' GO_DATETIME, '20120903215955' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90001' USER_UID, '20120903165906' GO_DATETIME, '20120903165925' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90002' USER_UID, '20120903170321' GO_DATETIME, '20120903184155' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90001' USER_UID, '20120903170609' GO_DATETIME, '20120903183452' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90007' USER_UID, '20120903175817' GO_DATETIME, '20120904090012' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90006' USER_UID, '20120903175830' GO_DATETIME, '20120904044045' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90008' USER_UID, '20120903175916' GO_DATETIME, '20120904090043' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '77777' USER_UID, '20120903185343' GO_DATETIME, ' ' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '99998' USER_UID, '20120903190253' GO_DATETIME, '20120903190625' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90020' USER_UID, '20120903190658' GO_DATETIME, '20120903200220' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90020' USER_UID, '20120903200222' GO_DATETIME, '20120903220007' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '90019' USER_UID, '20120903204140' GO_DATETIME, '20120903220051' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '99998' USER_UID, '20120903210301' GO_DATETIME, '20120903210445' LEAV_DATETIME FROM DUAL UNION ALL

SELECT '99998' USER_UID, '20120903234458' GO_DATETIME, '20120903234957' LEAV_DATETIME FROM DUAL

)

) A

)

--SELECT * FROM DILIGENCE ORDER BY GO_DATETIME, LEAV_DATETIME, USER_UID;

SELECT A.STD_DT

,COUNT(A.CHK00) CHK00

,COUNT(A.CHK01) CHK01

,COUNT(A.CHK02) CHK02

,COUNT(A.CHK03) CHK03

,COUNT(A.CHK04) CHK04

,COUNT(A.CHK05) CHK05

,COUNT(A.CHK06) CHK06

,COUNT(A.CHK07) CHK07

,COUNT(A.CHK08) CHK08

,COUNT(A.CHK09) CHK09

,COUNT(A.CHK10) CHK10

,COUNT(A.CHK11) CHK11

,COUNT(A.CHK12) CHK12

,COUNT(A.CHK13) CHK13

,COUNT(A.CHK14) CHK14

,COUNT(A.CHK15) CHK15

,COUNT(A.CHK16) CHK16

,COUNT(A.CHK17) CHK17

,COUNT(A.CHK18) CHK18

,COUNT(A.CHK19) CHK19

,COUNT(A.CHK20) CHK20

,COUNT(A.CHK21) CHK21

,COUNT(A.CHK22) CHK22

,COUNT(A.CHK23) CHK23

FROM (

SELECT S.STD_DT

,A.USER_UID

,A.GO_DATETIME

,A.LEAV_DATETIME

,A.FR_TIME

,A.TO_TIME

,CASE WHEN S.HR00 BETWEEN A.FR_TIME AND A.TO_TIME THEN 1 END CHK00

,CASE WHEN S.HR01 BETWEEN A.FR_TIME AND A.TO_TIME THEN 1 END CHK01

,CASE WHEN S.HR02 BETWEEN A.FR_TIME AND A.TO_TIME THEN 1 END CHK02

,CASE WHEN S.HR03 BETWEEN A.FR_TIME AND A.TO_TIME THEN 1 END CHK03

,CASE WHEN S.HR04 BETWEEN A.FR_TIME AND A.TO_TIME THEN 1 END CHK04

,CASE WHEN S.HR05 BETWEEN A.FR_TIME AND A.TO_TIME THEN 1 END CHK05

,CASE WHEN S.HR06 BETWEEN A.FR_TIME AND A.TO_TIME THEN 1 END CHK06

,CASE WHEN S.HR07 BETWEEN A.FR_TIME AND A.TO_TIME THEN 1 END CHK07

,CASE WHEN S.HR08 BETWEEN A.FR_TIME AND A.TO_TIME THEN 1 END CHK08

,CASE WHEN S.HR09 BETWEEN A.FR_TIME AND A.TO_TIME THEN 1 END CHK09

,CASE WHEN S.HR10 BETWEEN A.FR_TIME AND A.TO_TIME THEN 1 END CHK10

,CASE WHEN S.HR11 BETWEEN A.FR_TIME AND A.TO_TIME THEN 1 END CHK11

,CASE WHEN S.HR12 BETWEEN A.FR_TIME AND A.TO_TIME THEN 1 END CHK12

,CASE WHEN S.HR13 BETWEEN A.FR_TIME AND A.TO_TIME THEN 1 END CHK13

,CASE WHEN S.HR14 BETWEEN A.FR_TIME AND A.TO_TIME THEN 1 END CHK14

,CASE WHEN S.HR15 BETWEEN A.FR_TIME AND A.TO_TIME THEN 1 END CHK15

,CASE WHEN S.HR16 BETWEEN A.FR_TIME AND A.TO_TIME THEN 1 END CHK16

,CASE WHEN S.HR17 BETWEEN A.FR_TIME AND A.TO_TIME THEN 1 END CHK17

,CASE WHEN S.HR18 BETWEEN A.FR_TIME AND A.TO_TIME THEN 1 END CHK18

,CASE WHEN S.HR19 BETWEEN A.FR_TIME AND A.TO_TIME THEN 1 END CHK19

,CASE WHEN S.HR20 BETWEEN A.FR_TIME AND A.TO_TIME THEN 1 END CHK20

,CASE WHEN S.HR21 BETWEEN A.FR_TIME AND A.TO_TIME THEN 1 END CHK21

,CASE WHEN S.HR22 BETWEEN A.FR_TIME AND A.TO_TIME THEN 1 END CHK22

,CASE WHEN S.HR23 BETWEEN A.FR_TIME AND A.TO_TIME THEN 1 END CHK23

FROM DILIGENCE A

-- 일자별 시간에 대한 집합

SELECT DT STD_DT

,MAX(DECODE(HR, 0, DT + HR/24)) HR00

,MAX(DECODE(HR, 1, DT + HR/24)) HR01

,MAX(DECODE(HR, 2, DT + HR/24)) HR02

,MAX(DECODE(HR, 3, DT + HR/24)) HR03

,MAX(DECODE(HR, 4, DT + HR/24)) HR04

,MAX(DECODE(HR, 5, DT + HR/24)) HR05

,MAX(DECODE(HR, 6, DT + HR/24)) HR06

,MAX(DECODE(HR, 7, DT + HR/24)) HR07

,MAX(DECODE(HR, 8, DT + HR/24)) HR08

,MAX(DECODE(HR, 9, DT + HR/24)) HR09

,MAX(DECODE(HR, 10, DT + HR/24)) HR10

,MAX(DECODE(HR, 11, DT + HR/24)) HR11

,MAX(DECODE(HR, 12, DT + HR/24)) HR12

,MAX(DECODE(HR, 13, DT + HR/24)) HR13

,MAX(DECODE(HR, 14, DT + HR/24)) HR14

,MAX(DECODE(HR, 15, DT + HR/24)) HR15

,MAX(DECODE(HR, 16, DT + HR/24)) HR16

,MAX(DECODE(HR, 17, DT + HR/24)) HR17

,MAX(DECODE(HR, 18, DT + HR/24)) HR18

,MAX(DECODE(HR, 19, DT + HR/24)) HR19

,MAX(DECODE(HR, 20, DT + HR/24)) HR20

,MAX(DECODE(HR, 21, DT + HR/24)) HR21

,MAX(DECODE(HR, 22, DT + HR/24)) HR22

,MAX(DECODE(HR, 23, DT + HR/24)) HR23

FROM (

SELECT TO_DATE('20120901', 'YYYYMMDD') + LEVEL - 1 DT

FROM DUAL

CONNECT BY LEVEL <= 5

) A

SELECT LEVEL - 1 HR

FROM DUAL

CONNECT BY LEVEL <= 24

)

GROUP BY DT

) S

ORDER BY S.STD_DT

,A.USER_UID

) A

GROUP BY A.STD_DT

ORDER BY A.STD_DT

;

m님이 2012-09-21 14:57에 작성한 댓글입니다. Edit Ｘ