Oracle月份累计平均数据(1-2-3月份的数据除以3,如果是4月份就除以4)

 

 

 

 

select

'ck0g4403000' as NBJGH, --内部机构号

'' as KMBH, --科目编号

'CNY' AS BZ, --币种

RDTDATE AS RQ, --日期

'YJ002' AS BBMC, --报表名称

'18' AS ZBH, --行号

'E' AS ZBL, --列号

case

when to_char(TO_DATE(RDTDATE,'yyyymmdd'),'mm') IN (1) then -- 1月份日均贷款

(SELECT sum(cast(nvl(strValue,0) as decimal(38,2))) from Itemdata

WHERE STRITEMCODE in('A80290000800003','A80290000600003','A80010000900004') and to_char(dtdate,'YYYYMMDD')='20190131')/

(SELECT sum(cast(nvl(strValue,0) as decimal(38,2))) from Itemdata

WHERE STRITEMCODE = 'A80010008600004' and to_char(dtdate,'YYYYMMDD')='20190131')

when to_char(TO_DATE(RDTDATE,'yyyymmdd'),'mm') IN (2) then ---1月份和2月份的日均贷款

(SELECT sum(cast(nvl(strValue,0) as decimal(38,2)))/2 from Itemdata

WHERE STRITEMCODE in('A80290000800003','A80290000600003','A80010000900004') and to_char(dtdate,'YYYYMMDD') in ('20190131','20190228')/

(SELECT sum(cast(nvl(strValue,0) as decimal(38,2)))/2 from Itemdata

WHERE STRITEMCODE = 'A80010008600004' and to_char(dtdate,'YYYYMMDD')in ('20190131','20190228'))

when to_char(TO_DATE(RDTDATE,'yyyymmdd'),'mm') IN (3) then ---1-2-3月份的日均贷款

(SELECT sum(cast(nvl(strValue,0) as decimal(38,2)))/3 from Itemdata

WHERE STRITEMCODE in('A80290000800003','A80290000600003','A80010000900004') and to_char(dtdate,'YYYYMMDD') in ('20190131','20190228','20190331')/

(SELECT sum(cast(nvl(strValue,0) as decimal(38,2)))/3 from Itemdata

WHERE STRITEMCODE = 'A80010008600004' and to_char(dtdate,'YYYYMMDD')in ('20190131','20190228','20190331'))

end as BL

from dual;