做了好久的一个通过条件查询进销存的存储过程

代码

USE   [ DEV_WXT ]
GO
/* ***** Object:  StoredProcedure [dbo].[usp_GetPeriodInOutList]    Script Date: 08/19/2010 17:15:58 ***** */
SET  ANSI_NULLS  ON
GO
SET  QUOTED_IDENTIFIER  ON
GO
ALTER     PROCEDURE   [ dbo ] . [ usp_GetPeriodInOutList ]
(
     @strSiteCode   nvarchar ( 200 )  = '' ,    
     @BeginDate   nvarchar ( 100 ) = '' ,
     @EndDate   nvarchar ( 100 ) = '' ,
     @Brand   nvarchar ( 200 ) = '' ,
     @MacNo   nvarchar ( 200 ) = ''
)

AS


declare   @EndDate1   nvarchar ( 10 )
select   @EndDate1 = convert ( char ( 10 ), dateadd ( day , 1 , @EndDate ), 121 )
-- print @EndDate1



-- -insert trx detail
select  A.sitecode,a.trxno,a.closedate,b.fromsubinv  as  subinv,b.brand,b.itemno,
b.unitcost * b.applyqty  as  InAmount,b.applyqty  as  InQty,
convert ( decimal ( 19 , 2 ), 0 )  as  OutAmount, 0   as  OutQty  into  # temp
from  dbo.SYS_TrxHeadHist a,dbo.SYS_TrxLineHist b
where  a.SiteCode = b.SiteCode   and  a.TrxNo = B.TrxNo  and  a.SiteCode = @strSiteCode
and  a.Status = ' AP '  
and  b.FromSubinv = ''   and  b.ToSubinv <> ''   and  (b.Brand = @Brand   or   @Brand = '' ) 
and  (b.ItemNo = @MacNo   or   @MacNo = '' )
and  a.CloseDate < @EndDate1   AND  b.ApplyQty > 0

insert   into  # temp
select  A.SiteCode,a.TrxNo,a.CloseDate,b.FromSubinv  as  subinv,b.Brand,b.ItemNo,
convert ( decimal ( 19 , 2 ), 0 )  as  InAmount, 0   as  InQty,
b.UnitCost * ( - b.ActualQty)  as  OutAmount, - b.ApplyQty  as  OutQty  
from  dbo.SYS_TrxHeadHist a,dbo.SYS_TrxLineHist b
where  a.SiteCode = b.SiteCode   and  a.TrxNo = B.TrxNo  and  a.SiteCode = @strSiteCode
  AND  a.Status = ' AP '  
and  b.FromSubinv <> ''   and  b.ToSubinv = ''   and  (b.Brand = @Brand   or   @Brand = '' )
and  (b.ItemNo = @MacNo   or   @MacNo = '' )
and  a.CloseDate < @EndDate1   AND  b.ApplyQty < 0






-- -得到期末数量，金额
select  sitecode  AS  SITECODE,brand  as  Brand,itemno  as  MACNO,
convert ( nvarchar ( 10 ), '' )  as  Type, convert ( nvarchar ( 100 ), '' )  as  MACNAME, convert ( nvarchar ( 4000 ), '' )  as  Model,
0   as  BeginQty, convert ( decimal ( 19 , 2 ), 0 )  as  BeginAmount,
0   as  PeriodInQty, convert ( decimal ( 19 , 2 ), 0 )  as  PeriodInAmount,
0   as  PeriodOutQty, convert ( decimal ( 19 , 2 ), 0 )  as  PeriodOutAmount,
sum (InQty) - sum (OutQty)  as  EndQty, sum (InAmount) - sum (OutAmount)  as  EndAmount 
into  #temp_r  from  # temp   group   by  sitecode,brand,itemno


-- 更新期初数量，金额
select  brand,itemno,
sum (InQty) - sum (OutQty)  as  BeginQty, sum (InAmount) - sum (OutAmount)  as  BeginAmount 
into  #temp1  from  # temp   where  closedate < @BeginDate   group   by  brand,itemno

update  #temp_r  set  BeginQty = b.BeginQty,BeginAmount = b.BeginAmount  from  #temp_r a,#temp1 b
where  a.Brand = b.brand  and  a.MACNO = b.itemno

-- 更新本期入库数量，金额
select  brand,itemno,
sum (InQty)  as  InQty, sum (InAmount)  as  InAmount 
into  #temp2  from  # temp   where  closedate >= @BeginDate   group   by  brand,itemno

update  #temp_r  set  PeriodInQty = b.InQty,PeriodInAmount = b.InAmount  from  #temp_r a,#temp2 b
where  a.Brand = b.brand  and  a.MACNO = b.itemno


-- 更新本期出库数量，金额
select  brand,itemno,
sum (OutQty)  as  OutQty, sum (OutAmount)  as  OutAmount 
into  #temp3  from  # temp   where  closedate >= @BeginDate   group   by  brand,itemno

update  #temp_r  set  PeriodOutQty = b.OutQty,PeriodOutAmount = b.OutAmount  from  #temp_r a,#temp3 b
where  a.Brand = b.brand  and  a.MACNO = b.itemno

-- 更新配件类型，料品描述, 型号
update  #temp_r  set  TYPE = b.TYPE,MACNAME = b.ItemDesc,MODEL = dbo.GetModelList(b.sitecode,b.BRAND,b.ItemNo)
  from  #temp_r a,SYS_SiteItem b
WHERE  a.BRAND = b.BRAND  and  a.macno = b.ItemNo

SELECT   *   FROM  #temp_r  order   by  BRAND,MACNO

drop   table  #temp_r,# temp ,#temp1,#temp2,#temp3

 

转载于:https://www.cnblogs.com/dthom/articles/1803727.html