K3整车物流进稍存SQL语句20051020

declare @sql  varchar(8000)
declare @sqll1 varchar(8000)
declare @sqll2 varchar(8000)
declare @x  varchar(800)
declare @sqltext varchar(8000)

declare @sql1  varchar(8000)
 set @sql='select * from (select SUBSTRING(FValue, 1, CHARINDEX(''|'', FValue)-1) as 公司帐套号 from dbo.t_systemprofile where FCategory = ''General'' and FKey = ''CompanyAddress'') A,(select FValue as 公司名称 from dbo.t_systemprofile where FCategory = ''General'' and FKey = ''CompanyName'') B,(SELECT ISNULL(SUM(dbo.ICInvInitial.FBegQty), 0.00) AS 初始化数量,ISNULL(SUM(dbo.ICInvInitial.FBegBal), 0.00) AS 初始化金额 FROM dbo.t_Stock INNER JOIN dbo.ICInvInitial ON dbo.t_Stock.FItemID = dbo.ICInvInitial.FStockID WHERE (dbo.t_Stock.FNumber LIKE ''01%'') AND (dbo.ICInvInitial.FSNListID < 0)) C,(SELECT isnull(sum(case when month(FDate)=6  and ((FTranType in (1,2,5,10,40,101,102) and FCancellation=0) or (FTranType=100 and FRefType=0 )) and dbo.t_stock.FNumber like ''01%'' then dbo.ICStockBillEntry.FQty end ),0.00) as 六月采购数量,isnull(sum(case when month(FDate)=6  and ((FTranType in (1,2,5,10,40,101,102) and FCancellation=0) or (FTranType=100 and FRefType=0 )) and dbo.t_stock.FNumber like ''01%'' then dbo.ICStockBillEntry.FAmount end ),0.00) as 六月采购成本,isnull(sum(case when month(FDate)=6  and ((FTranType in (21,28,29) and FCancellation=0) or (FTranType=100 and FRefType<>0 )) and dbo.t_stock.FNumber like ''01%'' then dbo.ICStockBillEntry.FQty end ),0.00) as 六月销售数量,isnull(sum(case when month(FDate)=6  and ((FTranType in (21,28,29) and FCancellation=0) or (FTranType=100 and FRefType<>0 )) and dbo.t_stock.FNumber like ''01%'' then dbo.ICStockBillEntry.FAmount end ),0.00) as 六月销售成本,isnull(sum(case when month(FDate)=7  and ((FTranType in (1,2,5,10,40,101,102) and FCancellation=0) or (FTranType=100 and FRefType=0 )) and dbo.t_stock.FNumber like ''01%'' then dbo.ICStockBillEntry.FQty end ),0.00) as 七月采购数量,isnull(sum(case when month(FDate)=7  and ((FTranType in (1,2,5,10,40,101,102) and FCancellation=0) or (FTranType=100 and FRefType=0 )) and dbo.t_stock.FNumber like ''01%'' then dbo.ICStockBillEntry.FAmount end ),0.00) as 七月采购成本,isnull(sum(case when month(FDate)=7  and ((FTranType in (21,28,29) and FCancellation=0) or (FTranType=100 and FRefType<>0 )) and dbo.t_stock.FNumber like ''01%'' then dbo.ICStockBillEntry.FQty  end ),0.00) as 七月销售数量,isnull(sum(case when month(FDate)=7  and ((FTranType in (21,28,29) and FCancellation=0) or (FTranType=100 and FRefType<>0 )) and dbo.t_stock.FNumber like ''01%'' then dbo.ICStockBillEntry.FAmount end ),0.00) as 七月销售成本,isnull(sum(case when month(FDate)=8  and ((FTranType in (1,2,5,10,40,101,102) and FCancellation=0) or (FTranType=100 and FRefType=0 )) and dbo.t_stock.FNumber like ''01%'' then dbo.ICStockBillEntry.FQty end ),0.00) as 八月采购数量,isnull(sum(case when month(FDate)=8  and ((FTranType in (1,2,5,10,40,101,102) and FCancellation=0) or (FTranType=100 and FRefType=0 )) and dbo.t_stock.FNumber like ''01%'' then dbo.ICStockBillEntry.FAmount end ),0.00) as 八月采购成本,isnull(sum(case when month(FDate)=8  and ((FTranType in (21,28,29) and FCancellation=0) or (FTranType=100 and FRefType<>0 )) and dbo.t_stock.FNumber like ''01%'' then dbo.ICStockBillEntry.FQty end ),0.00) as 八月销售数量,isnull(sum(case when month(FDate)=8  and ((FTranType in (21,28,29) and FCancellation=0) or (FTranType=100 and FRefType<>0 )) and dbo.t_stock.FNumber like ''01%'' then dbo.ICStockBillEntry.FAmount end ),0.00) as 八月销售成本,isnull(sum(case when month(FDate)=9  and ((FTranType in (1,2,5,10,40,101,102) and FCancellation=0) or (FTranType=100 and FRefType=0 )) and dbo.t_stock.FNumber like ''01%'' then dbo.ICStockBillEntry.FQty end ),0.00) as 九月采购数量,isnull(sum(case when month(FDate)=9  and ((FTranType in (1,2,5,10,40,101,102) and FCancellation=0) or (FTranType=100 and FRefType=0 )) and dbo.t_stock.FNumber like ''01%'' then dbo.ICStockBillEntry.FAmount end ),0.00) as 九月采购成本,isnull(sum(case when month(FDate)=9  and ((FTranType in (21,28,29) and FCancellation=0) or (FTranType=100 and FRefType<>0 )) and dbo.t_stock.FNumber like ''01%'' then dbo.ICStockBillEntry.FQty end ),0.00) as 九月销售数量,isnull(sum(case when month(FDate)=9  and ((FTranType in (21,28,29) and FCancellation=0) or (FTranType=100 and FRefType<>0 )) and dbo.t_stock.FNumber like ''01%'' then dbo.ICStockBillEntry.FAmount end ),0.00) as 九月销售成本,isnull(sum(case when month(FDate)=10  and ((FTranType in (1,2,5,10,40,101,102) and FCancellation=0) or (FTranType=100 and FRefType=0 )) and dbo.t_stock.FNumber like ''01%'' then dbo.ICStockBillEntry.FQty end ),0.00) as 十月采购数量,isnull(sum(case when month(FDate)=10  and ((FTranType in (1,2,5,10,40,101,102) and FCancellation=0) or (FTranType=100 and FRefType=0 )) and dbo.t_stock.FNumber like ''01%'' then dbo.ICStockBillEntry.FAmount end ),0.00) as 十月采购成本,isnull(sum(case when month(FDate)=10  and ((FTranType in (21,28,29) and FCancellation=0) or (FTranType=100 and FRefType<>0 )) and dbo.t_stock.FNumber like ''01%'' then dbo.ICStockBillEntry.FQty end ),0.00) as 十月销售数量,isnull(sum(case when month(FDate)=10  and ((FTranType in (21,28,29) and FCancellation=0) or (FTranType=100 and FRefType<>0 )) and dbo.t_stock.FNumber like ''01%'' then dbo.ICStockBillEntry.FAmount end ),0.00) as 十月销售成本 FROM ((dbo.ICStockBill INNER JOIN dbo.ICStockBillEntry ON dbo.ICStockBill.FInterID = dbo.ICStockBillEntry.FInterID) )left join dbo.t_stock on dbo.t_stock.FItemID=dbo.ICStockBillEntry.FDCStockID) D,(SELECT  isnull(sum(case when MONTH(dbo.ICSale.FDate) = 6 then FAmount end ),0.00) as 六月销售金额,isnull(sum(case when MONTH(dbo.ICSale.FDate) = 7 then FAmount end ),0.00) as 七月销售金额,isnull(sum(case when MONTH(dbo.ICSale.FDate) = 8 then FAmount end ),0.00) as 八月销售金额,isnull(sum(case when MONTH(dbo.ICSale.FDate) = 9 then FAmount end ),0.00) as 九月销售金额,isnull(sum(case when MONTH(dbo.ICSale.FDate) = 10 then FAmount end ),0.00) as 十月销售金额 FROM dbo.ICSale INNER JOIN dbo.ICSaleEntry ON dbo.ICSale.FInterID = dbo.ICSaleEntry.FInterID) E'
--'select * from (select FValue as 单位名称 from dbo.t_systemprofile where FCategory = ''General'' and FKey = ''CompanyName'') z,(select FValue as 联系电话 from dbo.t_systemprofile where FCategory = ''General'' and FKey = ''CompanyPhone'') z1,(SELECT sum(FBeginBalanceFor) as 配件累计金额 FROM dbo.t_Balance INNER JOIN dbo.t_Account  ON dbo.t_Balance.FAccountID = dbo.t_Account.FAccountID where fnumber= ''1243.02.01'' and dbo.t_Balance.fcurrencyid = 0) z2'

--'select fname as 用户名, fforbidden as 是否禁用,ccccc.fgs as 公司名称  from dbo.t_User ,(SELECT fvalue as fgs FROM dbo.t_SystemProfile  WHERE FDescription =''公司名称'') ccccc'
--set @sqll1='and name in (''AIS20050123112846'')'
--print @sqll1

declare cr cursor for SELECT NAME FROM master.dbo.sysdatabases WHERE NAME LIKE 'AIS2005%' and right(NAME,1)  not in ('c','b','j','h','y')
--   @sqll1

open cr
fetch next from CR into @x
--SELECT NAME FROM master.dbo.sysdatabases WHERE NAME LIKE 'AIS2005%'
--select  @X
 if  object_id('tempdb..##tempuse') is not null
 begin
    Drop Table ##Tempuse
    print '删除'
 end

 set @sql1=replace( @sql,'dbo.',@X +'.dbo.')
        --select @sql
 
 set @sqltext='select * into ##tempuse from (' + @sql1 + ' ) aaaa'
 exec(@sqltext)
--print 'dddd'

fetch next from CR into @x

while @@fetch_status=0
begin
--SELECT @X
print @x

set @sql1=replace( @sql,'dbo.',@X+'.dbo.')

SET @sqltext= 'Insert INTO ##Tempuse ' + @sql1

--'第二步插入语句

--select @sqltext
EXEC(@sqltext)
    fetch next from cr into @x
end

Select * From ##Tempuse

-- Drop Table ##Tempuse
close cr

deallocate cr
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO