데이터베이스 개발자 질문과 답변 게시판
시작일과 종료일을 가지고 MM 를 구할 수 있을까요?
| 사용자 | STARTDATE | ENDDATE |
| A | 2015-05-01 | 2015-06-30 |
| B | 2015-05-15 | 2015-06-30 |
| C | 2015-05-10 | 2015-06-30 |
직원들의 업무시작일 종료일 등이 들어가 있는데요.
시작일과 종료일을 이용해서 MM 을 바로 구할 수 있을까요?
결과값으로
| 사용자 | MM |
| A | 2 |
| B | 1.5483871 |
| C | 1.70967742 |
이런 식으로 뽑아 보고 싶은데요.
첨에는 한달을 30일로 잡고 30으로 나눌까도 생각을 해봤는데... 오차가 좀 있어서...
해당 월에 맞는 모수로 MM 을 구할 방법이 있을까요?
Comment 4
-
Terry
2016.04.22 07:50
Datediff 사용하시면 될듯하네요 -
DOOLLY
2016.04.22 08:14
datediff 도 소수 형태로 결과값이 나오나요?
datediff (mm,..... ) ..형변환을 시키면?해보니... 안되네요.
cast(datediff(MM, '20100301' , '20100315' ) as DECIMAL(5,2)) ->
저는 0.5...가 나와야 하는데.. . 0 이 나오네요.
-
Terry
2016.04.22 09:29
하기 형태대로 진행해보세요..
1. 해당테이블의 시작일,종료일 을 CTE 쿼리 이용하여 분리 ( 월단위로 )
예) 사용자 A, 시작일 2015-04-03, 종료일 2015-05-11
위의 예에 대해 분리작업 진행시
1번ROW = 사용자 A, 순번 1, 시작일 2015-04-03, 종료일 2015-04-30 ( 종료일은 해당월의 종료일자이며, DATEADD 함수 이용해서 구함 )
,월총일자 30 ( DATEDIFF 함수 이용해서 일자로 처리 ), 근무(?)일자 28일 ( DATEDIFF 함수 이용해서 구함 )
2번ROW = 사용자 A, 순번 1, 시작일 2015-05-01, 종료일 2015-05-11
,월총일자 31 , 근무(?)일자 11
그럼 나머지는?
CTE를 통해 분리된 데이터들을 계산해서 더하면 되겠죠?
* 계산일자 = 근무(?)일자 / 월총일자
위의 과정까지 완료가 되면
나머지는 사용자별로 Group 잡아서 계산결과값을 Sum 해주면 됩니다..
그럼 수고하세요~
-
Wook
2016.04.22 10:37
Terry 님이 다 풀어주셨네요저라면, 아래처럼...짜보겠습니다. ㅎㅎㅎ--Working Day 기반으로 매트릭스를 짜고,SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;WITH cte_DAYS AS( SELECT MD.MON_START_DATE, MD.MON_END_DATE, MD.MON_DAY_CNT, MC.TARGET_DATE, WEEKDAY_VAL = DATEPART(WEEKDAY,MC.TARGET_DATE) --7토/1일FROM ( SELECT MON_START_DATE = DATEADD(MONTH,ROW_NUMBER() OVER(ORDER BY (SELECT 1)),'1999-12-01'), MON_END_DATE = DATEADD(MONTH,1,DATEADD(MONTH,ROW_NUMBER() OVER(ORDER BY (SELECT 1)),'1999-12-01')) - 1, MON_DAY_CNT = DATEDIFF(DAY ,DATEADD(MONTH,ROW_NUMBER() OVER(ORDER BY (SELECT 1)),'1999-12-01'),DATEADD(MONTH,1,DATEADD(MONTH,ROW_NUMBER() OVER(ORDER BY (SELECT 1)),'1999-12-01')))FROM master.dbo.spt_values) AS MDCROSS APPLY ( SELECT TOP (MD.MON_DAY_CNT)DATEADD(DAY,ROW_NUMBER() OVER(ORDER BY (SELECT 1))-1,MON_START_DATE) AS TARGET_DATEFROM master.dbo.spt_values) AS MC)SELECT *, ONE_DAY_PCT = CASE WHEN WEEKDAY_VAL NOT IN (1,7) THEN CAST(100.0/WD.WORKING_DAY_CNT AS REAL) ELSE 0 ENDINTO #TMP_TBL_DAY_MXFROM cte_DAYS AS DSCROSS APPLY ( SELECT SUM(CASE WHEN WEEKDAY_VAL NOT IN (1,7) THEN 1 END) AS WORKING_DAY_CNTFROM cte_DAYSWHERE MON_START_DATE = DS.MON_START_DATEAND MON_END_DATE = DS.MON_END_DATE) AS WDORDER BY4--Man-Month 계산SELECT USR.USER_ID, USR.START_DATE, USR.END_DATE, ROUND(SUM(ONE_DAY_PCT)/100,5) AS MAN_MONTHFROM ( SELECT TOP 0USER_ID = CAST(NULL AS nvarchar(100)), START_DATE = CAST(NULL AS datetime), END_DATE = CAST(NULL AS datetime)UNION SELECT 'A' , '2015-05-01' , '2015-06-30'UNION SELECT 'B' , '2015-05-15' , '2015-06-30'UNION SELECT 'C' , '2015-05-10' , '2015-06-30') AS USRCROSS APPLY ( SELECT TARGET_DATE, WEEKDAY_VAL, ONE_DAY_PCTFROM #TMP_TBL_DAY_MXWHERE TARGET_DATE >= USR.START_DATEAND TARGET_DATE <= USR.END_DATE) AS DMGROUP BYUSR.USER_ID, USR.START_DATE, USR.END_DATE
