
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Up_ProdTiCheng1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 drop procedure [dbo].[Up_ProdTiCheng1]

Go

Create proc Up_ProdTiCheng1 @BrandCode varchar(6),@YWCode varchar(6), @sDate varchar(10), @eDate varchar(10)
As
create table #RptMaster(BrandCode varchar(6),BrandName varchar(20),YWCode varchar(6),YWName varchar(10),Prototal money,TiCheng money)

if @sDate='' or @sDate is null set @sDate = '0000-00-00'
if @eDate='' or @eDate is null set @eDate = '9999-99-99'


declare  @sql varchar(8000)

Set @Sql='insert into #RptMaster '
+' Select IsNull(c.BrandCode,''''),IsNull(e.BrandName,''''),YWCode,d.UserName,prototal=Sum(b.Prototal)
,TiChengTotal=Sum(b.ProTotal*IsNull(
(Case when c.TiCheng>0 then c.TiCheng else IsNull(d.TiCheng,0) end)
/100,0) ) from '
+' FWXHH a join FWXHB b on a.FormNo=b.FormNo '
+' left join Product c on b.ProdCode=c.ProdCode '
+' left join tUser d on a.YWCode=d.UserCode ' 
+' left join tbrandSet e on c.BrandCode=e.BrandCode ' 
+' where CheckType=''3'' and WriteDate Between '''+@sDate+''' and '''+ @eDate+''''
if @YWCode<>''
  Set @Sql=@Sql+' and YWCode='''+@YWCode+''''
if Len(@BrandCode)<>0
  Set @Sql=@Sql+' and c.BrandCode='''+@BrandCode+''''
Set @Sql=@Sql+' Group by YWCode,d.UserName,c.BrandCode,e.BrandName '
Exec (@Sql)

Set @Sql='insert into #RptMaster 
 Select IsNull(c.BrandCode,''''),IsNull(e.BrandName,''''),YWCode,d.UserName,prototal=Sum(b.Prototal)
 ,TiChengTotal=Sum(b.ProTotal*IsNull(
 (Case when c.TiCheng>0 then c.TiCheng else IsNull(d.TiCheng,0) end)
 /100,0) ) from 
 FWTHH a join FWTHB b on a.FormNo=b.FormNo 
 left join Product c on b.ProdCode=c.ProdCode 
 left join tUser d on a.YWCode=d.UserCode  
 left join tbrandSet e on c.BrandCode=e.BrandCode
 where CheckType=''3'' and WriteDate Between '''+@sDate+''' and '''+ @eDate+''''
if @YWCode<>''
  Set @Sql=@Sql+' and YWCode='''+@YWCode+''''
if Len(@BrandCode)<>0
  Set @Sql=@Sql+' and c.BrandCode='''+@BrandCode+''''
Set @Sql=@Sql+' Group by YWCode,d.UserName,c.BrandCode,e.BrandName '
Exec (@Sql)

Set @Sql='Select BrandCode,BrandName,YWCode,YWName,ProTotal=Sum(ProTotal),TiCheng=Sum(TiCheng) from #RptMaster
Group by YWCode,YWName,BrandCode,BrandName '
Exec (@Sql)

Drop table #RptMaster