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

Go

Create proc Up_ProdTiCheng2 @BrandCode varchar(6),@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= 'Ïú»õ' ,c.ProdCode,c.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(
  (Case when d.TiCheng>0 then d.TiCheng else IsNull(b.TiCheng,0) end)
  /100,0),a.timeMark
  from FWXHH a join FWXHB c on a.FormNo=c.FormNo
  left join tUser b on a.YWCode=b.UserCode
  left join Product d on c.ProdCode=d.ProdCode
 
  where CheckType= '3'  and IsPresent= '0'  and WriteDate Between  @sDate  and   @eDate 
  and (@YWCode='' or YWCode=+@YWCode)
  and IsNull(d.BrandCode,'')=@BrandCode
  and c.ProTotal-IsNull(c.ThProTotal,0.00)<>0
 
 
Union ALL
  
 Select YWCode,UserName=IsNull(b.UserName,''),a.FormNo,FTypeName= 'ÍË»õ' ,c.ProdCode,c.ProdName, c.CountN 
  ,c.ProTotal ,TiChengTotal= c.ProTotal *IsNull(
 (Case when d.TiCheng>0 then d.TiCheng else IsNull(b.TiCheng,0) end)
 /100,0),a.timeMark
 from FWTHH a join FWTHB c on a.FormNo=c.FormNo
 left join tUser b on a.YWCode=b.UserCode
 left join Product d on c.ProdCode=d.ProdCode
 
 where CheckType= '3'  and IsPresent= '0'  and WriteDate Between  @sDate  and  @eDate 
  and (@YWCode='' or YWCode=@YWCode)
  and IsNull(d.BrandCode,'')=@BrandCode  and IsNull(a.ReferNo,'')=''
   
) as aa Order by timeMark 