Excellent. Next step, can you test this modification of your query please?

--------------------------------------------------------------------------------------

-- Items

--------------------------------------------------------------------------------------

IF OBJECT_ID ('tempdb..#Items') IS NOT NULL DROP TABLE #Items

SELECT DISTINCT

ipcm.KeyDepot,

KeyVendor,

ipcm.KeyItem,

StockingUnitofMeasure,

LeadTime,

LotSize,

ipcm.MinimumBalance,

ipcm.PurchasingBuyerCode

,CASE WHEN BuyerCodes.PrimaryCode IS NULL THEN 'No Buyer Code Linked' ELSE BuyerCodes.Description END AS BuyerDescription

,ISNULL(BuyerCodes.PrimaryCode, '') AS BuyerCode

INTO #Items

FROM BI1_DW_Dim_ItemPlanningCostingMaster ipcm (NOLOCK)

INNER JOIN BI1_DW_Dim_ItemMaster im (NOLOCK)

ON im.KeyItem = ipcm.keyitem

INNER JOIN dbo.BI1_DW_Dim_DepotMaster DM (NOLOCK)

ON DM.KeyDepot = ipcm.keydepot

LEFT JOIN BI1_DW_Dim_VendorMaster vm (NOLOCK)

ON vm.VendorCode = cast(ipcm.PrimaryVendor as varchar(30))

INNER JOIN (SELECT KeyDepot, KeyCompany FROM BI1_DW_Dim_DepotMaster (NOLOCK) WHERE Depotdescription LIKE '%plant%') VDepot

ON VDepot.KEYDEPOT = ipcm.KeyDepot

LEFT JOIN -- ITEMS.BUYER CODES

(SELECT PrimaryCode, Description from BI1_DW_Dim_CodePlanningMaster CPM (NOLOCK) WHERE CPM.TableID = 'BUYER' AND CPM.RecordID = 'CC') BuyerCodes

ON BuyerCodes.PrimaryCode = ipcm.PurchasingBuyerCode

INNER JOIN BI1_DW_Dim_BillOfMaterialsMaster BOM (NOLOCK)

on BOM.KeyItemChild = ipcm.keyitem

AND BOM.KeyBOMDepot = ipcm.keydepot

AND BOM.RecordId NOT LIKE '%Z%'

WHERE VDepot.KeyCompany = '1'

AND ((im.itemtype IN('A', 'B')) OR (im.itemtype = 'G' AND ipcm.RevisionLevel <> ipcm.depotcode and rtrim(ipcm.RevisionLevel) <> ''))

--------------------------------------------------------------------------------------

-- Inventory

--------------------------------------------------------------------------------------

IF OBJECT_ID ('tempdb..#Inventory') IS NOT NULL DROP TABLE #Inventory

SELECT

LM.KeyDepot,

LI.KeyItem,

SUM((LI.OpeningBalance + LI.Receipts + LI.Adjustments) - LI.Issues) AS Qty

INTO #Inventory

FROM BI1_DW_Fact_LocationInventory LI (NOLOCK)

INNER JOIN dbo.BI1_DW_Dim_LocationMaster LM (NOLOCK)

on LM.KeyLocation = LI.KeyLocation

INNER JOIN dbo.BI1_DW_Dim_DepotMaster DM (NOLOCK)

ON DM.KeyDepot = LM.KeyDepot

INNER JOIN (-- BOM ITEMS

SELECT BOM.KeyBOMDepot, BOM.KeyItemChild

FROM BI1_DW_Dim_BillOfMaterialsMaster BOM (NOLOCK)

INNER JOIN dbo.BI1_DW_Dim_DepotMaster DM (NOLOCK)

ON DM.KeyDepot = BOM.KeyBOMDepot

WHERE DM.KeyCompany = 1

AND BOM.RecordId NOT LIKE '%Z%'

GROUP BY BOM.KeyBOMDepot, BOM.KeyItemChild

) BOMItems

ON BOMItems.KeyItemChild = LI.KeyItem

AND BOMItems.KeyBOMDepot = LM.KeyDepot

WHERE KeyCompany = 1

AND LI.RecordId NOT LIKE '%Z%'

GROUP BY LM.KeyDepot,LI.KeyItem

--------------------------------------------------------------------------------------

-- OnOrder

--------------------------------------------------------------------------------------

IF OBJECT_ID ('tempdb..#OnOrder') IS NOT NULL DROP TABLE #OnOrder

SELECT

keydepot, KeyItem,

SUM(HPO_DETAIL.LineOnOrder) AS SumOnOrder,

SUM(HPO_DETAIL.VDateDiff) AS DateDiffTotalHPO

INTO #OnOrder

FROM (-- HPO_DETAIL

SELECT DISTINCT

HPO.keydepot,

HPO.KeyItem,

HPO.PurchaseOrderNumber,

HPO.LineNumber,

(quantityOrdered - quantityreceived) as LineOnOrder

,CASE WHEN (KeyDateRescheduleFromMRP <> KeyDateDue) AND (KeyDateRescheduleFromMRP <> 0) THEN 1 ELSE 0 END As VDateDiff

FROM dbo.BI1_DW_Fact_PurchaseOrderAndRequisitionDetail HPO (NOLOCK)

INNER JOIN dbo.BI1_DW_Dim_DepotMaster DM (NOLOCK)

ON DM.KeyDepot = HPO.KeyDepot

INNER JOIN ( -- BOM ITEMS

SELECT DISTINCT

BOM.KeyBOMDepot, BOM.KeyItemChild

FROM BI1_DW_Dim_BillOfMaterialsMaster BOM (NOLOCK)

INNER JOIN dbo.BI1_DW_Dim_DepotMaster DM (NOLOCK)

ON DM.KeyDepot = BOM.KeyBOMDepot

where KeyCompany = 1

AND (BOM.ItemCodeChild LIKE 'A%' OR BOM.ItemCodeChild LIKE 'B%')

AND BOM.RecordId not LIKE '%Z%'

) BOMItems

ON BOMItems.KeyItemChild = HPO.KeyItem

AND BOMItems.KeyBOMDepot = HPO.keydepot

WHERE keycompany = 1

AND (HPO.quantityOrdered > HPO.quantityreceived)

AND (HPO.RecordID NOT LIKE '%Z')

AND (HPO.RecordID NOT LIKE 'R%')

) HPO_DETAIL

GROUP BY keydepot, KeyItem

--------------------------------------------------------------------------------------

-- PlannedRequirement

--------------------------------------------------------------------------------------

IF OBJECT_ID ('tempdb..#PlannedRequirement') IS NOT NULL DROP TABLE #PlannedRequirement

SELECT KeyDepot, KeyItem

,SUM(A.Period1starting20130601) As Period1_starting_20130601

,SUM(A.Period1DateDiffTotal) As Period1DateDiffTotal

,SUM(A.Period2starting20130701) As Period2_starting_20130701

,SUM(A.Period2DateDiffTotal) As Period2DateDiffTotal

,SUM(A.Period3starting20130801) As Period3_starting_20130801

,SUM(A.Period3DateDiffTotal) As Period3DateDiffTotal

,SUM(A.Period4starting20130901) As Period4_starting_20130901

,SUM(A.Period4DateDiffTotal) As Period4DateDiffTotal

,SUM(A.Period5starting20131001) As Period5_starting_20131001

,SUM(A.Period5DateDiffTotal) As Period5DateDiffTotal

,SUM(A.Period6starting20131101) As Period6_starting_20131101

,SUM(A.Period6DateDiffTotal) As Period6DateDiffTotal

,SUM(A.Period7starting20131201) As Period7_starting_20131201

,SUM(A.Period7DateDiffTotal) As Period7DateDiffTotal

,SUM(A.Period8starting20140101) As Period8_starting_20140101

,SUM(A.Period8DateDiffTotal) As Period8DateDiffTotal

,SUM(A.Period9starting20150601) As Period9_starting_20150601

,SUM(A.Period9DateDiffTotal) As Period9DateDiffTotal

,SUM(A.Period10starting20150701) As Period10_starting_20150701

,SUM(A.Period10DateDiffTotal) As Period10DateDiffTotal

,SUM(A.Period11starting20150801) As Period11_starting_20150801

,SUM(A.Period11DateDiffTotal) As Period11DateDiffTotal

,SUM(A.Period12starting20150901) As Period12_starting_20150901

,SUM(A.Period12DateDiffTotal) As Period12DateDiffTotal

,SUM(Total12Periods) As TotalForAll12Periods

INTO #PlannedRequirement

FROM ( -- A

SELECT DISTINCT

KeyDepotPlannedOrder as KeyDepot,

KeyItem,

PlannedRelease.TransDateNumeric,

x.DateRange

,CASE WHEN x.DateRange = 1 THEN QuantityPlanned ELSE 0 END As Period1starting20130601

,CASE WHEN x.DateRange = 1 AND PlannedDue.TransDateNumeric<>RescheduleMRP.TransDateNumeric AND RescheduleMRP.TransDateNumeric<>0 THEN 1 ELSE 0 END As Period1DateDiffTotal

,CASE WHEN x.DateRange = 2 THEN QuantityPlanned ELSE 0 END As Period2starting20130701

,CASE WHEN x.DateRange = 2 AND PlannedDue.TransDateNumeric<>RescheduleMRP.TransDateNumeric AND RescheduleMRP.TransDateNumeric<>0 THEN 1 ELSE 0 END As Period2DateDiffTotal

,CASE WHEN x.DateRange = 3 THEN QuantityPlanned ELSE 0 END As Period3starting20130801

,CASE WHEN x.DateRange = 3 AND PlannedDue.TransDateNumeric<>RescheduleMRP.TransDateNumeric AND RescheduleMRP.TransDateNumeric<>0 THEN 1 ELSE 0 END As Period3DateDiffTotal

,CASE WHEN x.DateRange = 4 THEN QuantityPlanned ELSE 0 END As Period4starting20130901

,CASE WHEN x.DateRange = 4 AND PlannedDue.TransDateNumeric<>RescheduleMRP.TransDateNumeric AND RescheduleMRP.TransDateNumeric<>0 THEN 1 ELSE 0 END As Period4DateDiffTotal

,CASE WHEN x.DateRange = 5 THEN QuantityPlanned ELSE 0 END As Period5starting20131001

,CASE WHEN x.DateRange = 5 AND PlannedDue.TransDateNumeric<>RescheduleMRP.TransDateNumeric AND RescheduleMRP.TransDateNumeric<>0 THEN 1 ELSE 0 END As Period5DateDiffTotal

,CASE WHEN x.DateRange = 6 THEN QuantityPlanned ELSE 0 END As Period6starting20131101

,CASE WHEN x.DateRange = 6 AND PlannedDue.TransDateNumeric<>RescheduleMRP.TransDateNumeric AND RescheduleMRP.TransDateNumeric<>0 THEN 1 ELSE 0 END As Period6DateDiffTotal

,CASE WHEN x.DateRange = 7 THEN QuantityPlanned ELSE 0 END As Period7starting20131201

,CASE WHEN x.DateRange = 7 AND PlannedDue.TransDateNumeric<>RescheduleMRP.TransDateNumeric AND RescheduleMRP.TransDateNumeric<>0 THEN 1 ELSE 0 END As Period7DateDiffTotal

,CASE WHEN x.DateRange = 8 THEN QuantityPlanned ELSE 0 END As Period8starting20140101

,CASE WHEN x.DateRange = 8 AND PlannedDue.TransDateNumeric<>RescheduleMRP.TransDateNumeric AND RescheduleMRP.TransDateNumeric<>0 THEN 1 ELSE 0 END As Period8DateDiffTotal

,CASE WHEN x.DateRange = 9 THEN QuantityPlanned ELSE 0 END As Period9starting20150601

,CASE WHEN x.DateRange = 9 AND PlannedDue.TransDateNumeric<>RescheduleMRP.TransDateNumeric AND RescheduleMRP.TransDateNumeric<>0 THEN 1 ELSE 0 END As Period9DateDiffTotal

,CASE WHEN x.DateRange = 10 THEN QuantityPlanned ELSE 0 END As Period10starting20150701

,CASE WHEN x.DateRange = 10 AND PlannedDue.TransDateNumeric<>RescheduleMRP.TransDateNumeric AND RescheduleMRP.TransDateNumeric<>0 THEN 1 ELSE 0 END As Period10DateDiffTotal

,CASE WHEN x.DateRange = 11 THEN QuantityPlanned ELSE 0 END As Period11starting20150801

,CASE WHEN x.DateRange = 11 AND PlannedDue.TransDateNumeric<>RescheduleMRP.TransDateNumeric AND RescheduleMRP.TransDateNumeric<>0 THEN 1 ELSE 0 END As Period11DateDiffTotal

,CASE WHEN x.DateRange = 12 THEN QuantityPlanned ELSE 0 END As Period12starting20150901

,CASE WHEN x.DateRange = 12 AND PlannedDue.TransDateNumeric<>RescheduleMRP.TransDateNumeric AND RescheduleMRP.TransDateNumeric<>0 THEN 1 ELSE 0 END As Period12DateDiffTotal

,CASE

WHEN 12<>1 THEN CASE WHEN x.DateRange = 99 THEN QuantityPlanned ELSE 0 END

ELSE CASE WHEN x.DateRange = 1 THEN QuantityPlanned ELSE 0 END

END As Total12Periods -- CJM this doesn't look correct, it should be x.DateRange = 99, the whole range

FROM BI1_DW_Fact_FirmPlannedOrders (NOLOCK)

INNER JOIN dbo.BI1_DW_Dim_DepotMaster DM (NOLOCK)

ON DM.KeyDepot = KeyDepotPlannedOrder

LEFT JOIN BI1_DW_Dim_CalendarDefinition PlannedRelease (NOLOCK)

on PlannedRelease.keydate = KeyDatePlannedRelease

LEFT JOIN BI1_DW_Dim_CalendarDefinition PlannedDue (NOLOCK)

on PlannedDue.keydate = KeyDatePlannedDue

LEFT JOIN BI1_DW_Dim_CalendarDefinition RescheduleMRP (NOLOCK)

on RescheduleMRP.keydate = KeyDateRescheduleMRP

CROSS APPLY (

SELECT DateRange = CASE

WHEN PlannedRelease.TransDateNumeric BETWEEN 20131001 AND 20131031 THEN 1

WHEN PlannedRelease.TransDateNumeric BETWEEN 20131101 AND 20131130 THEN 2

WHEN PlannedRelease.TransDateNumeric BETWEEN 20131201 AND 20131231 THEN 3

WHEN PlannedRelease.TransDateNumeric BETWEEN 20140101 AND 20140131 THEN 4

WHEN PlannedRelease.TransDateNumeric BETWEEN 20140201 AND 20140228 THEN 5

WHEN PlannedRelease.TransDateNumeric BETWEEN 20140301 AND 20140331 THEN 6

WHEN PlannedRelease.TransDateNumeric BETWEEN 20140401 AND 20140430 THEN 7

WHEN PlannedRelease.TransDateNumeric BETWEEN 20140501 AND 20140531 THEN 8

WHEN PlannedRelease.TransDateNumeric BETWEEN 20140601 AND 20140630 THEN 9

WHEN PlannedRelease.TransDateNumeric BETWEEN 20140701 AND 20140731 THEN 10

WHEN PlannedRelease.TransDateNumeric BETWEEN 20140801 AND 20140831 THEN 11

WHEN PlannedRelease.TransDateNumeric BETWEEN 20140901 AND 20140930 THEN 12

WHEN PlannedRelease.TransDateNumeric BETWEEN 20131001 AND 20140930 THEN 99

ELSE NULL END

) x

WHERE KeyCompany = 1 AND BI1_DW_Fact_FirmPlannedOrders.RecordID NOT LIKE '%Z'

) A

GROUP BY KeyDepot, KeyItem

--------------------------------------------------------------------------------------

-- Main query

--------------------------------------------------------------------------------------

SELECT --- this is the field that I want to populate. It is not part of the query as inserting it gives me too many rows. I need to keep

--- the rows the same amount when I populate this field.

keydate,

---from here is the query as is.

DM.KeyDepot

,VM.KeyVendor

,IM.KeyItem

,ItemDescription --take this out when testing done!!!!!!!

, LeadTime --mea

, Items.LotSize as LotSizeUnitsSku --meas -- Linked to 'Units' As UserOption, values = StdCosts

, Items.MinimumBalance As MinStock --meas

, ISNULL(Inventory.Qty, 0.0) AS Onhand--meas

, ISNULL(OnOrder.SumOnOrder, 0.0) As OnOrder--meas

, ISNULL(PlannedRequirement.Period1_starting_20130601, 0.0) As ForecastMonth1--meas

, ISNULL(PlannedRequirement.Period2_starting_20130701, 0.0) As ForecastMonth2--meas

, ISNULL(PlannedRequirement.Period3_starting_20130801, 0.0) As ForecastMonth3--meas

, ISNULL(PlannedRequirement.Period4_starting_20130901, 0.0) As ForecastMonth4--meas

, ISNULL(PlannedRequirement.Period5_starting_20131001, 0.0) As ForecastMonth5--meas

, ISNULL(PlannedRequirement.Period6_starting_20131101, 0.0) As ForecastMonth6--meas

, ISNULL(PlannedRequirement.Period7_starting_20131201, 0.0) As ForecastMonth7--meas

, ISNULL(PlannedRequirement.Period8_starting_20140101, 0.0) As ForecastMonth8--meas

, ISNULL(PlannedRequirement.Period9_starting_20150601, 0.0) As ForecastMonth9--meas

, ISNULL(PlannedRequirement.Period10_starting_20150701, 0.0) As ForecastMonth10--meas

, ISNULL(PlannedRequirement.Period11_starting_20150801, 0.0) As ForecastMonth11--meas

, ISNULL(PlannedRequirement.Period12_starting_20150901, 0.0) As ForecastMonth12--meas

FROM #Items ITEMS ---1

LEFT JOIN #Inventory INVENTORY -- 2

ON ITEMS.KeyDepot = INVENTORY.KeyDepot

AND ITEMS.KeyItem = INVENTORY.KeyItem

LEFT JOIN #OnOrder OnOrder --3

ON OnOrder.keydepot = Items.keydepot

AND OnOrder.KeyItem = Items.KeyItem

LEFT JOIN #PlannedRequirement PlannedRequirement -- 4

ON PlannedRequirement.KeyDepot = Items.KeyDepot

AND PlannedRequirement.KeyItem=Items.KeyItem

LEFT JOIN dbo.BI1_view_Dim_DepotMaster DM (NOLOCK)

ON DM.KeyDepot = Items.KeyDepot

LEFT JOIN dbo.BI1_view_Dim_ItemMaster IM (NOLOCK)

ON IM.KeyItem = Items.KeyItem

LEFT JOIN dbo.BI1_view_Dim_VendorMaster VM (NOLOCK)

ON VM.KeyVendor = Items.KeyVendor

------------------------------------------------------------------------------------------------------------------------