库存控制行列式 [英] Stock Control Determinant

问题描述

我有一个表格，里面有一个库存，一个库存和(一个库存-一个库存).我希望根据(库存入库-库存出库)产生的收益来计算入库和出库之间的差异.即

I have a table that has a Stock In, Stock Out, and (Stock In - Stock Out). I want the difference between the in and out stocks to be calculated based on the proceeding resulting from (Stock In - Stock Out). I.e

Stock In |Stock Out|(Stock In - Stock Out)|

500      |    50   |    450               |

450      |    100  |    350               |

我遇到的问题是，缺货正在从原始的500进货中扣除.

The problem I'm having is that Stock Out is deducting from original Stock In of 500.

我得到的结果是:

Stock In |Stock Out|(Stock In - Stock Out)|

500      |    50   |    450               |

0        |    100  |    400               |

0        |    150  |    350               |

我希望从他们的差异中扣除.

I want it to be deducted from the result of their differences.

这是我的查询:

SELECT  Product_Code,  SUM(Stock In - Stock Out) AS Stock_Balance
FROM Daily_Stock_Balance
GROUP BY  Product_Code;

这是我从上述查询中得到的结果:

This is the result I'm getting from the above query:

     Stock In |Stock Out|Stock Balance|

     500      |    50   |    450      |

              |    100  |    100      |

              |    150  |   100       |

下面是我的真实数据库表

Below is my real database table

任何帮助将不胜感激.

推荐答案

如何汇总相关列:

SELECT Testx.dispatchdate,
       Testx.stockin,
       Testx.stockout,
       (SELECT SUM(stockin)
        FROM   Testx a
        WHERE  a.dispatchdate <= Testx.dispatchdate) AS SumIn,
       (SELECT SUM(stockout)
        FROM   Testx a
        WHERE  a.dispatchdate <= Testx.dispatchdate) AS SumOut,
       [sumin] - [sumout]                            AS Balance
FROM   Testx
ORDER  BY Testx.dispatchdate; 

这篇关于库存控制行列式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！