I’m working on a query to show our PO releases to land, broken out by year and quarter. I’ve got the innersub and top level built, but when I go to pivot the top level, I get multiple rows for the same part, and I’m not sure why. I want it to show one part number per row, with the quantities summed up by quarter rather than sporadically. Below is what it looks like when I test it. Below that is the query expression.
select
[ReleaseData].[PODetail_PartNum] as [PODetail_PartNum],
[ReleaseData].[PODetail_LineDesc] as [PODetail_LineDesc],
[ReleaseData].[Calculated_Year] as [Calculated_Year],
[ReleaseData].[1] as [ReleaseData_1],
[ReleaseData].[2] as [ReleaseData_2],
[ReleaseData].[3] as [ReleaseData_3],
[ReleaseData].[4] as [ReleaseData_4]
from (select
[POHeader].[OrderDate] as [POHeader_OrderDate],
[PODetail].[PONUM] as [PODetail_PONUM],
[PODetail].[PartNum] as [PODetail_PartNum],
[PODetail].[LineDesc] as [PODetail_LineDesc],
[PODetail].[IUM] as [PODetail_IUM],
[PODetail].[UnitCost] as [PODetail_UnitCost],
[PORel].[OpenRelease] as [PORel_OpenRelease],
[PORel].[PORelNum] as [PORel_PORelNum],
[PORel].[DueDate] as [PORel_DueDate],
[PORel].[XRelQty] as [PORel_XRelQty],
[PORel].[ReceivedQty] as [PORel_ReceivedQty],
(PORel.XRelQty - PORel.ReceivedQty) as [Calculated_RemainingQty],
(RemainingQty * PODetail.UnitCost) as [Calculated_OpenAmount],
[Vendor].[Name] as [Vendor_Name],
(datepart(quarter, PORel.DueDate)) as [Calculated_Quarter],
(datepart(year, PORel.DueDate)) as [Calculated_Year]
from Erp.PODetail as PODetail
inner join Erp.PORel as PORel on
PODetail.Company = PORel.Company
and PODetail.PONUM = PORel.PONum
and PODetail.POLine = PORel.POLine
and ( PORel.OpenRelease = 1 and PORel.Plant = @CurrentPlant )
inner join Erp.POHeader as POHeader on
PODetail.Company = POHeader.Company
and PODetail.PONUM = POHeader.PONum
inner join Erp.Vendor as Vendor on
POHeader.Company = Vendor.Company
and POHeader.VendorNum = Vendor.VendorNum) ReleaseData_src
pivot
(Sum(Calculated_RemainingQty)
for Calculated_Quarter in ([1],[2],[3],[4]) )
as ReleaseData