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

Go

Create proc Up_YWTiCheng1 @YWCode varchar(6), @sDate varchar(10), @eDate varchar(10)
As

create table #RptMaster(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 YWCode,b.UserName,ProTotal=Sum(ProTotal),TiChengTotal=Sum(ProTotal)*IsNull(b.TiCheng/100,0) '
+' from FWXHH a left join tUser b on a.YWCode=b.UserCode '
+'where CheckType=''3'' and WriteDate Between '''+@sDate+''' and '''+ @eDate+''''
if @YWCode<>''
  Set @Sql=@Sql+' and YWCode='''+@YWCode+''''
Set @Sql=@Sql+' Group by YWCode,b.UserName,TiCheng '
Exec (@Sql)

Set @Sql='insert into #RptMaster '
+' Select YWCode,b.UserName,ProTotal=Sum(ProTotal),TiChengTotal=Sum(ProTotal)*IsNull(b.TiCheng/100,0) '
+' from FWTHH a left join tUser b on a.YWCode=b.UserCode '
+' where CheckType=''3'' and WriteDate Between '''+@sDate+''' and '''+ @eDate+''''
if @YWCode<>''
  Set @Sql=@Sql+' and YWCode='''+@YWCode+''''
Set @Sql=@Sql+' Group by YWCode,b.UserName,TiCheng '
Exec (@Sql)

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

Exec (@Sql)

Drop table #RptMaster

 
 
 
