create proc p_vba
@start_date datetime
@end_date datetime
as
begin
declare @sql varchar(8000)
set @sql = 'select LTRIM(RTRIM(brand)) as brand,item_name,item_spec '
select @sql = @sql + ' , sum(case client_name when ''' + client_name + ''' then qcsl else 0 end) [' + LTRIM(RTRIM(client_name)) + ']' + ' , sum(case client_name when ''' + client_name + ''' then insl else 0 end) [' + LTRIM(RTRIM(client_name)) + ']'+ ' , sum(case client_name when ''' + client_name + ''' then outsl else 0 end) [' + LTRIM(RTRIM(client_name)) + ']'+ ' , sum(case client_name when ''' + client_name + ''' then kcsl else 0 end) [' + LTRIM(RTRIM(client_name)) + ']'
from (select distinct client_name from v_client_jxcb) as a
set @sql = @sql + ', sum(qcsl) qcsl, sum(insl) insl, sum(outsl) outsl, sum(kcsl) kcsl from v_client_jxcb where rq1 <''2009-03-01'' or (rq2>=''2009-03-01'' and rq2>=''2009-04-30'') or (rq3>=''2009-03-01'' and rq3>=''2009-04-30'') or (rq4 <=''2009-04-30'' ) group by brand,item_name,item_spec'
exec(@sql)
end
go
@start_date datetime
@end_date datetime
as
begin
declare @sql varchar(8000)
set @sql = 'select LTRIM(RTRIM(brand)) as brand,item_name,item_spec '
select @sql = @sql + ' , sum(case client_name when ''' + client_name + ''' then qcsl else 0 end) [' + LTRIM(RTRIM(client_name)) + ']' + ' , sum(case client_name when ''' + client_name + ''' then insl else 0 end) [' + LTRIM(RTRIM(client_name)) + ']'+ ' , sum(case client_name when ''' + client_name + ''' then outsl else 0 end) [' + LTRIM(RTRIM(client_name)) + ']'+ ' , sum(case client_name when ''' + client_name + ''' then kcsl else 0 end) [' + LTRIM(RTRIM(client_name)) + ']'
from (select distinct client_name from v_client_jxcb) as a
set @sql = @sql + ', sum(qcsl) qcsl, sum(insl) insl, sum(outsl) outsl, sum(kcsl) kcsl from v_client_jxcb where rq1 <''2009-03-01'' or (rq2>=''2009-03-01'' and rq2>=''2009-04-30'') or (rq3>=''2009-03-01'' and rq3>=''2009-04-30'') or (rq4 <=''2009-04-30'' ) group by brand,item_name,item_spec'
exec(@sql)
end
go
create proc sp
@start_date datetime
@end_date datetime
as
set nocount on
declare @sql varchar(8000)
set @sql = 'select LTRIM(RTRIM(brand)) as brand,item_name,item_spec '
select @sql = @sql + ' , sum(case client_name when ''' + client_name + ''' then qcsl else 0 end) [' + LTRIM(RTRIM(client_name)) + ']' + ' , sum(case client_name when ''' + client_name + ''' then insl else 0 end) [' + LTRIM(RTRIM(client_name)) + ']'+ ' , sum(case client_name when ''' + client_name + ''' then outsl else 0 end) [' + LTRIM(RTRIM(client_name)) + ']'+ ' , sum(case client_name when ''' + client_name + ''' then kcsl else 0 end) [' + LTRIM(RTRIM(client_name)) + ']'
from (select distinct client_name from v_client_jxcb) as a
set @sql = @sql + ', sum(qcsl) qcsl, sum(insl) insl, sum(outsl) outsl, sum(kcsl) kcsl from v_client_jxcb where rq1 <''2009-03-01'' or (rq2>=''2009-03-01'' and rq2>=''2009-04-30'') or (rq3>=''2009-03-01'' and rq3>=''2009-04-30'') or (rq4 <=''2009-04-30'' ) group by brand,item_name,item_spec'
exec(@sql)
go
--调用:
exec sp '开始时间','结束时间'
@start_date varchar(10)
@end_date varchar(10)
as
set nocount on
declare @sql varchar(8000)
set @sql = 'select LTRIM(RTRIM(brand)) as brand,item_name,item_spec '
select @sql = @sql + ' , sum(case client_name when ''' + client_name + ''' then qcsl else 0 end) [' + LTRIM(RTRIM(client_name)) + ']' + ' , sum(case client_name when ''' + client_name + ''' then insl else 0 end) [' + LTRIM(RTRIM(client_name)) + ']'+ ' , sum(case client_name when ''' + client_name + ''' then outsl else 0 end) [' + LTRIM(RTRIM(client_name)) + ']'+ ' , sum(case client_name when ''' + client_name + ''' then kcsl else 0 end) [' + LTRIM(RTRIM(client_name)) + ']'
from (select distinct client_name from v_client_jxcb) as a
set @sql = @sql + ', sum(qcsl) qcsl, sum(insl) insl, sum(outsl) outsl, sum(kcsl) kcsl from v_client_jxcb where rq1 <'+@start_date+' or (rq2>='+@start_date+' and rq2>='+@end_date+') or (rq3>='+@start_date+' and rq3>='+c+') or (rq4 <='+@start_date+' ) group by brand,item_name,item_spec'
exec(@sql)
go
--调用:
exec sp '2009-03-01','2009-04-30'