Trying to create a BAQ that will show the first 3 forecast dates and the forecast qty per date in a single row per part number
I can PIVOT the forecast info by using a row number and MIN(forecast date), but for the life of me I am at a loss how to include the qty info…
Columns I want: PN, Qty1, Date1, Qty2, Date2, Qty3, Date3
What I have so far…
select
[SubQuery2].[Forecast_PartNum] as [Forecast_PartNum],
[SubQuery2].[1] as [SubQuery2_1],
[SubQuery2].[2] as [SubQuery2_2],
[SubQuery2].[3] as [SubQuery2_3]
from (select
[Forecast].[PartNum] as [Forecast_PartNum],
[Forecast].[ForeQty] as [Forecast_ForeQty],
[Forecast].[ForeDate] as [Forecast_ForeDate],
(ROW_NUMBER() OVER(PARTITION BY Forecast.PartNum ORDER BY Forecast.ForeDate ASC)) as [Calculated_RowNum]
from Erp.Forecast as Forecast
where (Forecast.PartNum like ‘xyz’)) SubQuery2_src
PIVOT
(
min( Forecast_ForeDate )
FOR Calculated_RowNum in ([1],[ 2],[ 3]) )
as SubQuery2