用友财务总账(GL)模块的BI数据ETL分析(四)
ING=@SQL_STRING+'isnull(md,0.00) as '+''''+'MISExpense'+''''+','
set @SQL_STRING=@SQL_STRING+'iYPeriod,iperiod,iyear into BI.DBO.temp6 from '+rtrim(@DB_CODE)+'.dbo.GL_accsum where ccode='+''''+'660233'+''''+';'
set @SQL_STRING=@SQL_STRING+' insert into BI.DBO.RCSHB(GLType,CompanyName,FixLineTel,MobilePhone,OfficeSupplies,OfficeRepairMaintenance,WaterElectricity,MISExpense,iYPeriod,iperiod,iyear)'
set @SQL_STRING=@SQL_STRING+' select BI.DBO.temp1.GLType,BI.DBO.temp1.CompanyName,'
set @SQL_STRING=@SQL_STRING+' BI.DBO.temp1.FixLineTel,'
set @SQL_STRING=@SQL_STRING+' BI.DBO.temp2.MobilePhone,'
set @SQL_STRING=@SQL_STRING+' BI.DBO.temp3.OfficeSupplies,'
set @SQL_STRING=@SQL_STRING+' BI.DBO.temp4.OfficeRepairMaintenance,'
set @SQL_STRING=@SQL_STRING+' BI.DBO.temp5.WaterElectricity,'
set @SQL_STRING=@SQL_STRING+' BI.DBO.temp6.MISExpense,'
set @SQL_STRING=@SQL_STRING+' BI.DBO.temp1.iYPeriod,'
set @SQL_STRING=@SQL_STRING+' BI.DBO.temp1.iperiod,BI.DBO.temp1.iyear from BI.DBO.temp1 left join BI.DBO.temp2 on '
set @SQL_STRING=@SQL_STRING+' BI.DBO.temp1.iperiod=BI.DBO.temp2.iperiod and BI.DBO.temp1.iyear=BI.DBO.temp2.iyear and BI.DBO.temp1.iYPeriod=BI.DBO.temp2.iYPeriod'
set @SQL_STRING=@SQL_STRING+' left join BI.DBO.temp3 on '
set @SQL_STRING=@SQL_STRING+' BI.DBO.temp1.iperiod=BI.DBO.temp3.iperiod and BI.DBO.temp1.iyear=BI.DBO.temp3.iyear and BI.DBO.temp1.iYPeriod=BI.DBO.temp3.iYPeriod'
set @SQL_STRING=@SQL_STRING+' left join BI.DBO.temp4 on '
set @SQL_STRING=@SQL_STRING+' BI.DBO.temp1.iperiod=BI.DBO.temp4.iperiod and BI.DBO.temp1.iyear=BI.DBO.temp4.i
year and BI.DBO.temp1.iYPeriod=BI.DBO.temp4.iYPeriod'
set @SQL_STRING=@SQL_STRING+' left join BI.DBO.temp5 on '
set @SQL_STRING=@SQL_STRING+' BI.DBO.temp1.iperiod=BI.DBO.temp5.iperiod and BI.DBO.temp1.iyear=BI.DBO.temp5.iyear and BI.DBO.temp1.iYPeriod=BI.DBO.temp5.iYPeriod'
set @SQL_STRING=@SQL_STRING+' left join BI.DBO.temp6 on '
set @SQL_STRING=@SQL_STRING+' BI.DBO.temp1.iperiod=BI.DBO.temp6.iperiod and BI.DBO.temp1.iyear=BI.DBO.temp6.iyear and BI.DBO.temp1.iYPeriod=BI.DBO.temp6.iYPeriod;'
set @SQL_STRING=@SQL_STRING+' drop table BI.DBO.temp1;'
set @SQL_STRING=@SQL_STRING+' drop table BI.DBO.temp2;'
set @SQL_STRING=@SQL_STRING+' drop table BI.DBO.temp3;'
set @SQL_STRING=@SQL_STRING+' drop table BI.DBO.temp4;'
set @SQL_STRING=@SQL_STRING+' drop table BI.DBO.temp5;'
set @SQL_STRING=@SQL_STRING+' drop table BI.DBO.temp6;'
exec(@SQL_STRING)
print @SQL_STRING
/*
人事表 RSB
*/
set @SQL_STRING='select ' +''''+ '人事表' +''''+ ' as GLType,'+''''+rtrim(@DB_COMPANY_NAME)+''''+ ' as CompanyName,'
set @SQL_STRING=@SQL_STRING+'isnull(md,0.00) as '+''''+'Salary'+''''+','
set @SQL_STRING=@SQL_STRING+'iYPeriod,iperiod,iyear into BI.DBO.temp1 from '+rtrim(@DB_CODE)+'.dbo.GL_accsum where ccode='+''''+'66020101'+''''+';'
set @SQL_STRING=@SQL_STRING+'select ' +''''+ '人事表' +''''+ ' as GLType,'+''''+rtrim(@DB_COMPANY_NAME)+''''+ ' as CompanyName,'
set @SQL_STRING=@SQL_STRING+'isnull(md,0.00) as '+''''+'Bonus'+''''+','
set @SQL_STRING=@SQL_STRING+'iYPeriod,iperiod,iyear into BI.DBO.temp2 from '+rtrim(@DB_CODE)+'.dbo.GL_accsum where ccode='+''''+'