Oracle月度取季度末的数据实例

1-2-3月获取去年年底的数据 4-5-6获取3-31数据

     select

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

     '' as KMBH, --科目编号

     'CNY' AS BZ, --币种

      '20190430' AS RQ, --日期

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

     '24' AS ZBH, --行号

    'B' AS ZBL, --列号

   0 AS SL, --数量

  case

        when to_char(TO_DATE('20190430','yyyymmdd'),'mm') IN (1,2,3) then

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

WHERE STRITEMCODE = 'A80120003700003' and                      to_char(dtdate,'YYYYMMDD')=to_char(trunc(to_date('20190430','yyyymmdd'),'yyyy')-1,'yyyymmdd'))

when to_char(TO_DATE('20190430','yyyymmdd'),'mm') IN (4,5,6) then

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

WHERE STRITEMCODE = 'A80120003700003' and to_char(dtdate,'YYYYMMDD')=to_char(TO_DATE('20190430','yyyymmdd'),'yyyy')||'0331')

when to_char(TO_DATE('20190430','yyyymmdd'),'mm') IN (7,8,9) then

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

WHERE STRITEMCODE = 'A80120003700003' and to_char(dtdate,'YYYYMMDD')=to_char(TO_DATE('20190430','yyyymmdd'),'yyyy')||'0630')

when to_char(TO_DATE('20190430','yyyymmdd'),'mm') IN (10,11,12) then

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

WHERE STRITEMCODE = 'A80120003700003' and to_char(dtdate,'YYYYMMDD')=to_char(TO_DATE('20190430','yyyymmdd'),'yyyy')||'0930')

end as je

from dual;

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！