吉林市：吉林地区养老保险基金计算机审计思路及SQL语句

　　近期，吉林省开展了全省2010年基本养老保险基金审计调查工作，在审计过程中，吉林市审计局审计人员灵活运用SQL语句展开查询，大大的节省了审计时间，提高了审计效率，现将审计思路与各位审计同仁一同分享。

　　一、审计思路

　　（一）个人信息中身份证号的合法性审查。

　　非法身份证号大致有以下几种形式：不是15位或者18位、非数值型、全是同一数字。

　　（二）合法身份证号的唯一性审查。

　　以15位身份证号位基础，将18位身份证号中的七、八、及末位去掉后，查看是否存在重复的身份证号；核对参保人员出生日期与身份证中体现的出生日期是否一致。

　　（三）缴费情况审查。

　　按照个人编号分组汇总出个人缴费金额，单位缴费金额，缴费次数以及最后缴费时间；查看缴费比例是否合规。

　　（四）支付情况审查。

　　按照个人编号分组汇总出支付金额，支付次数以及最早支付时间，最晚支付时间。重点审查支付一、两次的情况；查看是否存在重复支付的情况；查看是否存在死亡还支付的情况；查看是否存在提前支付的情况。

　　（五）提前退休问题。

　　查看退休工龄是否符合规定年限。

　　（六）需要注意的问题。

　　由于数据从文本文件转换而成，所有字段类型都是字符型，在需要的时候将字段转换成相应的类型，如金额转换成数值型、时间转换成日期型。

　　二、实施过程及相关SQL语句

　　（一）身份证号的合法性审查。身份证号是不是15位或者18位的记录。

　　select len(公民身份证号) as 身份证号位数，* 

　　from 个人基本信息

　　where len(公民身份证号)〈〉15

　　and len(公民身份证号)〈〉18

　　（二）合法身份证号的唯一性审查。

　　1.整理个人基本信息表，将身份证号转换成15位，出生日期转换成日期型，参加工作时间转换成日期型，生成新表【个人信息】。

　　select case 

　　when len(公民身份证号)=18 then left(公民身份证号，6)+substring(公民身份证号，9，9) 

　　when len(公民身份证号)=15 then 公民身份证号 end as 新身份证号，

　　case left(出生日期，3)

　　when ’Jan’ then cast(right(left(出生日期，11)，4)+’-’+’1’+’-’+substring(出生日期，5，2) as datetime)

　　when ’Feb’ then cast(right(left(出生日期，11)，4)+’-’+’2’+’-’+substring(出生日期，5，2) as datetime)

　　when ’Mar’ then cast(right(left(出生日期，11)，4)+’-’+’3’+’-’+substring(出生日期，5，2) as datetime)

　　when ’Apr’ then cast(right(left(出生日期，11)，4)+’-’+’4’+’-’+substring(出生日期，5，2) as datetime)

　　when ’May’ then cast(right(left(出生日期，11)，4)+’-’+’5’+’-’+substring(出生日期，5，2) as datetime)

　　when ’Jun’ then cast(right(left(出生日期，11)，4)+’-’+’6’+’-’+substring(出生日期，5，2) as datetime)  

　　when ’Jul’ then cast(right(left(出生日期，11)，4)+’-’+’7’+’-’+substring(出生日期，5，2) as datetime)  

　　when ’Aug’ then cast(right(left(出生日期，11)，4)+’-’+’8’+’-’+substring(出生日期，5，2) as datetime)  

　　when ’Sep’ then cast(right(left(出生日期，11)，4)+’-’+’9’+’-’+substring(出生日期，5，2) as datetime) 

　　when ’Oct’ then cast(right(left(出生日期，11)，4)+’-’+’10’+’-’+substring(出生日期，5，2) as datetime)  

　　when ’Nov’ then cast(right(left(出生日期，11)，4)+’-’+’11’+’-’+substring(出生日期，5，2) as datetime)  

　　when ’Dec’ then cast(right(left(出生日期，11)，4)+’-’+’12’+’-’+substring(出生日期，5，2) as datetime)  

　　end as 整理后出生日期，

　　case left(参加工作日期，3)

　　when ’Jan’ then cast(right(left(参加工作日期，11)，4)+’-’+’1’+’-’+substring(参加工作日期，5，2) as datetime)

　　when ’Feb’ then cast(right(left(参加工作日期，11)，4)+’-’+’2’+’-’+substring(参加工作日期，5，2) as datetime)

　　when ’Mar’ then cast(right(left(参加工作日期，11)，4)+’-’+’3’+’-’+substring(参加工作日期，5，2) as datetime)

　　when ’Apr’ then cast(right(left(参加工作日期，11)，4)+’-’+’4’+’-’+substring(参加工作日期，5，2) as datetime)

　　when ’May’ then cast(right(left(参加工作日期，11)，4)+’-’+’5’+’-’+substring(参加工作日期，5，2) as datetime)

　　when ’Jun’ then cast(right(left(参加工作日期，11)，4)+’-’+’6’+’-’+substring(参加工作日期，5，2) as datetime)  

　　when ’Jul’ then cast(right(left(参加工作日期，11)，4)+’-’+’7’+’-’+substring(参加工作日期，5，2) as datetime)  

　　when ’Aug’ then cast(right(left(参加工作日期，11)，4)+’-’+’8’+’-’+substring(参加工作日期，5，2) as datetime)  

　　when ’Sep’ then cast(right(left(参加工作日期，11)，4)+’-’+’9’+’-’+substring(参加工作日期，5，2) as datetime) 

　　when ’Oct’ then cast(right(left(参加工作日期，11)，4)+’-’+’10’+’-’+substring(参加工作日期，5，2) as datetime)  

　　when ’Nov’ then cast(right(left(参加工作日期，11)，4)+’-’+’11’+’-’+substring(参加工作日期，5，2) as datetime)  

　　when ’Dec’ then cast(right(left(参加工作日期，11)，4)+’-’+’12’+’-’+substring(参加工作日期，5，2) as datetime)  

　　end as 整理后参加工作日期，*

　　into 个人信息

　　from 个人基本信

　　2.查询身份证号重复的记录，对照档案查实原因，看是否为重复参保。

　　select * 

　　into 身份证号重复记录

　　from个人信息

　　where 新身份证号 in(

　　select 新身份证号

　　from 个人信息

　　group by 新身份证号

　　having count(*)〉1)

　　order by 新身份证号

　　3.查询参保人员出生日期的年份与身份证中体现的出生年份不一致的记录，查阅档案或询问原因。

　　select 个人编号，姓名，单位编号，新身份证号，整理后出生日期，*

　　from  个人信息

　　where  substring(新身份证号，7，2)〈〉right(year(整理后出生日期)，2)

　　order by 个人编号，新身份证号

　　（三）缴费情况审查。

　　1.将养老保险个人应缴实缴明细信息表中个人缴费到账日期转换成日期型、单位缴费到账日期转换成日期型，生成新表【缴费表】。

　　select  case left(个人缴费到账日期，3)

　　when ’Jan’ then cast(right(left(个人缴费到账日期，11)，4)+’-’+’1’+’-’+substring(个人缴费到账日期，5，2) as datetime)

　　when ’Feb’ then cast(right(left(个人缴费到账日期，11)，4)+’-’+’2’+’-’+substring(个人缴费到账日期，5，2) as datetime)

　　when ’Mar’ then cast(right(left(个人缴费到账日期，11)，4)+’-’+’3’+’-’+substring(个人缴费到账日期，5，2) as datetime)

　　when ’Apr’ then cast(right(left(个人缴费到账日期，11)，4)+’-’+’4’+’-’+substring(个人缴费到账日期，5，2) as datetime)

　　when ’May’ then cast(right(left(个人缴费到账日期，11)，4)+’-’+’5’+’-’+substring(个人缴费到账日期，5，2) as datetime)

　　when ’Jun’ then cast(right(left(个人缴费到账日期，11)，4)+’-’+’6’+’-’+substring(个人缴费到账日期，5，2) as datetime)  

　　when ’Jul’ then cast(right(left(个人缴费到账日期，11)，4)+’-’+’7’+’-’+substring(个人缴费到账日期，5，2) as datetime)  

　　when ’Aug’ then cast(right(left(个人缴费到账日期，11)，4)+’-’+’8’+’-’+substring(个人缴费到账日期，5，2) as datetime)  

　　when ’Sep’ then cast(right(left(个人缴费到账日期，11)，4)+’-’+’9’+’-’+substring(个人缴费到账日期，5，2) as datetime) 

　　when ’Oct’ then cast(right(left(个人缴费到账日期，11)，4)+’-’+’10’+’-’+substring(个人缴费到账日期，5，2) as datetime) 

　　when ’Nov’ then cast(right(left(个人缴费到账日期，11)，4)+’-’+’11’+’-’+substring(个人缴费到账日期，5，2) as datetime)  

　　when ’Dec’ then cast(right(left(个人缴费到账日期，11)，4)+’-’+’12’+’-’+substring(个人缴费到账日期，5，2) as datetime)  

　　end as 个人缴费日期，

　　case left(单位缴费到账日期，3)

　　when ’Jan’ then cast(right(left(单位缴费到账日期，11)，4)+’-’+’1’+’-’+substring(单位缴费到账日期，5，2) as datetime)

　　when ’Feb’ then cast(right(left(单位缴费到账日期，11)，4)+’-’+’2’+’-’+substring(单位缴费到账日期，5，2) as datetime)

　　when ’Mar’ then cast(right(left(单位缴费到账日期，11)，4)+’-’+’3’+’-’+substring(单位缴费到账日期，5，2) as datetime)

　　when ’Apr’ then cast(right(left(单位缴费到账日期，11)，4)+’-’+’4’+’-’+substring(单位缴费到账日期，5，2) as datetime)

　　when ’May’ then cast(right(left(单位缴费到账日期，11)，4)+’-’+’5’+’-’+substring(单位缴费到账日期，5，2) as datetime)

　　when ’Jun’ then cast(right(left(单位缴费到账日期，11)，4)+’-’+’6’+’-’+substring(单位缴费到账日期，5，2) as datetime)  

　　when ’Jul’ then cast(right(left(单位缴费到账日期，11)，4)+’-’+’7’+’-’+substring(单位缴费到账日期，5，2) as datetime)  

　　when ’Aug’ then cast(right(left(单位缴费到账日期，11)，4)+’-’+’8’+’-’+substring(单位缴费到账日期，5，2) as datetime)  

　　when ’Sep’ then cast(right(left(单位缴费到账日期，11)，4)+’-’+’9’+’-’+substring(单位缴费到账日期，5，2) as datetime) 

　　when ’Oct’ then cast(right(left(单位缴费到账日期，11)，4)+’-’+’10’+’-’+substring(单位缴费到账日期，5，2) as datetime) 

　　when ’Nov’ then cast(right(left(单位缴费到账日期，11)，4)+’-’+’11’+’-’+substring(单位缴费到账日期，5，2) as datetime)  

　　when ’Dec’ then cast(right(left(单位缴费到账日期，11)，4)+’-’+’12’+’-’+substring(单位缴费到账日期，5，2) as datetime)  

　　end as 单位缴费日期，* 

　　into 缴费表

　　from 养老保险个人应缴实缴明细信息表

　　2.按照个人编号分组汇总出个人缴费总额、个人缴费最晚日期、单位缴费总额、单位缴费最晚日期以及缴费次数整理缴费表生成视图【v_缴费】。

　　create view v_缴费

　　as

　　select 个人编号，sum(cast(个人缴费金额 as numeric(10，2))) as ’个人缴费总额’，

　　max(个人缴费日期)as ’个人缴费最晚日期’，

　　sum(cast(单位缴费划入账户金额 as numeric(10，2))) as ’单位缴费总额’，

　　max(单位缴费日期) as ’单位缴费最晚日期’，

　　count(*) as ’缴费次数’

　　from 缴费表

　　group by 个人编号

　　3.缴费比例是否合规。查询2009年缴费基数计算个人缴费比例不等于8%、单位缴费比例不等于21%的记录。

　　select 个人编号，left(费款所属期，4) as 年份，

　　sum(cast(养老保险缴费基数 as numeric(10，2))) as ’缴费基数’，

　　sum(cast(个人缴费金额 as numeric(10，2))) as ’个人缴费’，

　　sum(cast(个人缴费金额 as numeric(10，2)))*1.0／sum(cast(养老保险缴费基数 as numeric(10，2))) as ’个人缴费比例’，

　　sum(cast(单位缴费划入账户金额 as numeric(10，2))) as ’单位缴费’，

　　sum(cast(单位缴费划入账户金额 as numeric(10，2)))*1.0／sum(cast(养老保险缴费基数 as numeric(10，2))) as ’单位缴费比例’

　　from 缴费表

　　where left(费款所属期，4)= ’2009’

　　group by 个人编号，left(费款所属期，4)

　　having  sum(cast(养老保险缴费基数 as numeric(10，2)))〈〉0

　　and  (sum(cast(个人缴费金额 as numeric(10，2)))*1.0／sum(cast(养老保险缴费基数 as numeric(10，2)))〈〉0.08

　　or  sum(cast(单位缴费划入账户金额 as numeric(10，2)))*1.0／sum(cast(养老保险缴费基数 as numeric(10，2)))〈〉0.21)

　　order by 个人编号，left(费款所属期，4)

　　（四）支付情况审查。

　　1.将个人支付信息中的合计金额转换成数值型，业务支付日期转换成时间型。

　　select  

　　case left(业务支付日期，3)

　　when ’Jan’ then cast(right(left(业务支付日期，11)，4)+’-’+’1’+’-’+substring(业务支付日期，5，2) as datetime)

　　when ’Feb’ then cast(right(left(业务支付日期，11)，4)+’-’+’2’+’-’+substring(业务支付日期，5，2) as datetime) 

　　when ’Mar’ then cast(right(left(业务支付日期，11)，4)+’-’+’3’+’-’+substring(业务支付日期，5，2) as datetime) 

　　when ’Apr’ then cast(right(left(业务支付日期，11)，4)+’-’+’4’+’-’+substring(业务支付日期，5，2) as datetime) 

　　when ’May’ then cast(right(left(业务支付日期，11)，4)+’-’+’5’+’-’+substring(业务支付日期，5，2) as datetime)  

　　when ’Jun’ then cast(right(left(业务支付日期，11)，4)+’-’+’6’+’-’+substring(业务支付日期，5，2) as datetime)  

　　when ’Jul’ then cast(right(left(业务支付日期，11)，4)+’-’+’7’+’-’+substring(业务支付日期，5，2) as datetime)  

　　when ’Aug’ then cast(right(left(业务支付日期，11)，4)+’-’+’8’+’-’+substring(业务支付日期，5，2) as datetime)  

　　when ’Sep’ then cast(right(left(业务支付日期，11)，4)+’-’+’9’+’-’+substring(业务支付日期，5，2) as datetime) 

　　when ’Oct’ then cast(right(left(业务支付日期，11)，4)+’-’+’10’+’-’+substring(业务支付日期，5，2) as datetime)  

　　when ’Nov’ then cast(right(left(业务支付日期，11)，4)+’-’+’11’+’-’+substring(业务支付日期，5，2) as datetime)  

　　when ’Dec’ then cast(right(left(业务支付日期，11)，4)+’-’+’12’+’-’+substring(业务支付日期，5，2) as datetime)  

　　end as 支付时间，*

　　into 支付表

　　from 个人支付信息

　　2.按照个人编号分组汇总出支付金额，支付次数以及最早支付时间，最晚支付时间整理个人支付信息表生成视图【v_支付】。

　　create view v_支付

　　as

　　select 个人编号，sum(合计金额) as ’支付总额’，count(*) as ’支付次数’，min(支付时间) as 最早支付时间，max(支付时间) as 最晚支付时间

　　from 支付表

　　group by 个人编号

　　3.查询支付一次、两次的记录。支付一次表示参保人退保、支付两次表示参保人死亡，核对档案。

　　select * from v_支付

　　where 支付次数〈=2

　　order by 支付次数

　　4.查询重复支付的记录。对于重复身份证号记录与整理的支付视图对应，查看养老基金支付一次以上的身份证号相同的参保人员的信息。

　　select  * 

　　from 个人信息

　　where 新身份证号 in

　　(select 新身份证号 

　　from 身份证号重复记录 a

　　left join v_支付 v on a.个人编号=v.个人编号

　　where 支付总额 is not null

　　group by 新身份证号

　　having count(*)〉1)

　　order by 新身份证号

　　5.死亡还支付的记录。

　　（1）查询变更原因

　　select distinct(变更原因)

　　from 离退休人员变更信息

　　（2）单位名称含死亡

　　select * from 单位基本信息

　　where 单位名称 like’%死亡%’

　　（3）上述查询，变更原因有死亡、转入或者转出单位为死亡的（本案例中死亡单位编码“500004+”“500003-”），查询变更原因与死亡相关的生产新表【死亡统计】，以便进一步核查。

　　select case left(变更日期，3)

　　when ’Jan’ then cast(right(left(变更日期，11)，4)+’-’+’1’+’-’+substring(变更日期，5，2) as datetime)

　　when ’Feb’ then cast(right(left(变更日期，11)，4)+’-’+’2’+’-’+substring(变更日期，5，2) as datetime)

　　when ’Mar’ then cast(right(left(变更日期，11)，4)+’-’+’3’+’-’+substring(变更日期，5，2) as datetime)

　　when ’Apr’ then cast(right(left(变更日期，11)，4)+’-’+’4’+’-’+substring(变更日期，5，2) as datetime)

　　when ’May’ then cast(right(left(变更日期，11)，4)+’-’+’5’+’-’+substring(变更日期，5，2) as datetime)

　　when ’Jun’ then cast(right(left(变更日期，11)，4)+’-’+’6’+’-’+substring(变更日期，5，2) as datetime)  

　　when ’Jul’ then cast(right(left(变更日期，11)，4)+’-’+’7’+’-’+substring(变更日期，5，2) as datetime)  

　　when ’Aug’ then cast(right(left(变更日期，11)，4)+’-’+’8’+’-’+substring(变更日期，5，2) as datetime)  

　　when ’Sep’ then cast(right(left(变更日期，11)，4)+’-’+’9’+’-’+substring(变更日期，5，2) as datetime) 

　　when ’Oct’ then cast(right(left(变更日期，11)，4)+’-’+’10’+’-’+substring(变更日期，5，2) as datetime)  

　　when ’Nov’ then cast(right(left(变更日期，11)，4)+’-’+’11’+’-’+substring(变更日期，5，2) as datetime)  

　　when ’Dec’ then cast(right(left(变更日期，11)，4)+’-’+’12’+’-’+substring(变更日期，5，2) as datetime)  

　　end as 变更时间，*

　　into死亡统计

　　from 离退休人员变更信息

　　where 变更原因 like ’%死亡%’

　　or 变更原因 like ’%500004+%’

　　or 变更原因 like ’%500003-%’

　　（4）最晚支付时间大于变更时间的，怀疑死亡还支付的记录

　　select a.个人编号，变更时间，最晚支付时间，datediff(mm，变更时间，最晚支付时间) as 多支付月数，变更原因，* 

　　from 离退休人员变更原因为死亡统计表 a 

　　join v_支付 v on a.个人编号=v.个人编号

　　where 变更时间〈最晚支付时间

　　order by 变更原因，多支付月数

　　6.提前支付，即最早支付和最后缴费时间间隔大于2个月。

　　select z.个人编号，*

　　from v_支付 z

　　left join v_缴费 j on z.个人编号=j.个人编号

　　where datediff(mm，最早支付时间，个人缴费最晚日期)〉2

　　（五）提前退休问题。重点关注男的工龄小于60，女的工龄小于55的记录，查看是否为特殊工种等其他原因。

　　select t.个人编号，t.单位编号，姓名，性别，

　　cast(substring(离退休日期，8，4) as int)-cast(substring(出生日期，8，4) as int) as 工龄，待遇享受开始时间，离退休类别，出生日期，离退休日期，提前退休标志

　　from 离退休人员基本信息 t

　　join 个人信息 g on t.个人编号=g.个人编号

　　where (性别=’1’ and cast(substring(离退休日期，8，4) as int)-cast(substring(出生日期，8，4) as int)〈60)

　　or (性别=’2’ and cast(substring(离退休日期，8，4) as int)-cast(substring(出生日期，8，4) as int)〈55)

　　（吉林市审计局  刘雪晗  供稿）

　　（本文内容仅为作者个人观点，不代表任何审计机关和本网站的观点，未经许可，不得转载）