企业资产存储中库存的SQL计数

Question

我正在为我工作的企业设计一个资产管理系统。他们在家庭环境中雇佣员工。

我已经开始了一个数据库设计，但我不确定如何获得当前库存的数字。例如，我知道描述=“鼠标”的资产总数为(3)，但是1被发送给John，1最初被发送给Jane Doe，但当它被破坏时，它被返回。所以我还有一只老鼠要寄给简。

将使用什么SQL查询，以便获得当前存储的“鼠标”资产的总量？

我把它看作

总库存(鼠标)减去出货量(鼠标)+(鼠标)返回的发货量(鼠标)，其中返回= "1“减去已退役的股票(鼠标)= "1”

我现在在这里：

Tables:

Employees
ID (Primary key, incremental)
F_Name
L_Name
Address
Phone_Number

+----+---------+----------+-----------------+--------------------------
| ID |  F_Name |  L_Name  | Address         |  Phone_Number
| 122  John      Smith      123 anywhere...    111-222-3333
| 123  John      Doe        128 somewhere...   222-333-4444
| 135  Jane      Doe        128 somewhere...   222-333-4444

Shipments_Outbound
ID (Primary key, incremental)
Employee_ID (Foreign key, references Employees(ID))
Sent (date value for date left warehouse)
Courier_ID

+----+--------------+------------+---------------------------------
| ID |  Employee_ID |  Sent      | Courier_ID
| 001   123           2016-03-12   2223334445
| 002   135           2017-03-12   3334445556


Contents_Outbound (many to many composite from Shipments_Outbound and Assets)
Shipment_ID (Primary key, references Shipments_Outbound(ID))
Asset_ID (Primary key, references Assets(ID)

+------------+-----------
| Shipment_ID|  Asset_ID 
| 001            001
| 001            004
| 001            005
| 002            002
| 002            003
| 002            006

Assets
ID (Primary key, incremental)
SKU (unique)
Description
Decommissioned (tinyint value for discarded equipment)
Comments (to note why discarded)

+----+---------+---------------+-----------------+-------------
| ID |  SKU    |  Description  | Decommissioned  | Comments
|001   123456    Monitor          0
|002   987654    Monitor          0          
|003   456789    Desktop          0
|004   NULL      Mouse            0
|005   NULL      Keyboard         0
|006   NULL      Mouse            1                 Broken scroll wheel
|007   NULL      Mouse            0

Shipments_Return
ID (Primary key, incremental)
Employee_ID (Foreign key, references (Employees(ID))
Issued (Date value for when return waybill issued for employee)
Courier_ID
Returned (tinyint for boolean on if product was returned)

+----+---------+---------------+-----------------+-------------
| ID |  Employee_Id |  Issued  | Courier_ID  | Returned
|222     123          2018-01-12  9998887776    0
|223     135          2018-01-13  8887776665    1

Contents_Return (many to many composite from Shipments_Return and Assets)
ShipmentR_ID (Primary key, references Shipments_Return(ID))
Asset_ID (Primary key, references Assets(ID)

+-------------+-----------
| ShipmentR_ID|  Asset_ID 
| 222            001
| 222            004
| 223            002
| 223            006

Answer 1

你可以这样做你的查询。但是，您可能会遇到其他情况下的问题。

您有一个小部件。您的查询会说Instock说总量为1 -发货量(0) +返回(0) -退役(0) =1。

你把它运到工人那里。Instock说，总计1出货量1+返回0 -退役的0=0。很好

如果工人的房子被抢劫，小部件消失了，会发生什么情况？或者他们着火了。或者小部件一开始就不会到达工人手中。如果您只是解除它，您将得到总计1发送1+返回0-退役1= -1。如果你不解除它，那么据你所知，它总是在工人的地方，这是不正确的。你不会有回报的，他们也回不来。你得伪造一份报税单才能得到正确的数字。

您需要在每一步都有一个忽略退役资产的查询，例如

total stock(Mouse) where decommissioned = 0
minus shipments out with (Mouse) where decommissioned = 0
plus return shipments with (Mouse) where returned = "1" and decommissioned = 0

或者，您可以在资产上添加一个status列，将其链接到一个状态表，其值为"In Stock“、"In Use”、“Use”。在创建时，资产将被设置为“股票”。装运完成后，货物中的所有资产都将改为“正在使用”。在返回(当返回设置为1)时，返回中的所有资产都将更改为"In Stock“。在退役时，将状态设置为退役，等等。如果您决定需要其他状态值，可以添加其他状态值，如“修复”、“返回”。

那么您在上面的查询应该是这样的

Total(Mouse) where Status = <in-stock status>