如何在SQL中维护库存管理逻辑 [英] How to maintain stock management Logic in SQL

问题描述

我正在vb.net中开发库存管理系统
并面临显示商品库存的问题.该商品的打开，接收，发出和关闭数据.
我有一个表格tab_itemStock，其结构如下:
ItemId
日期
开幕
借方-已收款
积分-已发行
关闭.

需要的是例如一个名为PENCIL-itemID-10020
的项目 收到的数量=15.
这会影响库存，因为
日期-2012年11月15日
开幕-0
RECEIVED-15
问题0
CLOSING-15

现在，第二天在2012年11月16日说，如果用户查看库存并必须发行.
然后在库存报告中，该特定物料的期初应显示为
日期-16/NOV/2012
OPENING-15
收到-0
第8期
CLOSING-7

意味着要求是最后一天的收盘应该是第二天的开盘.
我已经编写了一个sql函数来实现此目的，并在SQl Procedure中使用它来显示报告.但是没有获得Required输出.
如果您有任何想法要实现这一目标，请提供帮助.
下面是我编写的用于从表中获取单个物品的库存的函数.但是在显示报告中所有项目的库存时遇到了麻烦.
请指导操作方法.

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

并在SQL过程中以如下方式使用此函数:

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是Items的主表.
我将-1作为itemId传递以获取所有项目.对于个人传递项目ID以及fromdate和todate.我认为您现在可以理解该问题.

解决方案

<您没有向我们展示此功能，所以很难说.如果您要选择日期，我会说如果开放时间为空，请使用COALESCE来获取前几天的收盘价.

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.

解决方案

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.

这篇关于如何在SQL中维护库存管理逻辑的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！