I am developing a stock management system in vb.net
And facing problem to show the items stocks .OPENING,RECEIVED,ISSUED and CLOSING data for Items.
I have a table tab_itemStock whose structure is below:
ItemId
Date
Opening
Debits --RECEIVED
Credits -- ISSUED
Closing .
The need is that for example A item named PENCIL-itemID-10020
Is Received Quantity = 15.
this will affect the stock as
DATE - 15/NOV/2012
OPENING -0
RECEIVED-15
ISSUED-0
CLOSING-15
Now on next day say on 16/NOV/2012 if the user views the stock and have to issue .
then in the stock report .the opening of that particular Item should show as
DATE -16/NOV/2012
OPENING-15
RECEIVED-0
ISSUED-8
CLOSING-7
Means the Requirement is that the last day Closing should be the Opening for Next Day.
I have written a sql function to achieve this and using this in SQl Procedure to show the report.but am not getting the Required output.
Please help if you have any idea to achieve this.
Below is the Function I wrote For getting the Stock for
Individual Items From The table.But got stuck in showing the
Stocks for all the items in the Report.
Please Guide How to do that.
Create FUNCTION FN_ItemOpening
(
@cDate varchar(12),
@ItemID int
)
Returns int
AS
Begin
DECLARE @Balance int
Declare @date datetime
Set @date = convert(datetime, @cDate)
if @ItemID <> -1 --Individual
BEGIN
SET @Balance = (
SELECT sum(Closing) FROM tab_itemStock cls,
(SELECT ItemID, max([Date]) lastclosingdt FROM tab_itemStock
WHERE [Date] < @Date
group by ItemID) dta
where
cls.ItemID = dta.ItemID
and cls.[date] = dta.lstclosingdt
and dta.ItemID = @ItemID)
END
return isnull(@Balance ,0)
End
And using this Function In a SQL Procedure as :
Create PROCEDURE SP_GetStock
@dt1 varchar(12),
@dt2 varchar(12),
@ItemID int-- =(-1:Any)
AS
declare @date1 smalldatetime
declare @date2 smalldatetime ,@Opening int
set @date2 = convert(smalldatetime, @dt2)
set @date1 = convert(smalldatetime, @dt1)
Set @Opening =dbo.FN_ItemOpening(@date1,@ItemID)
select itm.[ID] [ID],itm.[Name] [Name],itm.UnitAbbr Abbr,Sum(@Opening) opening,Sum(stk.debits) Recvd,Sum(stk.Credits) Issued,
case when Sum(@Opening)+Sum(stk.debits)- Sum(stk.Credits) < 0 then 0 else Sum(@Opening)+Sum(stk.debits)- Sum(stk.Credits) end Closing from tab_item itm ,tab_itemStock stk
where stk.ItemId =itm.[ID] and itm.[ID] =@ItemID
and [Date] between @date1 and @date2
group by itm.[Name],itm.UnitAbbr,itm.[ID] order by itm.[Name]
tab_itemStock is the master table for Items.
Am passing -1 as itemId to get all the items.and for individual passing the item id and fromdate and todate.I think you can able to understand the issue now.