时间戳

首页 > 解决方案 > 计算COGS以完成库存余额报告SQL

sql - 计算COGS以完成库存余额报告SQL

问题描述

我有两个源表如下：采购和销售

               purchase (incoming)
--------------+----------+-------+----------+--------------
 inventory_id | quantity | price |  Amount  |  timestamp 
--------------+----------+-------+----------+--------------
 A            |        1 | $1.00 |   $1.00  |  2020-02-01
 B            |        2 | $1.00 |   $2.00  |  2020-02-02
 C            |        4 | $2.00 |   $8.00  |  2020-02-03
--------------+----------+-------+----------+--------------


               sale (outgoing)
--------------+----------+-------+----------+--------------
 inventory_id | quantity | price |  Revenue |  timestamp
--------------+----------+-------+----------+--------------
 A            |        1 | $3.00 |   $3.00  |  2020-02-04
 B            |        1 | $3.00 |   $3.00  |  2020-02-05
 C            |        2 | $3.00 |   $6.00  |  2020-02-05
 C            |        1 | $3.00 |   $3.00  |  2020-02-07
--------------+----------+-------+----------+--------------

我想要做的是以下格式的余额报告：

               Balance Report (2020-02-02 to 2020-02-05)
--------------+-----------+-------------+-------------+---------------+-----------+------------+---------+------------+
 inventory_id | Begin Qty | Begin Amount| Purchase Qty|Purchase Amount|  Sale Qty | COGS Amount | End Qty | End Amount |
--------------+-----------+-------------+-------------+---------------+-----------+------------+---------+------------+
 A            |         1 |     $1.00   |             |               |     1     |    $1.00   |         |            |
 B            |           |             |      2      |      $2.00    |     1     |    $1.00   |  $1.00  |   $1.00    |
 C            |           |             |      4      |      $8.00    |     2     |    $4.00   |  $2.00  |   $4.00    |
--------------+-----------+-------------+-------------+---------------+-----------+------------+---------+------------+

Note: The last outgoing transaction is out of the Report time range (2-05/02/2020) then not showing on the report.

我目前的解决方案：

问题：

如果采购或销售订单中过去的交易有任何变化，则 COGS 列不会更新。

我需要帮助：

如果过去的交易发生变化，如何更新此列中的值？

请帮我写一个查询来计算余额报告中的销售金额列（移动平均线没问题）

如果您可以提供查询以按 FIFO、LIFO 计算销售金额列，那就太好了。

非常感谢。

标签： sqlsql-server

解决方案

我找到了解决方案：“我需要为每笔交易更新 COGS 单价”

以下是更新 COGS 的主要代码

您可能需要更改某些内容以适应您的情况。

我知道这不是最好的解决方案，但就我而言，没关系。

create procedure update_COGS
as
 update Sale
    set
        Sale.price = b.Aprice
    from
        Sale a
        left join (select code, SUM(quantity) as quantity, sum(amount) as amount, SUM(amount)/nullif(sum(quantity),0) as Aprice from Purchase  group by code) b
        on a.code = b.code

推荐阅读