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

Go

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


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

--没有退货的销售数据
 
 Select YWCode,YWName,FormNo,FTypeName,ProdCode,ProdName,CountN,Prototal,TiCheng  
 
from 
(
  Select YWCode,YWName=IsNull(b.UserName,''),a.FormNo,FTypeName='销货',ProdCode,ProdName,CountN=c.CountN-IsNull(c.ThCount,0.00)
 ,ProTotal=c.ProTotal-IsNull(c.THProTotal,0.00),TiCheng =( c.ProTotal-IsNull(c.THProTotal,0.00) )*IsNull(b.TiCheng/100,0),a.TimeMark
  from FWXHH a left join tUser b on a.YWCode=b.UserCode
  join FWXHB c on a.FormNo=c.FormNo
 where CheckType='3' and IsPresent='0' and WriteDate Between @sDate and  @eDate and (@YWCode='' or YWCode=@YWCode)
  and (c.ProTotal-IsNull(c.THProTotal,0.00) )<>0  

Union All
  
  Select YWCode,UserName=IsNull(b.UserName,''),a.FormNo,FTypeName='退货',ProdCode,ProdName,c.CountN
 ,c.ProTotal,TiChengTotal=c.ProTotal*IsNull(b.TiCheng/100,0),a.TimeMark
  from FWTHH a left join tUser b on a.YWCode=b.UserCode
  join FWTHB c on a.FormNo=c.FormNo
 where CheckType='3' and IsPresent='0' and WriteDate Between @sDate and @eDate and (@YWCode='' or YWCode=@YWCode) and IsNull(a.ReferNo,'')=''
) as a order by timeMark