数据式审计和数据挖掘典型案例典型案例对大数（根据业务处理逻辑）1将HIS系统中药品购销数据与财务账面相比较理论上，财务帐应该真实反应实际情况，财务账与HIS业务系统中相对应数据应当一致，将HIS系统中的药品购销数据与财务账面“药品”科目的购销核算情况进行比对，看是否一致。审计步骤：第一步：汇总药品入库情况selectsubstring(jzrq,5,2)as月份,sum(lsje)as零售价总计,sum(jjje)as进价总计,sum(lsje)-sum(jjje)as进销差额fromYK_YPRKZDwherejzrqlike'2008%'andjzbzin(1,2)andksdm='4425'and(gzbz=0or(gzbz=1anddpbz=2))andcxbz='0'groupbysubstring(jzrq,5,2)orderbysubstring(jzrq,5,2)第二步：汇总药品退货情况selectsubstring(jzrq,5,2)as月份,sum(ypje_ls)as零售价总计,sum(ypje_pf)as进价总计,sum(thje)as退货金额fromdbo.YK_YPTHZDwherejzrqlike'2008%'andksdm='4425'andjzbzin(1,2)andjlzt='0'andthfs='01'groupbysubstring(jzrq,5,2)orderbysubstring(jzrq52)第三步：药品出库情况selectsubstring(yfrq,5,2)as月份,sum(ypje_ls)as零售价总计,sum(ypje_pf)as进价总计,sum(ypje_ls)-sum(ypje_pf)as进销差额fromdbo.YK_YPCKZDwhereyfrqlike'2008%'andckfsdm='01'andykdm='4425'andjzbz<>'0'andjlzt='0'andqrbz='1'groupbysubstring(yfrq,5,2)orderbysubstring(yfrq,5,2)第四步：药品退库情况selectsubstring(ykrq,5,2)as月份,sum(ypje_ls)as零售价总计,sum(ypje_pf)as进价总计,sum(ypje_ls)-sum(ypje_pf)as进销差额fromdbo.YK_YFTKZDwhereykrqlike'2008%'andykdm='4425'andjzbz<>'0'andjlzt='0'groupbysubstring(ykrq,5,2)orderbysubstring(ykrq,5,2)第五步：科室发药及退药情况selectsubstring(jzrq,5,2)as月份,sum(ypje_ls)as零售价总计,sum(ypje_pf)as进价总计,sum(ypje_ls)-sum(ypje_pf)as进销差额fromYK_KSFYZDwherejzrqlike'2008%'andykdm='4425'andjzbzin(1,2)andjlzt='0'groupbysubstring(jzrq,5,2)orderbysubstring(jzrq,5,2)第六步：盘盈、盘亏情况比对selectsubstring(jzrq,5,2)as月份,sum(pyje_ls)as盘盈零售价总计,sum(pyje_pf)as盘盈进价总计,sum(pyje_ls)-sum(pyje_pf)as盘盈进销差额,sum(pkje_ls)as盘亏零售价总计,sum(pkje_pf)as盘亏进价总计,sum(pkje_ls)-sum(pkje_pf)as盘亏进销差额fromdbo.YK_YPPDZDwherejzrqlike'2008%'andksdm='4425'andjzbz<>'0'andjlzt='0'groupbysubstring(jzrq,5,2)orderbysubstring(jzrq,...
