--20100513修改325,326,327,169的Response Weighting /* author:ww time :2009/10/27 aim :导出Table中待检测的table 执行: EXECUTE Sp_Extract_table 'F325_20100317_183047' EXECUTE Sp_Extract_table 'F326_20090914_130136' EXECUTE Sp_Extract_table 'F327_20100512_133136' EXECUTE Sp_Extract_table 'F327B_20100512_133234' EXECUTE Sp_Extract_table 'F169b_20090701_174700' EXECUTE Sp_Extract_table 'F169C_20090519_180700' EXECUTE Sp_Extract_table 'F169_0902_20090415_123600' EXECUTE Sp_Extract_table 'F169_0903_20090427_094900' */ create procedure Sp_Extract_table(@Catecode as varchar(500)) as set nocount on Declare @string Varchar(5000) execute FuncDeletetable 'V_data' select @string = ' SELECT * into V_data FROM '+@Catecode execute( @string ) --------***************修改数据的部分*****************************---------------------------- update v_data set pur_calc_vol2 = lurunumber*1000 where isnull(pur_calc_vol2,0) <=1000 and lurunumber>=1 update v_data set pur_calc_vol2 = 1000 where lurunumber<1 ------------------********************************************---------------------------- execute FuncDeleteView 'V_C35' set @string = 'create view V_C35 as select pp,0 as bzhkco, bzhksn, bzhkstore, convert(int,(CASE WHEN LURUFCODE <10000000 then LURUFCODE * 10 else LURUFCODE end )) as pur_Panelistnum , Pur_YPW_on_extract, Pur_Category, SPA, convert(int,lurunumber) as Pur_Units, convert(int,Pur_Price) as Pur_Price , convert(int,Pur_Shop) as Pur_Shop, Pur_Gender, LURUCONSUM as Buyer, convert(int,Pur_Age) as Pur_age, convert(int,Pur_Calc_Vo11) as Pur_Calc_Vo11, convert(int,Pur_Calc_Vol2) as Pur_Calc_Vol2, convert(int,Pur_Calc_Tot_Price) as Pur_Calc_Tot_Price , case when right(rw_all.Channel,8)=lurufcode and YEAR(lurupurday)=right(Period,4) and Ref_att01 in(3469,3873,3874,3875,3876) then ''1'' else ''0'' end as Pur_Extreme_Flag, case when right(rw_all.Channel,8)=lurufcode and YEAR(lurupurday)=right(Period,4) and Ref_att01 in(3469,3873,3874,3875,3876) then Factor else 0 end as Pur_Extreme_Percentage, convert(int,Promotion) as Promotion , dbo.get_WD(lurupurday) AS Weekday, YEAR(lurupurday) AS [Year],MONTH(lurupurday) AS [Month], DAY(lurupurday) AS [Day], Pur_PurchaseId, convert(int,Ref_Item_Code) as Ref_Item_Code, newbarcode as Pur_Barcode, ''0'' as MCA, ''0'' as MCB, BuyerAge, UserAge, newCity as City, convert(int,v_data.Channel) as Channel, BuyerGender, UserGender, Ref_att01,Ref_att02,Ref_att03,Ref_att04,Ref_att05,Ref_att06,Ref_att07,Ref_att08,Ref_att09,Ref_att10,Ref_att11,Ref_att12,Ref_att13,Ref_att14,Ref_att15 from V_data,rw_all where rw_all.category=325' execute( @string ) Declare @time varchar(100) set @time = Replace (Replace (Replace (convert(varchar(30),getdate(),20), '-',''),' ','_'),':','')Declare @tablename varchar(100) if @catecode like '%[_]0902[_]%' or @catecode like '%[_]0903[_]%' begin set @tablename = left(@Catecode,9)+'_'+@time print @tablename end else begin set @tablename = left(@Catecode,charindex('_',@Catecode)-1)+'_'+@time print @tablename declare @strDel varchar(1000) declare @strRename varchar(1000) declare @doscmd varchar(8000) --4 非调试情况下, 倒出 395NC35_3y.Dat set @strDel = 'del c:\temp\'+@tablename+'_'+'NC35_3Y.txt' set @strRename = 'rename c:\temp\NC35_3y.txt '+@tablename+'_'+'NC35_3Y.txt' EXEC master.dbo.xp_cmdshell 'dtsrun /S /E /N"NC35_F"' EXEC master.dbo.xp_cmdshell @strDel print @strDel print @strRename EXEC master.dbo.xp_cmdshell @strRename set @doscmd = 'copy c:\temp\'+@tablename+'_'+'NC35_2009.txt \\10.20.12.221\SytaxServer_P\H\D' print @doscmd EXEC master.dbo.xp_cmdshell @doscmd,no_output exec sp_rename 'V_data',@tablename
/*
author:ww
time :2009/10/27
aim :导出Table中待检测的table
执行:
EXECUTE Sp_Extract_table 'F325_20100317_183047'
EXECUTE Sp_Extract_table 'F326_20090914_130136'
EXECUTE Sp_Extract_table 'F327_20100512_133136'
EXECUTE Sp_Extract_table 'F327B_20100512_133234'
EXECUTE Sp_Extract_table 'F169b_20090701_174700'
EXECUTE Sp_Extract_table 'F169C_20090519_180700'
EXECUTE Sp_Extract_table 'F169_0902_20090415_123600'
EXECUTE Sp_Extract_table 'F169_0903_20090427_094900'
*/
create procedure Sp_Extract_table(@Catecode as varchar(500))
as
set nocount on
Declare @string Varchar(5000)
execute FuncDeletetable 'V_data'
select @string = ' SELECT * into V_data FROM '+@Catecode
execute( @string )
--------***************修改数据的部分*****************************----------------------------
update v_data set pur_calc_vol2 = lurunumber*1000 where isnull(pur_calc_vol2,0) <=1000 and lurunumber>=1
update v_data set pur_calc_vol2 = 1000 where lurunumber<1
------------------********************************************----------------------------
execute FuncDeleteView 'V_C35'
set @string = 'create view V_C35 as select
pp,0 as bzhkco,
bzhksn, bzhkstore,
convert(int,(CASE WHEN LURUFCODE <10000000 then LURUFCODE * 10 else LURUFCODE end )) as pur_Panelistnum ,
Pur_YPW_on_extract,
Pur_Category,
SPA,
convert(int,lurunumber) as Pur_Units,
convert(int,Pur_Price) as Pur_Price ,
convert(int,Pur_Shop) as Pur_Shop,
Pur_Gender,
LURUCONSUM as Buyer,
convert(int,Pur_Age) as Pur_age,
convert(int,Pur_Calc_Vo11) as Pur_Calc_Vo11,
convert(int,Pur_Calc_Vol2) as Pur_Calc_Vol2,
convert(int,Pur_Calc_Tot_Price) as Pur_Calc_Tot_Price ,
case when right(rw_all.Channel,8)=lurufcode and YEAR(lurupurday)=right(Period,4) and Ref_att01 in(3469,3873,3874,3875,3876) then ''1''
else ''0'' end as Pur_Extreme_Flag,
case when right(rw_all.Channel,8)=lurufcode and YEAR(lurupurday)=right(Period,4) and Ref_att01 in(3469,3873,3874,3875,3876) then Factor
else 0 end as Pur_Extreme_Percentage,
convert(int,Promotion) as Promotion ,
dbo.get_WD(lurupurday) AS Weekday,
YEAR(lurupurday) AS [Year],MONTH(lurupurday) AS [Month], DAY(lurupurday) AS [Day],
Pur_PurchaseId,
convert(int,Ref_Item_Code) as Ref_Item_Code,
newbarcode as Pur_Barcode,
''0'' as MCA,
''0'' as MCB,
BuyerAge,
UserAge,
newCity as City,
convert(int,v_data.Channel) as Channel,
BuyerGender,
UserGender,
Ref_att01,Ref_att02,Ref_att03,Ref_att04,Ref_att05,Ref_att06,Ref_att07,Ref_att08,Ref_att09,Ref_att10,Ref_att11,Ref_att12,Ref_att13,Ref_att14,Ref_att15
from V_data,rw_all where rw_all.category=325'
execute( @string )
Declare @time varchar(100)
set @time = Replace (Replace (Replace (convert(varchar(30),getdate(),20), '-',''),' ','_'),':','')Declare @tablename varchar(100)
if @catecode like '%[_]0902[_]%' or @catecode like '%[_]0903[_]%'
begin
set @tablename = left(@Catecode,9)+'_'+@time
print @tablename
end
else
begin
set @tablename = left(@Catecode,charindex('_',@Catecode)-1)+'_'+@time
print @tablename
declare @strDel varchar(1000)
declare @strRename varchar(1000)
declare @doscmd varchar(8000)
--4 非调试情况下, 倒出 395NC35_3y.Dat
set @strDel = 'del c:\temp\'+@tablename+'_'+'NC35_3Y.txt'
set @strRename = 'rename c:\temp\NC35_3y.txt '+@tablename+'_'+'NC35_3Y.txt'
EXEC master.dbo.xp_cmdshell 'dtsrun /S /E /N"NC35_F"'
EXEC master.dbo.xp_cmdshell @strDel
print @strDel
print @strRename
EXEC master.dbo.xp_cmdshell @strRename
set @doscmd = 'copy c:\temp\'+@tablename+'_'+'NC35_2009.txt \\10.20.12.221\SytaxServer_P\H\D'
print @doscmd
EXEC master.dbo.xp_cmdshell @doscmd,no_output
exec sp_rename 'V_data',@tablename
end 就会说end 附近有语法错误