날짜 별 체류시간을 구하는 쿼리를 짜고 있습니다.
TBLVISITMASTER 테이블에 VarChar2 형식으로 입점,퇴점시간(INTIME,OUTTIME)이
2009-03-11 11:44:39 이와 같은 형식으로 저장 되어있습니다.
SELECT
t4.LOCATIONGROUPNAME --지역명
,t5.USERNAME -- 담당자 명
,t6.FRANCHISEFORMNAME --가맹점형태
,t7.FRANCHISETYPENAME -- 가맹점종류
,t8.CUSTOMERSECTIONNAME -- 가맹점 구분
,T9.VISITPERIODNAME
,t1.CUSTOMERCODE
,t3.CUSTOMERNAME -- 거래처 명
,t1.VISITDATE CDATE
,to_timestamp(to_char(TO_DATE(t1.OUTTIME, 'YYYY-MM-DD HH24:MI:SS'),'yyyymmddHH24miss'), 'YYYYMMDDHH24MISSFF6')
- to_timestamp(to_char(TO_DATE(t1.INTIME, 'YYYY-MM-DD HH24:MI:SS'),'yyyymmddHH24miss'), 'YYYYMMDDHH24MISSFF6') as WORKTIME
FROM
TBLVISITMASTER T1
LEFT JOIN TBLCUSTOMERMASTER t3 ON ( t1.CUSTOMERCODE = t3.CUSTOMERCODE)
LEFT JOIN TBLLOCATIONGROUPCODE t4 ON ( t3.LOCATIONGROUPCODE = t4.LOCATIONGROUPCODE)
LEFT JOIN TBLUSERMASTER t5 ON ( t1.USERID = t5.USERID )
LEFT JOIN TBLFRANCHISEFORMCODE t6 ON ( t3.FRANCHISEFORMCODE = t6.FRANCHISEFORMCODE)
LEFT JOIN TBLFRANCHISETYPECODE t7 ON ( t3.FRANCHISETYPECODE = t7.FRANCHISETYPECODE)
LEFT JOIN TBLCUSTOMERSECTIONCODE t8 ON ( t3.CUSTOMERSECTIONCODE = t8.CUSTOMERSECTIONCODE )
LEFT JOIN TBLVISITPERIODCODE T9 ON ( T3.VISITPERIOD = T9.VISITPERIODCODE )
WHERE
VISITDATE BETWEEN '2010-02-19' AND '2010-02-21'
GROUP BY
t4.LOCATIONGROUPNAME --지역명
,t5.USERNAME -- 담당자 명
,t6.FRANCHISEFORMNAME --가맹점형태
,t7.FRANCHISETYPENAME -- 가맹점종류
,t8.CUSTOMERSECTIONNAME -- 가맹점 구분
,T9.VISITPERIODNAME
,t1.CUSTOMERCODE
,t3.CUSTOMERNAME -- 거래처 명
,t1.VISITDATE
,t1.USERID
,t1.OUTTIME
,t1.inTIME |
위와 같은 쿼리로
거래처 및 일자별 체류 시간을 구했습니다.그런데 한 거래처를 같은 날 두번 반복하는 것 때문에 WORKTIME을 거래처 별로 더해줘야하는데
TimeStamp 형식 더하는 법을 모르겠습니다.
,SUM(NVL(LPAD(EXTRACT(DAY FROM WORKTIME)*24 + EXTRACT(HOUR FROM WORKTIME),2,0),'00'))
||':'|| SUM(NVL(LPAD(EXTRACT(MINUTE FROM WORKTIME),2,0),'00'))
||':'|| SUM(NVL(LPAD(EXTRACT(SECOND FROM WORKTIME),2,0),'00')) AS WORKTIME |
무식한 방법으로 이런식으로 추출했더니 72분 116초 라는 결과가 나와버리네요.
timestamp 나 date 형식을 sum 할 수 있는 방법이 있나요??
전체 쿼리 입니다.
Select
LOCATIONGROUPNAME --지역명
,USERNAME -- 담당자 명
,FRANCHISEFORMNAME --가맹점형태
,FRANCHISETYPENAME -- 가맹점종류
,CUSTOMERSECTIONNAME -- 가맹점 구분
,VISITPERIODNAME
,CUSTOMERCODE
,CUSTOMERNAME -- 거래처 명
,NVL(MAX( DECODE( CDATE, '2010-02-19', WORKTIME )),'00:00:00') "2010-02-19"
,NVL( MAX( DECODE( CDATE, '2010-02-20', WORKTIME )),'00:00:00') "2010-02-20"
,NVL( MAX( DECODE( CDATE, '2010-02-21', WORKTIME )),'00:00:00') "2010-02-21"
,cnt
FROM (
SELECT
LOCATIONGROUPNAME --지역명
,USERNAME -- 담당자 명
,FRANCHISEFORMNAME --가맹점형태
,FRANCHISETYPENAME -- 가맹점종류
,CUSTOMERSECTIONNAME -- 가맹점 구분
,VISITPERIODNAME
,CUSTOMERCODE
,CUSTOMERNAME -- 거래처 명
,CDATE
,SUM(NVL(LPAD(EXTRACT(DAY FROM WORKTIME)*24 + EXTRACT(HOUR FROM WORKTIME),2,0),'00'))
||':'|| SUM(NVL(LPAD(EXTRACT(MINUTE FROM WORKTIME),2,0),'00'))
||':'|| SUM(NVL(LPAD(EXTRACT(SECOND FROM WORKTIME),2,0),'00')) AS WORKTIME
,count(WORKTIME) as cnt
from(
SELECT
t4.LOCATIONGROUPNAME --지역명
,t5.USERNAME -- 담당자 명
,t6.FRANCHISEFORMNAME --가맹점형태
,t7.FRANCHISETYPENAME -- 가맹점종류
,t8.CUSTOMERSECTIONNAME -- 가맹점 구분
,T9.VISITPERIODNAME
,t1.CUSTOMERCODE
,t3.CUSTOMERNAME -- 거래처 명
,t1.VISITDATE CDATE
,to_timestamp(to_char(TO_DATE(t1.OUTTIME, 'YYYY-MM-DD HH24:MI:SS'),'yyyymmddHH24miss'), 'YYYYMMDDHH24MISSFF6')
- to_timestamp(to_char(TO_DATE(t1.INTIME, 'YYYY-MM-DD HH24:MI:SS'),'yyyymmddHH24miss'), 'YYYYMMDDHH24MISSFF6') as WORKTIME
FROM
TBLVISITMASTER T1
LEFT JOIN TBLCUSTOMERMASTER t3 ON ( t1.CUSTOMERCODE = t3.CUSTOMERCODE)
LEFT JOIN TBLLOCATIONGROUPCODE t4 ON ( t3.LOCATIONGROUPCODE = t4.LOCATIONGROUPCODE)
LEFT JOIN TBLUSERMASTER t5 ON ( t1.USERID = t5.USERID )
LEFT JOIN TBLFRANCHISEFORMCODE t6 ON ( t3.FRANCHISEFORMCODE = t6.FRANCHISEFORMCODE)
LEFT JOIN TBLFRANCHISETYPECODE t7 ON ( t3.FRANCHISETYPECODE = t7.FRANCHISETYPECODE)
LEFT JOIN TBLCUSTOMERSECTIONCODE t8 ON ( t3.CUSTOMERSECTIONCODE = t8.CUSTOMERSECTIONCODE )
LEFT JOIN TBLVISITPERIODCODE T9 ON ( T3.VISITPERIOD = T9.VISITPERIODCODE )
WHERE
VISITDATE BETWEEN '2010-02-19' AND '2010-02-21'
GROUP BY
t4.LOCATIONGROUPNAME --지역명
,t5.USERNAME -- 담당자 명
,t6.FRANCHISEFORMNAME --가맹점형태
,t7.FRANCHISETYPENAME -- 가맹점종류
,t8.CUSTOMERSECTIONNAME -- 가맹점 구분
,T9.VISITPERIODNAME
,t1.CUSTOMERCODE
,t3.CUSTOMERNAME -- 거래처 명
,t1.VISITDATE
,t1.USERID
,t1.OUTTIME
,t1.inTIME
)
group by LOCATIONGROUPNAME,
USERNAME -- 담당자 명
,FRANCHISEFORMNAME --가맹점형태
,FRANCHISETYPENAME -- 가맹점종류
,CUSTOMERSECTIONNAME -- 가맹점 구분
,VISITPERIODNAME
,CUSTOMERCODE
,CUSTOMERNAME -- 거래처 명
,CDATE
)
GROUP BY
LOCATIONGROUPNAME --지역명
,USERNAME -- 담당자 명
,FRANCHISEFORMNAME --가맹점형태
,FRANCHISETYPENAME -- 가맹점종류
,CUSTOMERSECTIONNAME -- 가맹점 구분
,VISITPERIODNAME
,CUSTOMERCODE
,CUSTOMERNAME -- 거래처 명
,cnt
order by
LOCATIONGROUPNAME,USERNAME,FRANCHISETYPENAME,CUSTOMERCODE |
|