2016权益单qy

-----20180428养老权益单统计

select * from gdsimis.ac01 a where a.aac147='440127195607180918';

SELECT c.aac001,

       round(sum(c.aae019) / count(distinct c.aae002), 2) 月平均养老待遇,

       1000000000000040 baz002,

       b.baf307 aaa027

  FROM gdylsy.ac83 c, gdylsy.ac82 b

 WHERE c.aaz220 = b.aaz220

   and c.aae002 between 201601 and 201612

   and c.aae140 = '110'

   and c.aaa036 in ('110098','110161','110001', '110002', '110003', '110025', '110038', '110495')

   and c.bae023 = 'S'

   and c.aac001 =1000000004700785

   group by c.aac001, c.aae140, b.baf307;

select * from ac83_yl a where a.aac001=1000000004700785;

---------------------------------更新月平均养老待遇

MERGE INTO ac83_yl t1

USING (SELECT * FROM ac83_yl_cic001) t2

ON ( t1.aac001=t2.aac001)

WHEN MATCHED THEN

  UPDATE SET t1.cic001 = t2.cic001

;

select * from gdsimis.ac01 a where a.aac147='440127195610230517';

select * from ac83_yl a where a.aac001=1000000004788043;

----------------------------------------更新当年调整金

select count(1) from ac83_yl a where a.aae041>201606;

--cytz nianyue 首次领取养老待遇须 <= 201606 

update ac83_yl a set a.cic002=0 where a.aae041>201606;

--上年度养老金标准

--取2015年12月的待遇，如果是16年1月到6月前领取待遇的，直接取这个待遇数值即可

alter table ac83_yl add dy201512 number;

MERGE INTO ac83_yl t1 

using  

(SELECT C.AAC001, SUM(C.AAE019) AAE019

  FROM AC83 C, AC83_YL B

 WHERE C.AAC001 = B.AAC001

   AND C.AAE002 = 201512

   AND C.AAE140 = '110'

   AND C.AAA036 IN ('110098', '110161', '110001', '110002', '110003',

        '110025', '110038', '110495')

   AND C.BAE023 = 'S'

 GROUP BY C.AAC001) t2 

ON ( t1.aac001=t2.aac001)

WHEN MATCHED THEN

  UPDATE SET t1.dy201512 = t2.AAE019

;  

select count(1) from ac83_yl a where a.dy201512 is null;

MERGE INTO ac83_yl t1 

using  

(SELECT C.AAC001, SUM(C.AAE019) AAE019

  FROM AC83 C, AC83_YL B

 WHERE C.AAC001 = B.AAC001

   AND C.AAE002 = b.aae041

   AND C.AAE140 = '110'

   AND C.AAA036 IN ('110098', '110161', '110001', '110002', '110003',

        '110025', '110038', '110495')

   AND C.BAE023 = 'S'

   and b.dy201512 is null

 GROUP BY C.AAC001) t2 

ON ( t1.aac001=t2.aac001)

WHEN MATCHED THEN

  UPDATE SET t1.dy201512 = t2.AAE019

;  

--35+ 上年度养老金标准*4.8%+ （缴费年限-15）向上取整*1

alter table ac83_yl add paymonth number;

--select aae200,(ceil(a.aae200/12)-15) from ic10 a where a.aac001=1000000004679085;

MERGE INTO ac83_yl t1

USING 

(SELECT B.AAC001, max(B.AAE200) aae200

  FROM IC10 B, AC83_YL A

 WHERE A.AAC001 = B.AAC001

   AND B.AAE140 = '110'

   and A.AAE041 <= 201606

   group by b.aac001) t2

ON ( t1.aac001=t2.aac001)

WHEN MATCHED THEN

  UPDATE SET t1.paymonth = t2.aae200

; 

----------------------

----2016年6月30日（含当日）前年满75周岁的，一次性加发1200元===19410630 qian 

select a.aac006,b.aac001 from gdsimis.ac01 a,ac83_yl b where a.aac001=b.aac001 

and a.aac006<=19410630;

alter table ac83_yl add dy201512 number;