SQL技术在失地保障审计中的高级应用

      审计实务中，SQL技术应用极其广泛，尤其在业务数据审计中，其发挥的作用尤为突出，以下是笔者在某县失地保障审计中应用的一个片段，以SQL Server语法编写代码。

      数据：某县失地保障养老保险发放表（对部分字段作了删除，表名为“test”），数据结构如下：

      

      样例数据内容如下（以2013年发放数据为例，对享受人员姓名作了修改）：

      

      发放背景资料：该县失地农民养老保障金按季度发放，凡男年满60周岁，女年满55周岁即可领取，上表中的发放月数大于3者，为补发以前月份，如果某人在某月最后一天达到规定年龄，当月也应正常发放。

      根据审计目标需要确定享受人在哪个季度、哪个月份提前领取了多少失地养老保障金，即多领金额。

      因发放表中每次发放金额包含多月，享受人员可能在部分月份未到达年龄，而在其他月份并未满整周岁，故需对每月进行判断，即每人需要判断的次数等于发放月数。如左宇梅发放月数为20个月，需要判断20次，其他类推。为实现审计目标，预先创建一张数字辅助表bignum，仅包含一列，存储1～10000个整数（实践中根据需要确定，一般只需100个整型数即可），创建bignum表的代码如下：

      CREATE TABLE bignum

      （val INT NOT NULL PRIMARY KEY）；

      DECLARE@max INT=10000，@i INT=1；

      INSERT INTO bignum（val） values（1）；

      WHILE@i*2＜=@max

      BEGIN

      INSERT INTO bignum SELECT val+@i

      FROM bignum；

      SET@i=@i*2；

      INSERT bignum SELECT val+@i

      FROM bignum

      WHERE val+@i＜=@max；

      上述语句以最小化日志记录方式为代价，创造极高的插入效率，也可以使用递归CTE技术创建类似的表。利用这张辅助数字表，将其与发放表（test）关联，因发放涉及到年龄及性别，还需从身份证中提取信息，季末最后一天所在日期根据发放季度使用CASE判断：

      SELECT t.*

      ，CASE CAST（SUBSTRING（身份证号码，17，1）AS INT）%2

      WHEN 0 THEN′女′

      ELSE′男′

      END AS性别

      ，CAST（SUBSTRING（身份证号码，7，4）+′-′+SUBSTRING（身份证号码，11，2）+′-′+SUBSTRING（身份证号码，13，2）AS DATE）出生日期

      ，CASE发放季度

      WHEN 201301′THEN CAST（′20130331′ AS DATE）

      WHEN′ 201302′THEN CAST（′20130630′AS DATE）

      WHEN ′201303′ THEN CAST（′20130930′ AS DATE）

      WHEN ′201304′ THEN CAST（′20131231′ AS DATE）

      END AS季末日期

      ，b.val AS行号

      from Test t

      join BigNum b

      on t.发放月数＞=b.va1：

      结果如下：

      

      这样每条记录均产生与发放月数相同的记录，如左宇梅发放月数为20，则产生20条记录。季末最后一天所在日期与val列之差可反推至以前日期，因此还需将val列值减1后取负数即可返回上月末日期，然后用这个日期与出生日期之差计算出天数，并除以365.25，即可算出享受人至各月末的周岁值。

      完整代码如下：

      WITH i1

      AS（SELECT t.*

      ，CASE CAST（SUBSTRING（身份证号码，17，1）AS INT）%2

      WHEN 0 THEN′女′

      ELSE′男′

      END AS性别

      ，CAST（SUBSTRING（身份证号码，7，4）+′-′+SUBSTRING（身份证号码，11，2）+′ ′+SUBSTRING（身份证号码，13，2）AS DATE）出生日期

      ，CASE发放季度

      WHEN ′201301′ THEN CAST（20130331′ AS DATE）

      WHEN ′201302′ THEN CAST（20130630′ AS DATE）

      WHEN ′201303′ THEN CAST（′20130930′ AS DATE）