Closing Stock

I haven't queried SQL since year. Today i got a call from my friend. he was struggling in querying Stock Adjustment thing's. and it's like feels good that even after a gap of one year i am good at sql and able to write sql with the same efficiency. :)

This is what i did to help him

Create Table

  CREATE TABLE [dbo].[MIS_COAL_DTLS](
   [ID] [int] IDENTITY(1,1) NOT NULL,
   [COAL_UNIT1] [decimal](14, 3) NULL,
   [COAL_UNIT2] [decimal](14, 3) NULL,
   [COAL_RCPT] [decimal](14, 3) NULL,
   [CLOSING_STOCK] [decimal](14, 3) NULL,
   [ENT_DATE] [date] NULL,
   [FIN_YEAR] [varchar](50) NULL,
   [CAL_YEAR] [int] NULL
  ) 

Table data

Cursor for getting Closing Stock

  DECLARE @ID int
  DECLARE @COAL_UNIT1 varchar(50)
  declare @COAL_UNIT2 varchar(50)
  declare @COAL_RCPT [decimal](14, 3)
  declare @CLOSING_STOCK  [decimal](14, 3)
  declare @ent_date date
  DECLARE @newclosingstock [decimal](14, 3)
  declare @openingstock [decimal](14, 3)

  DECLARE cur_emp CURSOR
  STATIC FOR 
  SELECT ID,COAL_UNIT1,COAL_UNIT2, COAL_RCPT, CLOSING_STOCK,ent_date from MIS_COAL_DTLS
  OPEN cur_emp
  IF @@CURSOR_ROWS > 0
   BEGIN 
   FETCH NEXT FROM cur_emp INTO @Id, @COAL_UNIT1, @COAL_UNIT2, @COAL_RCPT, @CLOSING_STOCK,@ent_date
   WHILE @@Fetch_status = 0
   BEGIN
   select @openingstock = closing_stock from MIS_COAL_DTLS where ent_date >= dateadd(day,datediff(day,1,@ent_date),0)
    and ent_date < dateadd(day,datediff(day,0,@ent_date),0)
   set @newclosingstock = (@openingstock + @COAL_RCPT) - (cast(@COAL_UNIT1 as decimal(14,3)) + cast(@COAL_UNIT2 as decimal(14,3)));
   if @newclosingstock is not null
       update  MIS_COAL_DTLS set CLOSING_STOCK = isnull(@newclosingstock,0) where id = @ID;
      --print @newclosingstock
   FETCH NEXT FROM cur_emp INTO @Id, @COAL_UNIT1, @COAL_UNIT2, @COAL_RCPT, @CLOSING_STOCK,@ent_date
   END
  END
  CLOSE cur_emp
  DEALLOCATE cur_emp