J모해의 기록보관소 (jmohae's Archive)

// 뷰 테이블 내용
select S.EMP_NO as [사원번호], I.EMP_NM as [사원이름], S.YYMM as [년월], sum(S.REL_PAY_AMT) as [실제받은 금액]
 from IN30100 S, IN10100 I" & _
 where S.EMP_NO = I.EMP_NO AND S.ON_WORK_YN='Y' group by S.YYMM, S.EMP_NO, I.EMP_NM

select [사원번호], [사원이름], avg([실제받은 금액]) as [3개월 평균 봉급] from test_view
 where [년월] = (select LEFT(convert(varchar(30),dateadd(mm,-1, getdate()),112),6))
 or [년월] = (select LEFT(convert(varchar(30),dateadd(mm,-2, getdate()),112),6))
 or [년월] = (select LEFT(convert(varchar(30),dateadd(mm,-3, getdate()),112),6))
 group by [사원이름], [사원번호]"

select S.EMP_NO as [사원번호], I.EMP_NM as [이름], sum(S.REL_PAY_AMT)/3 as [3개월 평균 급여]
 from IN30100 S, IN10100 I where S.EMP_NO=I.EMP_NO AND datediff(mm,getdate(),cast(S.YYMM+'01' as datetime)) >= -2 AND I.ON_WORK_YN='Y'
 group by S.EMP_NO, I.EMP_NM
 order by S.EMP_NO desc