직원별, 부서별, 사업별 업무시간을 구하려고 하는데요...
조직이 조직이동이 많이 있습니다.
승진제도도 년초에 하는게 아니라 연 중간에 해서....
1년에도 2개의 직급을 갖을 수 있고...
1년내에 여러 조직을 갖을 수 있고...
1년내에 여러 프로젝트를 수행 할 수 있습니다.
부서별로 월 실적 및 예상치를 파악을 하려고 하다보니...
직원들의 최종 소속을 가지고 할 수도 없고...
직원들이 업무 등록 시 장기로 등록을 하기 때문에 그리고 그 업무를 하는 도중에 조직 및 직급이 변화하기 때문에... 뭔가 관리하기가 어려운 상태입니다.
| 직원테이블 |
| 직원코드 |
| A |
| B |
| C |
| 직원이력TABLE | ||||
| 직원코드 | 시작일 | 종료일 | 부서코드 | 직급 |
| A | 2017-01-01 | 2017-01-31 | a | 대리 |
| A | 2017-02-01 | 2017-03-31 | b | 대리 |
| A | 2017-04-01 | 2017-12-31 | b | 과장 |
| B | 2017-01-01 | 2017-12-31 | b | 차장 |
| C | 2017-01-01 | 2017-01-15 | c | 부장 |
| C | 2017-01-16 | 2017-02-20 | b | 부장 |
| C | 2017-02-21 | 2017-12-31 | a | 부장 |
급여테이블
| 시작일 | 종료일 | 직급 | 급여 |
| 2016-01-01 | 2016-12-31 | 사원 | 100000 |
| 2016-01-01 | 2016-12-31 | 대리 | 200000 |
| 2016-01-01 | 2016-12-31 | 과장 | 300000 |
| 2016-01-01 | 2016-12-31 | 차장 | 400000 |
| 2016-01-01 | 2016-12-31 | 부장 | 500000 |
| 2016-01-01 | 2016-12-31 | 이사 | 600000 |
| 2017-01-01 | 2017-12-31 | 사원 | 100000 |
| 2017-01-01 | 2017-12-31 | 대리 | 200000 |
| 2017-01-01 | 2017-12-31 | 과장 | 300000 |
| 2017-01-01 | 2017-12-31 | 차장 | 400000 |
| 2017-01-01 | 2017-12-31 | 부장 | 500000 |
| 2017-01-01 | 2017-12-31 | 이사 | 600000 |
| 2018-01-01 | 2018-12-31 | 사원 | 100000 |
| 2018-01-01 | 2018-12-31 | 대리 | 200000 |
| 2018-01-01 | 2018-12-31 | 과장 | 300000 |
| 2018-01-01 | 2018-12-31 | 차장 | 400000 |
| 2018-01-01 | 2018-12-31 | 부장 | 500000 |
| 2018-01-01 | 2018-12-31 | 이사 | 600000 |
| 업무테이블 | |||
| 직원코드 | 시작시간 | 종료시간 | 업무코드 |
| A | 2016-04-01 | 2017-04-30 | 가 |
| B | 2017-03-01 | 2017-12-31 | 나 |
| A | 2017-05-01 | 2017-12-31 | 나 |
| C | 2017-01-01 | 2018-12-31 | 다 |
등으로 구성이 되어져 있는데요....
모든 테이블을 일별로 쪼개서....
| 직원테이블 | |||
| 직원코드 | 일자 | 부서코드 | 직급 |
| A | 2017-01-01 | a | 대리 |
| A | 2017-01-02 | a | 대리 |
| A | 2017-01-03 | a | 대리 |
| A | 2017-01-04 | a | 대리 |
| …. | |||
| A | 2017-02-01 | b | 대리 |
| …. | |||
| A | 2017-04-01 | b | 과장 |
| …. | |||
| A | 2017-12-31 | b | 과장 |
| B | 2017-01-01 | b | 차장 |
| …. | |||
| B | 2017-12-31 | b | 차장 |
| … | |||
| 급여테이블 | |||
| 일자 | 직급 | 급여 | |
| 2016-01-01 | 사원 | 10000 | |
| 2016-01-02 | 사원 | 10000 | |
| …. | |||
| 2016-12-31 | 사원 | 10000 | |
| …. | |||
| 2018-01-01 | 이사 | 600000 | |
| 2016-01-02 | 이사 | 600000 | |
| …. | |||
| 2016-12-31 | 이사 | 600000 | |
| 업무테이블 | |||
| 직원코드 | 일자 | 업무코드 | |
| A | 2016-04-01 | 가 | |
| A | 2016-04-02 | 가 | |
| …. | |||
| A | 2016-04-30 | 가 | |
| B | 2016-03-01 | 나 | |
| B | 2016-03-02 | 나 | |
| … | |||
| B | 2017-12-31 | 나 | |
| … |
이렇게 해서... 직원 - 일 - 업무 - 직급 - 급여 등을 join 을 해서 통계를 뽑았는데...
너무 느리고...
일로 쪼개는 쿼리를 잘 한것인지도 모르겠고...
조직별로 사람들이 2017년 1월 부터 2017년 12월까지 월단위로 얼마나 일을 했는지... 뽑아 보고 싶습니다.
그리고 그것을 돈으로 환산을 하고자 합니다.
그러기 위해서는
조직 a 1월에 직급별로 mm 이 나와야 하고....
그런데 조직 변경이 많아서... A란 사람이 1월에 a 조직에도 속하고 b 조직에도 속할 수 있어... 조직에 속한 일자를 구해야 하고...
하네요...
Comment 5
-
DOOLLY
2017.02.24 09:02
-
DOOLLY
2017.02.24 09:23
업무 테이블 - 일자로 분리하기Select A.*, B.DT from
( Select user_num, sDate, eDate, 업무코드 from 업무테이블 ) A
join
( SELECT CONVERT(VARCHAR(10), CONVERT(DATETIME, '2016-01-01' ) + NUMBER ,20) as DTFROM master.DBO.spt_values
WHERE TYPE = 'P'
AND number <= DATEDIFF(DD,CONVERT(DATETIME, '2016-01-01' ), CONVERT(DATETIME,'2017-12-01'))
) B on B.DT between A.sDate and A.eDate order by user_num, sDate
직원이력 테이블 - 일자로 분리하기Select A.*, B.DT from
( Select user_num, 부서코드, sDate, eDate from 직원이력 ) A
join
( SELECT CONVERT(VARCHAR(10), CONVERT(DATETIME, '2016-01-01' ) + NUMBER ,20) as DTFROM master.DBO.spt_values
WHERE TYPE = 'P'
AND number <= DATEDIFF(DD,CONVERT(DATETIME, '2016-01-01' ), CONVERT(DATETIME,'2017-12-01'))
) B on B.DT between A.sDate and A.eDate order by user_num, sDate
급여테이블 - 일자로 분리하기Select A.*, B.DT from
( Select 돈, 직급코드 , sDate, eDate from 급여 ) A
join
( SELECT CONVERT(VARCHAR(10), CONVERT(DATETIME, '2016-01-01' ) + NUMBER ,20) as DTFROM master.DBO.spt_values
WHERE TYPE = 'P'
AND number <= DATEDIFF(DD,CONVERT(DATETIME, '2016-01-01' ), CONVERT(DATETIME,'2017-12-01'))
) B on B.DT between A.sDate and A.eDate order by user_num, sDate각각을 뽑아서...
Select * from
( 직원이력 테이블 - 일자로 분리하기 ) A
left outer join
( 업무테이블 - 일자로 분리하기 ) B
on A.user_num = B.user_num and A.DT = B.DT
left outer join
( 급여테이블 - 일자로 분리하기 ) C
on A.직급 = C.직급 and A.DT = C.DT이런식으로 하고 있습니다.
-
건우아빠
2017.02.24 10:01
조직이동이 일 단위로 변경이 된다면 모를까 월단위 이면 그대로 하셔도 상관 없을듯 합니다.
이력 테이블이 있어서 보여지는 형태에 따라 다르겠지만
기준은 직원테이블과 이력 테이블을 기준으로 월별 기준자료를 만들고 이 기준 자료를 가지고 만드시면 되지 않을까요
-
DOOLLY
2017.02.24 18:40
조직이 일단위로 조정이 일어납니다.
2017월 1월 11일자 발령.. 뭐 이런 식으로...
그래서 일로 쪼개야 한데... 위의 것 뿐 아니라.. 계속 다른 내용을 붙히려고... 조인을 하고 하니... 너무 느려지네요.
-
건우아빠
2017.02.24 20:39
어떤 자료를 원하는지에 따라 다를듯 합니다.
배치로 자료를 만들어 놓고 조회 하는 방법도 괜찮을듯 합니다.
Select A.*, B.DT from
( Select user_num, sDate, eDate, 업무코드 from 업무테이블 ) A
join
( SELECT CONVERT(VARCHAR(10), CONVERT(DATETIME, '2016-01-01' ) + NUMBER ,20) as DT
FROM master.DBO.spt_values
WHERE TYPE = 'P'
AND number <= DATEDIFF(DD,CONVERT(DATETIME, '2016-01-01' ), CONVERT(DATETIME,'2017-12-01'))
) B on B.DT between A.sDate and A.eDate order by user_num, sDate
이런식으로 하려고 해봤습니다.
이렇게 뽑은 것들을 모두 join 을 시켜서...