Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL VB.NET
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.
Posted 15-Nov-12 2:37am
Edited 15-Nov-12 23:53pm
v5
Comments
digimanus at 15-Nov-12 9:10am
   
can you add the part of the procedure that should do this?
Karwa_Vivek at 16-Nov-12 5:14am
   
I have added the Procedure.please see If you can help
digimanus at 16-Nov-12 6:20am
   
why in your function:
@cDate varchar(12),
 
and not
@cDate smalldatetime,
?
Karwa_Vivek at 16-Nov-12 6:23am
   
because i am passing formatted date from Front end as varchar
and then Converting the date in smalldatetime.
digimanus at 16-Nov-12 6:26am
   
found!
 
you call the function with a smalldatetime object in stead of a varchar.
 
Set @Opening =dbo.FN_ItemOpening(cast(@date1 as varchar),@ItemID)
Karwa_Vivek at 16-Nov-12 6:31am
   
I think thats not the matter here.I am getting correct values for Single items.
How can I get data for all the items. the above function and procedure is working fine ,when I am passing itemID .I just want that how i should proceed to get openings for all the items .May there be some synatax mistakes but am asking about the logic

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

You didn't show us the function, so it's hard to say. If you were selecting for a date, I'd say use COALESCE to grab the previous days close, if the open is null.
  Permalink  
Comments
Karwa_Vivek at 16-Nov-12 5:15am
   
Updated my Question With My Function and Procedure .Please see.Thanks

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 406
1 Sergey Alexandrovich Kryukov 309
2 CB Sharma 79
3 RyanDev 75
4 PhilLenoir 70
0 Sergey Alexandrovich Kryukov 6,676
1 OriginalGriff 6,056
2 CPallini 2,473
3 Richard MacCutchan 1,697
4 Abhinav S 1,560


Advertise | Privacy | Mobile
Web03 | 2.8.140821.2 | Last Updated 16 Nov 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100