suchen

Heim >Datenbank >MySQL-Tutorial >用友财务总账(GL)模块的BI数据ETL分析

用友财务总账(GL)模块的BI数据ETL分析

WBOY
WBOYOriginal
2016-06-07 16:12:321634Durchsuche

业务需求如下： 某公司目前用了用友的总账BI分析案例。 /* Sql Server2012使用作业设置定时任务,来保证一天执行一次 */ /* 一定要注意temp1表里一定要保证要有记录，否则以temp1 来 left join就出现为空的情况。 */ /* Step 1: 把所有的数据库列表都插入到BI

业务需求如下：

某公司目前用了用友的总账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;'



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

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

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;'
set @SQL_STRING=@SQL_STRING+' drop table BI.DBO.temp7;'


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=@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



/*
折旧表 ZJB
*/

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

 

Stellungnahme：
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Vorheriger Artikel：Redis深入之数据结构Nächster Artikel：redis学习笔记

In Verbindung stehende Artikel

Mehr sehen