Pivoting a field based on date range in table B that is >= date range in table A-CodePudding

I am trying to pivot a row into columns that are based on formatted dates 'yyyy-MMM' and while this works as expected for a current month period, it fails to work for future months and I cannot figure out how to fix this and would greatly appreciate any feedback on this.

Here's a little bit of background: I have purchased quantity coming from a Vendor summary table that includes information about a contract such as contract type, quantity, lot number, creation date formatted into 'yyyy-MMM' as the actual date does not matter for this purpose.

Remaining Quantity is another field coming from an item ledger entry table which is essentially summing up all kinds of entries based on lot number.

Consumed Quantity is what I am trying to pivot based on the month it was consumed and is also coming from the item ledger entries with an entry type filter

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

RESULT (Only showing partially):

As you can see, the second ABC Technology record shows a consumption in 2021-Oct which is accurate, however, based on that contracted amount purchased also in 2021-Oct, and what is remaining in that record, the consumption needs to be 9,373 and is short (383) compared to the 8,990 it is accurately displaying in 2021-Oct. In this case the 383 actual consumption is falling in 2021-Nov, however, my code doesn't seem to pick that up.

This is the exact case in both Zebra technologies records as well, where the remaining is 0 which would mean it has been entirely consumed, however, since the purchase contract dates for both of them are in 2021-Sep and the consumption is in the future month 2021-Oct, the SQL script doesn't pick that up.

I'd appreciate any guidance to help fix this and thanks in advance.

CodePudding user response：

You are pivoting based on the VS.[Date] which is when the Lot was purchased, not when it was consumed. The last three columns will only show numbers when the items were purchased AND consumed in the SAME month.

Try removing the Date from the join to CQ and pivot based on CQ.[Date].
Also, you didn't share your data but the logic for Remaining_Qty doesn't look right. If you have a column to track remaining, you probably want to get the MIN, not the SUM. Otherwise, use a window function to subtract the SUM of Consumed_Qty from the Contracted_Qty.

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