declare @channelid varchar(100)
set @channelid='''WH00026'',''WH00083'''
declare @sql varchar(1000)
if(object_id('tempdb.dbo.#ac') is not null)
begin
drop table #ac
end
create table #ac
(
channelid varchar(50),
pmoney decimal(18,2),
ptype tinyint,
inday varchar(10),
scale varchar(10),
comScale varchar(10)
)
set @sql='insert into #ac'
set @sql=@sql+' select a.channelid,a.pmoney,a.ptype,a.inday,a.scale,a.comScale'
set @sql=@sql+' from('
set @sql=@sql+' select oc.channelid,sum(totalFee) as pmoney,inday,scale,comScale,1 as ptype from orderCPS oc'
set @sql=@sql+' left join userInfo u on oc.channelid=u.channelid'
set @sql=@sql+' where inday between ''2016-10-13'' and ''2016-10-13'' and oc.channelid in ('+@channelid+') and (comOrderStatus is null or comOrderStatus=0)'
set @sql=@sql+' group by oc.channelid,inday,scale,comScale'
set @sql=@sql+' union all'
set @sql=@sql+' select oc.channelid,sum(totalFee) as pmoney,inday,scale,comScale,2 as ptype from orderCPS oc'
set @sql=@sql+' left join userInfo u on oc.channelid=u.channelid'
set @sql=@sql+' where inday between ''2016-10-13'' and ''2016-10-13'' and oc.channelid in ('+@channelid+') and orderStatus=0'
set @sql=@sql+' group by oc.channelid,inday,scale,comScale) a'
print @sql
exec(@sql);
select * from #ac
