使用SQL計算庫存在站點上的天數

我正在嘗試計算一個專案的庫存在一個站點上的天數。

有兩個表格：Stock表格顯示當前手頭的專案和庫存，Receipts表格顯示站點收到庫存和數量的日期。

我想做一個左外連接來查看 Stock 表中的所有專案，并且只查看 Receipts 表中的行，其中包含仍有剩余庫存的日期。

庫存

| 專案|當前庫存| 價值 |
|-------|-------------|--------|
|刀片 |8 |$40 |
|桌子 |15 |$100 |
|螢屏 |3 |$30 |

收據

| 專案 |收貨日期| 數量|
|-------|------------|---------|
|刀片 |2020 年 1 月 3 日 | 20 |
|刀片 |12/10/2021 | 10 |
|刀片 |2022 年 1 月 5 日 | 5 |
|表 |2020 年 3 月 4 日 | 10 |
|表 |2021 年 5 月 1 日 | 7 |
|表 |2021 年 7 月 10 日 | 5 |
|表 |2021 年 8 月 1 日 | 5 |

日期采用 mm/dd/yyyy 格式。假設這里的當前日期是 2/1/2022。

期望的結果

| 專案|當前庫存| 值|收貨日期|天數|
|-------|-------------|--------|------------|------ -----|
|刀片 |8 |$40 |12/10/2021 |53 |  
|桌子 |15 |$100 |2021 年 5 月 1 日 |276 |
|螢屏 |3 |$30 | | |

邏輯：

| 專案 |收貨日期 | 數量|運行總和|運行總和-當前庫存|
|-------|-------------|---------|-----------|---- ----------------------|
|刀片 |2020 年 1 月 3 日 | 20 |35 |27 |
|刀片 |**12/10/2021**| 10 |15 |7 |
|刀片 |2022 年 1 月 5 日 | 5 |5 |0 |

例如：目前有 8 件刀片庫存。最新收據（2022 年 1 月 5 日）是 5 個單位。因此，從 2021 年 12 月 10 日收貨日期算起，還有 3 個單位。我想查看 (Running Sum-Current Stock) 大于 0 的第一個收貨日期。這是基于 FIFO（先進先出）

提前致謝。

uj5u.com熱心網友回復：

您沒有提到 DBMS 的名稱。我的答案是針對 SQL Server。對于其他 DBMS，您需要更改datediff()功能。

模式和插入陳述句：

 create table Stock(Item    varchar(50), Current_Stock  int, Value int);
 insert into Stock values('Blade'   ,8  ,40);
 insert into Stock values('Table'   ,15 ,100);
 insert into Stock values('Screen'  ,3  ,30);
 
 create table Receipts(Item varchar(50), Receipt_Date date, Quantity int);
 insert into Receipts values('Blade','1/3/2020',    20);
 insert into Receipts values('Blade','12/10/2021',  10);
 insert into Receipts values('Blade','1/5/2022',    5);
 insert into Receipts values('Table','3/4/2020',    10);
 insert into Receipts values('Table','5/1/2021',    7);
 insert into Receipts values('Table','7/10/2021',   5);
 insert into Receipts values('Table','8/1/2021',    5);

詢問：

 with Recepts_with_runningtotal_qty as
 (
    select *, sum(Quantity)over(partition by Item order by Receipt_Date desc) running_total_qty from Receipts
 ),
 current_stock as
 (
    select *, (select max(Receipt_Date) from Recepts_with_runningtotal_qty r where r.running_total_qty>s.current_stock and s.Item=r.Item)Receipt_Date
    from Stock s
 )
 select *,datediff(day, Receipt_Date,'2/1/2022')Age_in_Days from current_stock

輸出：

db<>在這里擺弄

對于 Oracle，您可以使用以下查詢：

 with Recepts_with_runningtotal_qty as
 (
    select Item , Receipt_Date, Quantity, sum(Quantity)over(partition by Item order by Receipt_Date desc) running_total_qty from Receipts
 ),
 current_stock as
 (
    select Item , Current_Stock, Value, (select max(Receipt_Date) from Recepts_with_runningtotal_qty r where r.running_total_qty>s.current_stock and s.Item=r.Item)Receipt_Date
    from Stock s
 )
 select Item, Current_Stock, Value, Receipt_Date,(to_date('1 Feb 2022','DD MM YY')-Receipt_Date)Age_in_Days from current_stock

輸出：

查詢2：不使用公用表運算式

select Item, Current_Stock, Value, Receipt_Date,(to_date('1 Feb 2022','DD MM YY')-Receipt_Date)Age_in_Days
     from
     (
       select Item, Current_Stock, Value, Receipt_Date,Quantity, Running_Total_Qty,
       row_number()over(partition by Item order by Receipt_Date desc)rn
       from
       (
         Select S.Item, S.Current_Stock, S.Value, R.Receipt_date, R.Quantity,
         sum(Quantity)over(partition by R.Item order by Receipt_Date desc) running_total_qty
         From Stock S 
         Left outer join Receipts R On (S.Item = R.Item)
       )
       where Running_Total_Qty>= Current_Stock or Running_Total_Qty is null
     )
     where rn=1

輸出：

db<>在這里擺弄

uj5u.com熱心網友回復：

您可以使用當前日期宣告變數，或者使用 GETDATE() - DECLARE @Today AS DATE SET @Today = GETDATE 或其他日期（如果需要）。

然后，您可以使用 DATEDIFF，如下所示：

SELECT DATEDIFF(day, @Today, Receipt Date) AS date_diff_days

之后只需執行左外連接它應該可以正常作業。玩得開心 ：）

標籤：sql 甲骨文

上一篇：我需要計算每天創建的每個檔案的每天記錄的平均值和大小（以MB為單位）。一整年

下一篇：根據多個欄位計算年齡