根據表B中的日期范圍透視一個欄位，即>=表A中的日期范圍

我正在嘗試將一行轉換為基于格式化日期“yyyy-MMM”的列，雖然這在當前月份期間按預期作業，但在未來幾個月內無法正常作業，我無法弄清楚如何解決這個問題并且會非常感謝對此的任何反饋。

這里有一點背景知識：我從供應商匯總表中購買了數量，其中包括有關合同的資訊，例如合同型別、數量、批號、格式為“yyyy-MMM”的創建日期，因為實際日期并不重要這個目的。

剩余數量是來自專案分類帳條目表的另一個欄位，它基本上是根據批號匯總所有型別的條目。

Consumed Quantity 是我試圖根據它消耗的月份進行透視，并且也來自帶有條目型別過濾器的專案分類帳條目

SELECT * 
FROM 
(
SELECT VS.[Vendor Name], VS.[Vendor No_] AS 'Vendor_No', VS.[Date] AS 'Date', 
SUM(VS.Quantity) AS 'Contracted_Quantity',
SUM(CQ.Consumed_Qty*-1) AS 'Consumed_Qty',
SUM(RQ.Remaining_Qty) AS Remaining_Qty,
'Contract Type' = 
CASE 
  WHEN VS.[Contract Type] = 1 THEN 'CONTRACT A'
  WHEN VS.[Contract Type] = 2 THEN 'CONTRACT B'
  ELSE 'OTHERS'
  END 
FROM
(SELECT [Document No_],[Vendor No_],[Lot No_],FORMAT([Date Created], 'yyyy-MMM') AS 'Date'
      ,[Purch_ Contract No_],[Contract Type],[Quantity] FROM [A].[dbo].[Company$Volume Summary]) VS

/*to identify remaining quantity by lot (Lot no. is included in my VS alias statement)*/
LEFT JOIN (SELECT [Lot No_] ,SUM([Remaining Quantity]) AS Remaining_Qty FROM [A].[dbo].[Company$Item Ledger Entry]
  GROUP BY [Lot No_]) RQ on RQ.[Lot No_] = VS.[Lot No_]

/*to identify consumed volume, if consumption is in future month compared to purchase month, it doesn't populate, this is where I believe the problem is*/
LEFT JOIN (SELECT [Lot No_],FORMAT([Posting Date], 'yyyy-MMM') AS 'Date',SUM([Quantity]) AS Consumed_Qty
FROM [A].[dbo].[Company$Item Ledger Entry]
WHERE [Entry Type] = '5' 
GROUP BY [Lot No_], Format([Posting Date], 'yyyy-MMM')) CQ on CQ.[Lot No_] = VS.[Lot No_] and CQ.Date >= VS.[Date]

GROUP BY VS.[Vendor Name], FORMAT(VS.[Date Created],'yyyy-MMM'), PT.[Contract Type], VS.[Vendor No_]
) cs 
PIVOT
(
 SUM(Consumed_Qty)
  for Date in ([2021-Sep], [2021-Oct], [2021-Nov])
  ) pvt

結果（僅部分顯示）：

如您所見，第二條 ABC Technology 記錄顯示了 2021 年 10 月的消費量，這是準確的，但是，根據也在 2021 年 10 月期間購買的合同金額以及該記錄中剩余的金額，消費量需要為 9,373 并且與它在 2021 年 10 月準確顯示的 8,990 相比較短（383）。在這種情況下，2021 年 11 月的 383 實際消耗量正在下降，但是，我的代碼似乎沒有發現這一點。

這也是 Zebra 技術記錄中的確切情況，其余為 0，這意味著它已被完全消耗，但是，因為它們的購買合同日期都在 2021 年 9 月，而消耗量是在未來2021 年 10 月，SQL 腳本無法識別。

我很感激任何幫助解決這個問題的指導，并提前致謝。

uj5u.com熱心網友回復：

您根據 VS.[Date] 進行旋轉，即購買批次的時間，而不是消費時間。最后三列只會顯示在同一個月內購買和消費的商品的數字。

• 嘗試從連接到 CQ 中洗掉日期并根據 CQ.[Date] 進行透視。
• 此外，您沒有共享資料，但 Remaining_Qty 的邏輯看起來不正確。如果您有一列要跟蹤剩余，您可能希望獲得 MIN，而不是 SUM。否則，使用視窗函式從 Contracted_Qty 中減去 Consumed_Qty 的 SUM。

SELECT * 
FROM 
(
SELECT VS.[Vendor Name], VS.[Vendor No_] AS 'Vendor_No', CQ.[Date] AS 'Date',
SUM(VS.Quantity) AS 'Contracted_Quantity',
SUM(CQ.Consumed_Qty*-1) AS 'Consumed_Qty',
SUM(RQ.Remaining_Qty) AS Remaining_Qty,
'Contract Type' = 
CASE 
  WHEN VS.[Contract Type] = 1 THEN 'CONTRACT A'
  WHEN VS.[Contract Type] = 2 THEN 'CONTRACT B'
  ELSE 'OTHERS'
  END 
FROM
(SELECT [Document No_],[Vendor No_],[Lot No_],FORMAT([Date Created], 'yyyy-MMM') AS 'Date'
      ,[Purch_ Contract No_],[Contract Type],[Quantity] FROM [A].[dbo].[Company$Volume Summary]) VS

/*to identify remaining quantity by lot (Lot no. is included in my VS alias statement)*/
LEFT JOIN (SELECT [Lot No_] , MIN([Remaining Quantity]) AS Remaining_Qty FROM [A].[dbo].[Company$Item Ledger Entry]
  GROUP BY [Lot No_]) RQ on RQ.[Lot No_] = VS.[Lot No_]

/*to identify consumed volume, if consumption is in future month compared to purchase month, it doesn't populate, this is where I believe the problem is*/
LEFT JOIN (SELECT [Lot No_],FORMAT([Posting Date], 'yyyy-MMM') AS 'Date',SUM([Quantity]) AS Consumed_Qty
FROM [A].[dbo].[Company$Item Ledger Entry]
WHERE [Entry Type] = '5' 
GROUP BY [Lot No_], Format([Posting Date], 'yyyy-MMM')) CQ on CQ.[Lot No_] = VS.[Lot No_]

GROUP BY VS.[Vendor Name], FORMAT(VS.[Date Created],'yyyy-MMM'), PT.[Contract Type], VS.[Vendor No_]
) cs 
PIVOT
(
 SUM(Consumed_Qty)
  for [Date] in ([2021-Sep], [2021-Oct], [2021-Nov])
  ) pvt

標籤：sql sql-server 查询语句 枢

上一篇：如何將字串格式化為日期sqlite

下一篇：如何使用子查詢查詢兩個表