|
쿼리를 만들었는데... 속도가 넘 안나오네요...
많이 부족하지만 간결하고, 좀 더 나은 빠르게 결과를 볼수 있을까 해서 글을 올립니다.
많은 가르침 부탁드려요^^
declare @deptCD nvarchar(128)
declare @sDate datetime
declare @eDate datetime
set @deptCD = company
set @sDate = '2010-02-17 00:00:00.000'
set @eDate = '2010-02-22 00:00:00.000'
select dept.DEPTCODE, dept.DEPTNAME,
(select COUNT(A1.USERID) as usercount
from DRMONE_USER A1
inner join DRMONE_DEPT A2
on A1.DEPTCODE = A2.DEPTCODE
where ((A2.DEPTCODE = dept.DEPTCODE) or (A2.PARENTCODE like dept.DEPTCODE + '%'))
) as usercount,
(select count(distinct P1.USERID)
from FSN2_RECEIVE_POLICY_LOG P1
inner join DRMONE_DEPT P2
on P1.DEPTCODE = P2.DEPTCODE
where P1.ENTRYTS >= @sDate
and P1.ENTRYTS <= @eDate
and ((P2.DEPTCODE = dept.DEPTCODE) or (P2.PARENTCODE like dept.DEPTCODE + '%'))
) as PDOWN,
(select count(B1.PURPOSE) as CREATE_NEW
from (select DEPTCODE, PURPOSE, LOGDATE
from FSN2_LOG
where PURPOSE = 'CREATE_NEW'
and LOGDATE >= @sDate
and LOGDATE <= @eDate
) B1
inner join DRMONE_DEPT B2
on B1.DEPTCODE = B2.DEPTCODE
where ((B2.DEPTCODE = dept.DEPTCODE) or (B2.PARENTCODE like dept.DEPTCODE + '%'))
) as CREATE_NEW,
(select count(PURPOSE) as CREATE_NEW
from (select DEPTCODE, PURPOSE, LOGDATE
from FSN2_LOG
where PURPOSE = 'VIEW'
and LOGDATE >= @sDate
and LOGDATE <= @eDate
) C1
inner join DRMONE_DEPT C2
on C1.DEPTCODE = C2.DEPTCODE
where ((C2.DEPTCODE = dept.DEPTCODE) or (C2.PARENTCODE like dept.DEPTCODE + '%'))
) as sVIEW,
(select count(PURPOSE) as CREATE_NEW
from (select DEPTCODE, PURPOSE, LOGDATE
from FSN2_LOG
where PURPOSE = 'SECURE_SAVE'
and LOGDATE >= @sDate
and LOGDATE <= @eDate
) D1
inner join DRMONE_DEPT D2
on D1.DEPTCODE = D2.DEPTCODE
where ((D2.DEPTCODE = dept.DEPTCODE) or (D2.PARENTCODE like dept.DEPTCODE + '%'))
) as SECURE_SAVE
from DRMONE_DEPT dept
where dept.PARENTCODE = @deptCD
and dept.ETC1 = 'Y' |
