CREATE PROCEDURE sp_proc1
@fYear int ,
@fMonth  int ,
@fCatID int

AS
begin
 set nocount on
 declare @fTotal int, @fNameInventory varchar(50), @fID int, @fShip int, @fBuy int, @fTest int, @fPast int, @fBalance int, @fUnitName char(10), @fPast1 int, @fPast2 int
 declare @s int, @s1 int, @s2 int

 create table #T1
 (
 fInventoryID int,
 fNameInventory varchar(30),
 fUnitName char(10),
 fPast int,
 fBuy int,
 fTotal int,
 fShip int,
 fBalance int,
 fTest int
 )
  if @fCatID=0
 begin
  declare cur cursor for
  select fID from tblInventory where fID<>8 order by fNameInventory

 end
 else
 begin
  declare cur cursor for
  select fID from tblInventory where fID<>8 and
fCatID=@fCatID order by fNameInventory
  end

 open cur
 fetch next from cur into @fID
 while @@fetch_status=0
  begin
   select @fNameInventory=fNameInventory from tblInventory where fID=@fID
   select @fUnitName=fUnitName from tblInventory where fID=@fID
   /*  กำหนดเงื่อนใข  ยอดยกมาใหม่อีกครั้ง กรณีที่ เอาเืดือน – 1 แล้วกลายเป็น 0 ให้เอาปีลบออก 1 แล้วเดือนกลายเป็น 12   */
   /* ยอดยกมา*/
   if @fMonth – 1 = 0
   begin
    select @fPast1=sum(fNum) from tblInventoryAdd where fYear<@fYear and fInventoryID=@fID and fStatus=”B”
    if @fPast=null
     select @fPast=0
    select @s1=sum(fNum) from tblInventoryAdd where fYear<@fYear and fInventoryID=@fID and fStatus=”S”
    if @s1=null
     select @s1=0
    
    select @fPast=@fPast1-@s1
    if @fPast=null
     select @fPast=0
   end
   else
   begin
    select @fPast1=sum(fNum) from tblInventoryAdd where fYear=@fYear and fMonth<@fMonth and fInventoryID=@fID and fStatus = “B”
    if @fPast1 = null
     select @fPast1=0
    select @fPast2=sum(fNum) from tblInventoryAdd where fYear<@fYear and fInventoryID=@fID and fStatus = “B”
    if @fPast2 = null
     select @fPast2=0
    /* หักออกด้วยจำนวนที่เบิกไป S */
    select @s1=sum(fNum) from tblInventoryAdd where fYear=@fYear and fMonth<@fMonth and fInventoryID=@fID and fStatus = “S”
    if @s1 = null
     select @s1=0
    select @s2=sum(fNum) from tblInventoryAdd where fYear<@fYear and fInventoryID=@fID and fStatus = “S”
    if @s2 = null
     select @s2=0
        
    select @fPast =  @fPast1+@fPast2 – @s1 – @s2
    if @fPast=null
     select @fPast=0

   end 
   /* ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ */  

   /* สั่งซื้อ */
   select @fBuy=sum(fNum) from tblInventoryAdd where  fYear=@fYear and fMonth=@fMonth and   fInventoryID=@fID and fStatus=”B”
   if @fBuy=null
     select @fBuy=0
   
   /* รวมเป็น */
   select @fTotal = @fPast+@fBuy
   if @fTotal=null 
    select @fTotal=0
   
    /* จ่ายไป */
   select @fShip=sum(fNum) from tblInventoryAdd where  fYear=@fYear and fMonth=@fMonth and (fInventoryID=@fID) and fStatus= “S”
   if @fShip=null
   begin
    select @fShip=0
   end

   select @fBalance = @fTotal – @fShip
          
   insert #T1 select @fID, @fNameInventory, @fUnitName, @fPast, @fBuy, @fTotal, @fShip , @fBalance,  @fTest
  fetch next from cur into @fID
  end
 close cur
 deallocate cur /* คืนทรัพยากร*/

 select * from #T1

set nocount off
return 0
end
GO