用友财务总帐(GL)模BI数据ETL分析

2023-08-16 21:53:34

业务需求，如下面的：

现在用友总帐一家公司BI分析案例。

/*

Sql Server2012使用作业设置定时任务,为了保证有一天运行时间

*/

/*

意temp1表里一定要保证要有记录，否则以temp1 来 left join就出现为空的情况。

*/

/*

Step 1: 把全部的数据库列表都插入到BI数据库的DBLIST表里

*/

USE BI;

/*

drop table BI.DBO.TEMP1;

drop table BI.DBO.TEMP2;

drop table BI.DBO.TEMP3;

drop table BI.DBO.TEMP4;

drop table BI.DBO.TEMP5;

drop table BI.DBO.TEMP6;

drop table BI.DBO.TEMP7;

*/

truncate table BI.DBO.DBLIST;

SELECT * FROM BI.DBO.DBLIST;

insert into BI.DBO.DBLIST(DB_CODE,DB_COMPANY_NAME) VALUES ('UFDATA_103_2010','公司名称1');

insert into BI.DBO.DBLIST(DB_CODE,DB_COMPANY_NAME) VALUES ('UFDATA_103_2011','公司名称1');

insert into BI.DBO.DBLIST(DB_CODE,DB_COMPANY_NAME) VALUES ('UFDATA_103_2012','公司名称1');

insert into BI.DBO.DBLIST(DB_CODE,DB_COMPANY_NAME) VALUES ('UFDATA_103_2013','公司名称1');

insert into BI.DBO.DBLIST(DB_CODE,DB_COMPANY_NAME) VALUES ('UFDATA_103_2014','公司名称1');

insert into BI.DBO.DBLIST(DB_CODE,DB_COMPANY_NAME) VALUES ('UFDATA_105_2010','公司名称2‘);

insert into BI.DBO.DBLIST(DB_CODE,DB_COMPANY_NAME) VALUES ('UFDATA_105_2011','公司名称2);

insert into BI.DBO.DBLIST(DB_CODE,DB_COMPANY_NAME) VALUES ('UFDATA_105_2012','公司名称2');

insert into BI.DBO.DBLIST(DB_CODE,DB_COMPANY_NAME) VALUES ('UFDATA_105_2013','公司名称2');

insert into BI.DBO.DBLIST(DB_CODE,DB_COMPANY_NAME) VALUES ('UFDATA_105_2014','公司名称2');

insert into BI.DBO.DBLIST(DB_CODE,DB_COMPANY_NAME) VALUES ('UFDATA_106_2010','公司名称3’);

insert into BI.DBO.DBLIST(DB_CODE,DB_COMPANY_NAME) VALUES ('UFDATA_106_2011','公司名称3');

insert into BI.DBO.DBLIST(DB_CODE,DB_COMPANY_NAME) VALUES ('UFDATA_106_2012','公司名称3');

insert into BI.DBO.DBLIST(DB_CODE,DB_COMPANY_NAME) VALUES ('UFDATA_106_2013','公司名称3');

insert into BI.DBO.DBLIST(DB_CODE,DB_COMPANY_NAME) VALUES ('UFDATA_106_2014','公司名称3');

/*

Step 2: 清空统计表里的记录，方便又一次插入，注意设置BI数据库不记录日志的形式

*/

truncate table BI.DBO.CLB;

truncate table BI.DBO.CWB;

truncate table BI.DBO.RCSHB;

truncate table BI.DBO.RSB;

truncate table BI.DBO.TDJS;

truncate table BI.DBO.ZJB;

/*

Step 3: 循环选择数据库，插入BI.DBO.CLB

*/

declare @DB_CODE char(50)

declare @DB_COMPANY_NAME char(100)

declare @SQL_STRING varchar(8000)

declare table_cursor cursor for select DB_CODE,DB_COMPANY_NAME from BI.DBO.DBLIST

open table_cursor

fetch next from table_cursor into @DB_CODE,@DB_COMPANY_NAME

WHILE @@FETCH_STATUS = 0

BEGIN

/*

差旅表 CLB

*/

set @SQL_STRING='select ' +''''+ '差旅表' +''''+ ' as GLType,'+''''+rtrim(@DB_COMPANY_NAME)+''''+ ' as CompanyName,'

set @SQL_STRING=@SQL_STRING+'isnull(md,0.00) as '+''''+'DomesticTravel'+''''+','

set @SQL_STRING=@SQL_STRING+'iYPeriod,iperiod,iyear into BI.DBO.temp1 from '+rtrim(@DB_CODE)+'.dbo.GL_accsum where ccode='+''''+'660214'+''''+';'

set @SQL_STRING=@SQL_STRING+'select ' +''''+ '差旅表' +''''+ ' as GLType,'+''''+rtrim(@DB_COMPANY_NAME)+''''+ ' as CompanyName,'

set @SQL_STRING=@SQL_STRING+'isnull(md,0.00) as '+''''+'OverseasTravel'+''''+','

set @SQL_STRING=@SQL_STRING+'iYPeriod,iperiod,iyear into BI.DBO.temp2 from '+ rtrim(@DB_CODE)+'.dbo.GL_accsum where ccode='+''''+'660213'+''''+';'

set @SQL_STRING=@SQL_STRING+'select ' +''''+ '差旅表' +''''+ ' as GLType,'+''''+rtrim(@DB_COMPANY_NAME)+''''+ ' as CompanyName,'

set @SQL_STRING=@SQL_STRING+'isnull(md,0.00) as '+''''+'Entertainment'+''''+','

set @SQL_STRING=@SQL_STRING+'iYPeriod,iperiod,iyear into BI.DBO.temp3 from '+ rtrim(@DB_CODE)+'.dbo.GL_accsum where ccode='+''''+'660223'+''''+';'

set @SQL_STRING=@SQL_STRING+' insert into BI.DBO.CLB(GLType,CompanyName,DomesticTravel,OverseasTravel,Entertainment,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.DomesticTravel,'

set @SQL_STRING=@SQL_STRING+' BI.DBO.temp2.OverseasTravel,'

set @SQL_STRING=@SQL_STRING+' BI.DBO.temp3.Entertainment,'

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+' 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;'

exec(@SQL_STRING)

print @SQL_STRING

/*

財务表 CWB

*/

set @SQL_STRING='select ' +''''+ '財务表' +''''+ ' as GLType,'+''''+rtrim(@DB_COMPANY_NAME)+''''+ ' as CompanyName,'

set @SQL_STRING=@SQL_STRING+'isnull(md,0.00) as '+''''+'BankCharges'+''''+','

set @SQL_STRING=@SQL_STRING+'iYPeriod,iperiod,iyear into BI.DBO.temp1 from '+rtrim(@DB_CODE)+'.dbo.GL_accsum where ccode='+''''+'660301'+''''+';'

set @SQL_STRING=@SQL_STRING+'select ' +''''+ '財务表' +''''+ ' as GLType,'+''''+rtrim(@DB_COMPANY_NAME)+''''+ ' as CompanyName,'

set @SQL_STRING=@SQL_STRING+'isnull(md,0.00) as '+''''+'InterestIncome'+''''+','

set @SQL_STRING=@SQL_STRING+'iYPeriod,iperiod,iyear into BI.DBO.temp2 from '+rtrim(@DB_CODE)+'.dbo.GL_accsum where ccode='+''''+'660302'+''''+';'

set @SQL_STRING=@SQL_STRING+'select ' +''''+ '財务表' +''''+ ' as GLType,'+''''+rtrim(@DB_COMPANY_NAME)+''''+ ' as CompanyName,'

set @SQL_STRING=@SQL_STRING+'isnull(md,0.00) as '+''''+'InterestExpenses'+''''+','

set @SQL_STRING=@SQL_STRING+'iYPeriod,iperiod,iyear into BI.DBO.temp3 from '+rtrim(@DB_CODE)+'.dbo.GL_accsum where ccode='+''''+'660303'+''''+';'

set @SQL_STRING=@SQL_STRING+'select ' +''''+ '財务表' +''''+ ' as GLType,'+''''+rtrim(@DB_COMPANY_NAME)+''''+ ' as CompanyName,'

set @SQL_STRING=@SQL_STRING+'isnull(md,0.00) as '+''''+'IncomeTaxes'+''''+','

set @SQL_STRING=@SQL_STRING+'iYPeriod,iperiod,iyear into BI.DBO.temp4 from '+rtrim(@DB_CODE)+'.dbo.GL_accsum where ccode='+''''+'6801'+''''+';'

set @SQL_STRING=@SQL_STRING+' insert into BI.DBO.CWB(GLType,CompanyName,BankCharges,InterestIncome,InterestExpenses,IncomeTaxes,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.BankCharges,'

set @SQL_STRING=@SQL_STRING+' BI.DBO.temp2.InterestIncome,'

set @SQL_STRING=@SQL_STRING+' BI.DBO.temp3.InterestExpenses,'

set @SQL_STRING=@SQL_STRING+' BI.DBO.temp4.IncomeTaxes,'

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.iyear and BI.DBO.temp1.iYPeriod=BI.DBO.temp4.iYPeriod;'

exec(@SQL_STRING)

print @SQL_STRING

/*

日常生活表 RCSHB

*/

set @SQL_STRING='select ' +''''+ '日常生活表' +''''+ ' as GLType,'+''''+rtrim(@DB_COMPANY_NAME)+''''+ ' as CompanyName,'

set @SQL_STRING=@SQL_STRING+'isnull(md,0.00) as '+''''+'FixLineTel'+''''+','

set @SQL_STRING=@SQL_STRING+'iYPeriod,iperiod,iyear into BI.DBO.temp1 from '+rtrim(@DB_CODE)+'.dbo.GL_accsum where ccode='+''''+'660211'+''''+';'

set @SQL_STRING=@SQL_STRING+'select ' +''''+ '日常生活表' +''''+ ' as GLType,'+''''+rtrim(@DB_COMPANY_NAME)+''''+ ' as CompanyName,'

set @SQL_STRING=@SQL_STRING+'isnull(md,0.00) as '+''''+'MobilePhone'+''''+','

set @SQL_STRING=@SQL_STRING+'iYPeriod,iperiod,iyear into BI.DBO.temp2 from '+rtrim(@DB_CODE)+'.dbo.GL_accsum where ccode='+''''+'660212'+''''+';'

set @SQL_STRING=@SQL_STRING+'select ' +''''+ '日常生活表' +''''+ ' as GLType,'+''''+rtrim(@DB_COMPANY_NAME)+''''+ ' as CompanyName,'

set @SQL_STRING=@SQL_STRING+'isnull(md,0.00) as '+''''+'OfficeSupplies'+''''+','

set @SQL_STRING=@SQL_STRING+'iYPeriod,iperiod,iyear into BI.DBO.temp3 from '+rtrim(@DB_CODE)+'.dbo.GL_accsum where ccode='+''''+'660221'+''''+';'

set @SQL_STRING=@SQL_STRING+'select ' +''''+ '日常生活表' +''''+ ' as GLType,'+''''+rtrim(@DB_COMPANY_NAME)+''''+ ' as CompanyName,'

set @SQL_STRING=@SQL_STRING+'isnull(md,0.00) as '+''''+'OfficeRepairMaintenance'+''''+','

set @SQL_STRING=@SQL_STRING+'iYPeriod,iperiod,iyear into BI.DBO.temp4 from '+rtrim(@DB_CODE)+'.dbo.GL_accsum where ccode='+''''+'660222'+''''+';'

set @SQL_STRING=@SQL_STRING+'select ' +''''+ '日常生活表' +''''+ ' as GLType,'+''''+rtrim(@DB_COMPANY_NAME)+''''+ ' as CompanyName,'

set @SQL_STRING=@SQL_STRING+'isnull(md,0.00) as '+''''+'WaterElectricity'+''''+','

set @SQL_STRING=@SQL_STRING+'iYPeriod,iperiod,iyear into BI.DBO.temp5 from '+rtrim(@DB_CODE)+'.dbo.GL_accsum where ccode='+''''+'660231'+''''+';'

set @SQL_STRING=@SQL_STRING+'select ' +''''+ '日常生活表' +''''+ ' as GLType,'+''''+rtrim(@DB_COMPANY_NAME)+''''+ ' as CompanyName,'

set @SQL_STRING=@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.iyear 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='+''''+'66020102'+''''+';'

set @SQL_STRING=@SQL_STRING+'select ' +''''+ '人事表' +''''+ ' as GLType,'+''''+rtrim(@DB_COMPANY_NAME)+''''+ ' as CompanyName,'

set @SQL_STRING=@SQL_STRING+'isnull(md,0.00) as '+''''+'SalesCommission'+''''+','

set @SQL_STRING=@SQL_STRING+'iYPeriod,iperiod,iyear into BI.DBO.temp3 from '+rtrim(@DB_CODE)+'.dbo.GL_accsum where ccode='+''''+'66020103'+''''+';'

set @SQL_STRING=@SQL_STRING+'select ' +''''+ '人事表' +''''+ ' as GLType,'+''''+rtrim(@DB_COMPANY_NAME)+''''+ ' as CompanyName,'

set @SQL_STRING=@SQL_STRING+'isnull(md,0.00) as '+''''+'SocialInsurance'+''''+','

set @SQL_STRING=@SQL_STRING+'iYPeriod,iperiod,iyear into BI.DBO.temp4 from '+rtrim(@DB_CODE)+'.dbo.GL_accsum where ccode='+''''+'660203'+''''+';'

set @SQL_STRING=@SQL_STRING+'select ' +''''+ '人事表' +''''+ ' as GLType,'+''''+rtrim(@DB_COMPANY_NAME)+''''+ ' as CompanyName,'

set @SQL_STRING=@SQL_STRING+'isnull(md,0.00) as '+''''+'Benefits'+''''+','

set @SQL_STRING=@SQL_STRING+'iYPeriod,iperiod,iyear into BI.DBO.temp5 from '+rtrim(@DB_CODE)+'.dbo.GL_accsum where ccode='+''''+'660204'+''''+';'

set @SQL_STRING=@SQL_STRING+'select ' +''''+ '人事表' +''''+ ' as GLType,'+''''+rtrim(@DB_COMPANY_NAME)+''''+ ' as CompanyName,'

set @SQL_STRING=@SQL_STRING+'isnull(md,0.00) as '+''''+'HousingOtherAllowance'+''''+','

set @SQL_STRING=@SQL_STRING+'iYPeriod,iperiod,iyear into BI.DBO.temp6 from '+rtrim(@DB_CODE)+'.dbo.GL_accsum where ccode='+''''+'660205'+''''+';'

set @SQL_STRING=@SQL_STRING+'select ' +''''+ '人事表' +''''+ ' as GLType,'+''''+rtrim(@DB_COMPANY_NAME)+''''+ ' as CompanyName,'

set @SQL_STRING=@SQL_STRING+'0 as '+''''+'HeadCount'+''''+','

set @SQL_STRING=@SQL_STRING+'iYPeriod,iperiod,iyear into BI.DBO.temp7 from '+rtrim(@DB_CODE)+'.dbo.GL_accsum where 1=2;'

set @SQL_STRING=@SQL_STRING+' insert into BI.DBO.RSB(GLType,CompanyName ,Salary ,Bonus ,SalesCommission,SocialInsurance,Benefits,HousingOtherAllowance,HeadCount,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.Salary,'

set @SQL_STRING=@SQL_STRING+' BI.DBO.temp2.Bonus,'

set @SQL_STRING=@SQL_STRING+' BI.DBO.temp3.SalesCommission,'

set @SQL_STRING=@SQL_STRING+' BI.DBO.temp4.SocialInsurance,'

set @SQL_STRING=@SQL_STRING+' BI.DBO.temp5.Benefits,'

set @SQL_STRING=@SQL_STRING+' BI.DBO.temp6.HousingOtherAllowance,'

set @SQL_STRING=@SQL_STRING+' BI.DBO.temp7.HeadCount,'

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.iyear 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+' left join BI.DBO.temp7 on '

set @SQL_STRING=@SQL_STRING+' BI.DBO.temp1.iperiod=BI.DBO.temp7.iperiod and BI.DBO.temp1.iyear=BI.DBO.temp7.iyear and BI.DBO.temp1.iYPeriod=BI.DBO.temp7.iYPeriod;'

exec(@SQL_STRING)

print @SQL_STRING

/*

团队建设表 TDJS

*/

set @SQL_STRING='select ' +''''+ '团队建设表' +''''+ ' as GLType,'+''''+rtrim(@DB_COMPANY_NAME)+''''+ ' as CompanyName,'

set @SQL_STRING=@SQL_STRING+' isnull(md,0.00) as '+''''+'Training'+''''+','

set @SQL_STRING=@SQL_STRING+'iYPeriod,iperiod,iyear into BI.DBO.temp1 from '+rtrim(@DB_CODE)+'.dbo.GL_accsum where ccode='+''''+'660208'+''''+';'

set @SQL_STRING=@SQL_STRING+'select ' +''''+ '团队建设表' +''''+ ' as GLType,'+''''+rtrim(@DB_COMPANY_NAME)+''''+ ' as CompanyName,'

set @SQL_STRING=@SQL_STRING+'isnull(md,0.00) as '+''''+'TeamBuilding'+''''+','

set @SQL_STRING=@SQL_STRING+'iYPeriod,iperiod,iyear into BI.DBO.temp2 from '+rtrim(@DB_CODE)+'.dbo.GL_accsum where ccode='+''''+'660207'+''''+';'

set @SQL_STRING=@SQL_STRING+'select ' +''''+ '团队建设表' +''''+ ' as GLType,'+''''+rtrim(@DB_COMPANY_NAME)+''''+ ' as CompanyName,'

set @SQL_STRING=@SQL_STRING+'isnull(md,0.00) as '+''''+'EmployeeUniforms'+''''+','

set @SQL_STRING=@SQL_STRING+'iYPeriod,iperiod,iyear into BI.DBO.temp3 from '+rtrim(@DB_CODE)+'.dbo.GL_accsum where ccode='+''''+'660210'+''''+';'

set @SQL_STRING=@SQL_STRING+' insert into BI.DBO.TDJS(GLType,CompanyName,Training,TeamBuilding,EmployeeUniforms,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.Training,'

set @SQL_STRING=@SQL_STRING+' BI.DBO.temp2.TeamBuilding,'

set @SQL_STRING=@SQL_STRING+' BI.DBO.temp3.EmployeeUniforms,'

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='INSERT INTO BI.DBO.ZJB (GLType,CompanyName,DYZJ,LJZJ,GDZCJZ,ZJCLGDZC,iYPeriod,iPeriod,iYear)'

set @SQL_STRING=@SQL_STRING+' select ' +''''+ '折旧表' +''''+ ' as GLType,'+''''+rtrim(@DB_COMPANY_NAME)+''''+ ' as CompanyName,'

set @SQL_STRING=@SQL_STRING+' a.mc as '+''''+ '当月折旧'+''''+',a.me as '+''''+'累计折旧'+''''+',(a.me-b.me) as '+''''+'固定资产净值'+''''+' ,a.md-a.mc as '+''''+'新增或处理固定资产'+''''+','

set @SQL_STRING=@SQL_STRING+' a.iYPeriod,a.iperiod,a.iyear '

set @SQL_STRING=@SQL_STRING+' from (select mc,md,me,iyear,iperiod,iYPeriod from '+rtrim(@DB_CODE)+'.dbo.GL_accsum where ccode='+''''+'1601'+''''+') a,'

set @SQL_STRING=@SQL_STRING+' (select mc,md,me,iyear,iperiod,iYPeriod from '+rtrim(@DB_CODE)+'.dbo.GL_accsum where ccode='+''''+'1602'+''''+') b '

set @SQL_STRING=@SQL_STRING+' where a.iYPeriod=b.iYPeriod and a.iperiod=b.iperiod and a.iyear=b.iyear;'

exec(@SQL_STRING)

print @SQL_STRING

fetch next from table_cursor into @DB_CODE,@DB_COMPANY_NAME

END

close table_cursor

deallocate table_cursor

码农公寓

相关文章