想要基于购买，购买退货，销售，销售退货来填充库存 [英] Want to populate Stock on the base of purchase,purchasereturn,sale,salereturn

问题描述

我有4个数据库表都包含ItemId和Quantity.
这些表是:
1)购买
2)PurchaseReturn
3)销售
4)SaleReturn
5)库存

库存表为空，其中没有数据.
我想在上述表格的基础上填充库存.

我该怎么办?

请在这方面帮助我！

在此先谢谢您！

Hi,

I have 4 database tables all contain ItemId and Quantity.
The tables are:
1) Purchase
2) PurchaseReturn
3) Sale
4) SaleReturn
5) Stock

The stock table is empty has no data in it.
I would like to populate the stock on the base above mentioned tables.

How can I do this?

Please help me in this regard!

Thanks in advance!

推荐答案

您如何做?大概您知道需要在哪里加载数据库的数据，所以我建议您编写一个程序以将其从现在的位置复制到想要的位置.

How do you do it? Presumably you know where the data is that you need to load the database, so I would suggest you write a program to copy it from where it is now to where you want it to be.

首先，您需要将所有要购买或销售的项目(零库存)插入到库存表中.

现在，当您在那时进行购买时，您需要在当时的购买表中插入记录，您必须将购买数量添加到该物料的当前库存数量中并更新库存表.

购买退货时，您必须从该物料的当前库存中扣除该数量，并将该值更新到库存表中.

当您当时处于销售状态时，必须从该物料的当前库存中扣除该数量并将其更新到库存表中.

并且在退货时，必须从该物料的当前库存中添加该数量，并将该值更新到库存表中.

First thing you have to required all the Items which is purchase or sales table insert with stock zero into stock table.

now when you are purchase at that time you are insert record into the purchase table at that time you have to add purchase qty into current stock qty of that item and update stock table.

at the time of the purchase return , you have to deduct that qty from the current stock of that item and update that value into the stock table.

when you are sales at that time you have to deduct that qty from the current stock of that item and update that value into the stock table.

and at the time of the sales return, you have to add that qty from the current stock of that item and update that value into the stock table.

以下SQL语句将产品添加为表中的当前库存值.

The following SQL statement adds the products with the current stock value into the table.

INSERT INTO dbo.Stock SELECT ItemID, SUM(Quantity) AS TotalQuanity
                      FROM (SELECT ItemID, Quantity
                            FROM dbo.Purchase
                            UNION
                            SELECT ItemID, -Quantity
                            FROM dbo.PurchaseReturn
                            UNION
                            SELECT ItemID, -Quantity
                            FROM dbo.Sale
                            UNION
                            SELECT ItemID, Quantity
                            FROM dbo.SaleReturn) AS AllTransactions
                      GROUP BY ItemID

假设所有表中的Quantity值为正.

首先，使用 UNION [ SUM [插入将SUM的结果插入到Stock表中[ ^ ]语句.

This assumes that the Quantity values in all the tables are positive.

First the four tables are put together using an UNION[^]. During this process the purchase returns and sales are made negative, as they will cause the stock to go down.

Next we perform a SUM[^] for each ItemID on the UNION results.

Finally the results of the SUM are inserted into the Stock table using the INSERT[^] statement.

这篇关于想要基于购买，购买退货，销售，销售退货来填充库存的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！