none
Stored Procedure for stock balance RRS feed

  • Question

  • e

    Hi all i have created a tables with the flowing columns

    Table Name = Order Details

    Columns

    No , Product_ID ,Product_Type, Order_Quantity ,Order_Date,Buying_Price, Sold_Quantity, Selling_Price 

    my question is how can i calculate update stock balance for each product to now stock level 

    please help me

    thanks 


    Wednesday, January 24, 2018 6:37 AM

All replies

  • With a program. This forum is not to order programs, therefore you have to hire a programmer. 

    Success Cor

    Wednesday, January 24, 2018 7:23 AM
  • Hello,

    In regards to calculate, it's basic math. Suppose I wanted to know if it's time to order something, I would have a field for reordering then check against current stock level.

    SELECT ProductID
          ,ProductName
          ,SupplierID
          ,CategoryID
          ,UnitPrice
          ,UnitsInStock
          ,UnitsOnOrder
          ,ReorderLevel      
    FROM NorthWindAzure.dbo.Products 
    WHERE UnitsInStock < ReorderLevel

    We end up with

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE PROCEDURE dbo.uspSomeName AS
    BEGIN
    SELECT ProductID
          ,ProductName
          ,SupplierID
          ,CategoryID
          ,UnitPrice
          ,UnitsInStock
          ,UnitsOnOrder
          ,ReorderLevel      
    FROM NorthWindAzure.dbo.Products 
    WHERE UnitsInStock < ReorderLevel
    END
    
    GO


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, January 24, 2018 10:25 AM
    Moderator
  • Karen,

    In my perception are you all the time ordering. 

    To do this you have to calculate what they call in my state economical stock which is Units in Stock - Units sold on order + Units bought on order


    Success Cor

    Wednesday, January 24, 2018 10:46 AM
  • thanks  Karen

    my problem is how to calculate stock level from order_quantity - sold_quantity grouped by product_type  from OrdersDetails table which i attached  with my question 

    Wednesday, January 24, 2018 12:27 PM
  • First off this of course is not a VB.NET question and with that should really be asked in a database forum e.g. T-SQL forum if SQL-Server etc. Anyways as I said before it's math along with a GROUP BY clause. 

    Lastly, what have you tried so far (without showing code, just your SQL statement)?


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, January 24, 2018 12:44 PM
    Moderator
  • If it is only about an SP then the guys who know that live here. 

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?forum=transactsql


    Success Cor

    Wednesday, January 24, 2018 5:46 PM