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