October 24, 2014 at 3:13 am
I have (sort of) the query with derived tables as per below:
SELECT column2,
column3,
column4,
column5
FROM (select columnA, columnB, columnC
FROM Table1
INNER JOIN Table3
INNER JOIN (SELECT columnA1, columnA2)
GROUP BY columnA1, columnA2
LEFT JOIN (SELECT columnB1, columnB2,
SUM(A.columnB3),
SUM(A.columnB4)
FROM (Select columnC1, ColumnC2,
CASE WHEN B.TransDateNumeric BETWEEN 20131001 AND 20131031 THEN QuantityPlanned ELSE 0 END As ColumnB3,
CASE WHEN B.TransDateNumeric BETWEEN 20131101 AND 20131130 THEN QuantityPlanned ELSE 0 END As ColumnB4
FROM Table4
GROUP BY) A
GROUp BY) B
Does anyone know how to store a (variable maybe?) when I hit the case statements? Right at the top there is a column1 that I want to populate, but in order to do that I want to say, if it is between the first dates store as a, and if it is between the other dates store as b.
kind regards
Fred
October 24, 2014 at 3:19 am
No problem - can you post the whole query?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 24, 2014 at 3:47 am
Hi Chris
Thanks in advance.
Down below is the original query. I have made a note in the query of the column that I want to populate.
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
, CASE WHEN Inventory.Qty IS NULL THEN 0.0 ELSE Inventory.Qty END As Onhand--meas
, CASE WHEN OnOrder.SumOnOrder IS NULL THEN 0.0 ELSE OnOrder.SumOnOrder END As OnOrder--meas
, CASE WHEN PlannedRequirement.Period1_starting_20130601 IS NULL
THEN 0.0 ELSE PlannedRequirement.Period1_starting_20130601 END As ForecastMonth1 --meas
, CASE WHEN PlannedRequirement.Period2_starting_20130701 IS NULL
THEN 0.0 ELSE PlannedRequirement.Period2_starting_20130701 END As ForecastMonth2--meas
, CASE WHEN PlannedRequirement.Period3_starting_20130801 IS NULL
THEN 0.0 ELSE PlannedRequirement.Period3_starting_20130801 END As ForecastMonth3--meas
, CASE WHEN PlannedRequirement.Period4_starting_20130901 IS NULL
THEN 0.0 ELSE PlannedRequirement.Period4_starting_20130901 END As ForecastMonth4--meas
, CASE WHEN PlannedRequirement.Period5_starting_20131001 IS NULL
THEN 0.0 ELSE PlannedRequirement.Period5_starting_20131001 END As ForecastMonth5--meas
, CASE WHEN PlannedRequirement.Period6_starting_20131101 IS NULL
THEN 0.0 ELSE PlannedRequirement.Period6_starting_20131101 END As ForecastMonth6--meas
, CASE WHEN PlannedRequirement.Period7_starting_20131201 IS NULL
THEN 0.0 ELSE PlannedRequirement.Period7_starting_20131201 END As ForecastMonth7--meas
, CASE WHEN PlannedRequirement.Period8_starting_20140101 IS NULL
THEN 0.0 ELSE PlannedRequirement.Period8_starting_20140101 END As ForecastMonth8--meas
, CASE WHEN PlannedRequirement.Period9_starting_20150601 IS NULL
THEN 0.0 ELSE PlannedRequirement.Period9_starting_20150601 END As ForecastMonth9--meas
, CASE WHEN PlannedRequirement.Period10_starting_20150701 IS NULL
THEN 0.0 ELSE PlannedRequirement.Period10_starting_20150701 END As ForecastMonth10--meas
, CASE WHEN PlannedRequirement.Period11_starting_20150801 IS NULL
THEN 0.0 ELSE PlannedRequirement.Period11_starting_20150801 END As ForecastMonth11--meas
, CASE WHEN PlannedRequirement.Period12_starting_20150901 IS NULL
THEN 0.0 ELSE PlannedRequirement.Period12_starting_20150901 END As ForecastMonth12--meas
FROM
--1 ITEMS
(Select 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
,CASE WHEN BuyerCodes.PrimaryCode IS NULL THEN '' ELSE BuyerCodes.PrimaryCode END AS BuyerCode
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) <> ''))
GROUP BY 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
,CASE WHEN BuyerCodes.PrimaryCode IS NULL THEN '' ELSE BuyerCodes.PrimaryCode END) ITEMS
---1
---2 INVENTORY
LEFT JOIN
(select LM.KeyDepot,LI.KeyItem, SUM((LI.OpeningBalance + LI.Receipts + LI.Adjustments) - LI.Issues) AS Qty
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) INVENTORY
ON ITEMS.KeyDepot = INVENTORY.KeyDepot
AND ITEMS.KeyItem = INVENTORY.KeyItem
--3 OnOrder
LEFT JOIN
(SELECT keydepot, KeyItem, SUM(HPO_DETAIL.LineOnOrder) AS SumOnOrder, SUM(HPO_DETAIL.VDateDiff) AS DateDiffTotalHPO FROM
-- HPO_DETAIL
(select 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 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%'
group by BOM.KeyBOMDepot, BOM.KeyItemChild) 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%')
group by HPO.keydepot, HPO.KeyItem, HPO.PurchaseOrderNumber, HPO.LineNumber,(quantityOrdered - quantityreceived)
,CASE WHEN (KeyDateRescheduleFromMRP <> KeyDateDue) AND (KeyDateRescheduleFromMRP <> 0) THEN 1 ELSE 0 END) HPO_DETAIL
GROUP BY keydepot, KeyItem) OnOrder
ON OnOrder.keydepot = Items.keydepot
AND OnOrder.KeyItem = Items.KeyItem
---4 PlannedRequirement
LEFT JOIN
(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
FROM
(SELECT KeyDepotPlannedOrder as KeyDepot,KeyItem,PlannedRelease.TransDateNumeric
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20131001 AND 20131031 THEN QuantityPlanned ELSE 0 END As Period1starting20130601
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20131001 AND 20131031 AND PlannedDue.TransDateNumeric<>RescheduleMRP.TransDateNumeric AND RescheduleMRP.TransDateNumeric<>0 THEN 1 ELSE 0 END As Period1DateDiffTotal
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20131101 AND 20131130 THEN QuantityPlanned ELSE 0 END As Period2starting20130701
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20131101 AND 20131130 AND PlannedDue.TransDateNumeric<>RescheduleMRP.TransDateNumeric AND RescheduleMRP.TransDateNumeric<>0 THEN 1 ELSE 0 END As Period2DateDiffTotal
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20131201 AND 20131231 THEN QuantityPlanned ELSE 0 END As Period3starting20130801
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20131201 AND 20131231 AND PlannedDue.TransDateNumeric<>RescheduleMRP.TransDateNumeric AND RescheduleMRP.TransDateNumeric<>0 THEN 1 ELSE 0 END As Period3DateDiffTotal
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20140101 AND 20140131 THEN QuantityPlanned ELSE 0 END As Period4starting20130901
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20140101 AND 20140131 AND PlannedDue.TransDateNumeric<>RescheduleMRP.TransDateNumeric AND RescheduleMRP.TransDateNumeric<>0 THEN 1 ELSE 0 END As Period4DateDiffTotal
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20140201 AND 20140228 THEN QuantityPlanned ELSE 0 END As Period5starting20131001
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20140201 AND 20140228 AND PlannedDue.TransDateNumeric<>RescheduleMRP.TransDateNumeric AND RescheduleMRP.TransDateNumeric<>0 THEN 1 ELSE 0 END As Period5DateDiffTotal
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20140301 AND 20140331 THEN QuantityPlanned ELSE 0 END As Period6starting20131101
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20140301 AND 20140331 AND PlannedDue.TransDateNumeric<>RescheduleMRP.TransDateNumeric AND RescheduleMRP.TransDateNumeric<>0 THEN 1 ELSE 0 END As Period6DateDiffTotal
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20140401 AND 20140430 THEN QuantityPlanned ELSE 0 END As Period7starting20131201
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20140401 AND 20140430 AND PlannedDue.TransDateNumeric<>RescheduleMRP.TransDateNumeric AND RescheduleMRP.TransDateNumeric<>0 THEN 1 ELSE 0 END As Period7DateDiffTotal
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20140501 AND 20140531 THEN QuantityPlanned ELSE 0 END As Period8starting20140101
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20140501 AND 20140531 AND PlannedDue.TransDateNumeric<>RescheduleMRP.TransDateNumeric AND RescheduleMRP.TransDateNumeric<>0 THEN 1 ELSE 0 END As Period8DateDiffTotal
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20140601 AND 20140630 THEN QuantityPlanned ELSE 0 END As Period9starting20150601
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20140601 AND 20140630 AND PlannedDue.TransDateNumeric<>RescheduleMRP.TransDateNumeric AND RescheduleMRP.TransDateNumeric<>0 THEN 1 ELSE 0 END As Period9DateDiffTotal
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20140701 AND 20140731 THEN QuantityPlanned ELSE 0 END As Period10starting20150701
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20140701 AND 20140731 AND PlannedDue.TransDateNumeric<>RescheduleMRP.TransDateNumeric AND RescheduleMRP.TransDateNumeric<>0 THEN 1 ELSE 0 END As Period10DateDiffTotal
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20140801 AND 20140831 THEN QuantityPlanned ELSE 0 END As Period11starting20150801
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20140801 AND 20140831 AND PlannedDue.TransDateNumeric<>RescheduleMRP.TransDateNumeric AND RescheduleMRP.TransDateNumeric<>0 THEN 1 ELSE 0 END As Period11DateDiffTotal
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20140901 AND 20140930 THEN QuantityPlanned ELSE 0 END As Period12starting20150901
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20140901 AND 20140930 AND PlannedDue.TransDateNumeric<>RescheduleMRP.TransDateNumeric AND RescheduleMRP.TransDateNumeric<>0 THEN 1 ELSE 0 END As Period12DateDiffTotal
,CASE WHEN 12<>1 THEN CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20131001 AND 20140930 THEN QuantityPlanned ELSE 0 END ELSE CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20131001 AND 20131031 THEN QuantityPlanned ELSE 0 END END As Total12Periods
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
WHERE KeyCompany = 1 AND BI1_DW_Fact_FirmPlannedOrders.RecordID NOT LIKE '%Z'
GROUP BY KeyDepotPlannedOrder,KeyItem,PlannedRelease.TransDateNumeric
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20131001 AND 20131031 THEN QuantityPlanned ELSE 0 END
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20131001 AND 20131031 AND PlannedDue.TransDateNumeric<>RescheduleMRP.TransDateNumeric AND RescheduleMRP.TransDateNumeric<>0 THEN 1 ELSE 0 END
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20131101 AND 20131130 THEN QuantityPlanned ELSE 0 END
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20131101 AND 20131130 AND PlannedDue.TransDateNumeric<>RescheduleMRP.TransDateNumeric AND RescheduleMRP.TransDateNumeric<>0 THEN 1 ELSE 0 END
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20131201 AND 20131231 THEN QuantityPlanned ELSE 0 END
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20131201 AND 20131231 AND PlannedDue.TransDateNumeric<>RescheduleMRP.TransDateNumeric AND RescheduleMRP.TransDateNumeric<>0 THEN 1 ELSE 0 END
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20140101 AND 20140131 THEN QuantityPlanned ELSE 0 END
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20140101 AND 20140131 AND PlannedDue.TransDateNumeric<>RescheduleMRP.TransDateNumeric AND RescheduleMRP.TransDateNumeric<>0 THEN 1 ELSE 0 END
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20140201 AND 20140228 THEN QuantityPlanned ELSE 0 END
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20140201 AND 20140228 AND PlannedDue.TransDateNumeric<>RescheduleMRP.TransDateNumeric AND RescheduleMRP.TransDateNumeric<>0 THEN 1 ELSE 0 END
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20140301 AND 20140331 THEN QuantityPlanned ELSE 0 END
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20140301 AND 20140331 AND PlannedDue.TransDateNumeric<>RescheduleMRP.TransDateNumeric AND RescheduleMRP.TransDateNumeric<>0 THEN 1 ELSE 0 END
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20140401 AND 20140430 THEN QuantityPlanned ELSE 0 END
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20140401 AND 20140430 AND PlannedDue.TransDateNumeric<>RescheduleMRP.TransDateNumeric AND RescheduleMRP.TransDateNumeric<>0 THEN 1 ELSE 0 END
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20140501 AND 20140531 THEN QuantityPlanned ELSE 0 END
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20140501 AND 20140531 AND PlannedDue.TransDateNumeric<>RescheduleMRP.TransDateNumeric AND RescheduleMRP.TransDateNumeric<>0 THEN 1 ELSE 0 END
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20140601 AND 20140630 THEN QuantityPlanned ELSE 0 END
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20140601 AND 20140630 AND PlannedDue.TransDateNumeric<>RescheduleMRP.TransDateNumeric AND RescheduleMRP.TransDateNumeric<>0 THEN 1 ELSE 0 END
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20140701 AND 20140731 THEN QuantityPlanned ELSE 0 END
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20140701 AND 20140731 AND PlannedDue.TransDateNumeric<>RescheduleMRP.TransDateNumeric AND RescheduleMRP.TransDateNumeric<>0 THEN 1 ELSE 0 END
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20140801 AND 20140831 THEN QuantityPlanned ELSE 0 END
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20140801 AND 20140831 AND PlannedDue.TransDateNumeric<>RescheduleMRP.TransDateNumeric AND RescheduleMRP.TransDateNumeric<>0 THEN 1 ELSE 0 END
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20140901 AND 20140930 THEN QuantityPlanned ELSE 0 END
,CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20140901 AND 20140930 AND PlannedDue.TransDateNumeric<>RescheduleMRP.TransDateNumeric AND RescheduleMRP.TransDateNumeric<>0 THEN 1 ELSE 0 END
,CASE WHEN 12<>1 THEN CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20131001 AND 20140930 THEN QuantityPlanned ELSE 0 END ELSE CASE WHEN PlannedRelease.TransDateNumeric BETWEEN 20131001 AND 20131031 THEN QuantityPlanned ELSE 0 END END
) A
GROUP BY KeyDepot, KeyItem) PlannedRequirement
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
October 24, 2014 at 4:10 am
frdrckmitchell7 (10/24/2014)
Hi ChrisThanks in advance....
No problem. Can you confirm that you are using SQL Server 2008?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 24, 2014 at 4:13 am
Yes I am
October 24, 2014 at 5:21 am
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
------------------------------------------------------------------------------------------------------------------------
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 24, 2014 at 5:33 am
Hi Chris. Yes it works, but had to take the keydate out of the main query as that is part of the:
LEFT JOIN BI1_DW_Dim_CalendarDefinition PlannedRelease (NOLOCK)
on PlannedRelease.keydate = KeyDatePlannedRelease
in there will be the field called KeyDate
it will be PlannedRelease.keydate
But your chances are amazing!!!
October 24, 2014 at 5:45 am
Excellent! Now we can work with a much smaller query than the original. You want to see keydate in the output of the PlannedRequirement query, correct?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 24, 2014 at 5:55 am
Yep. I await with baited breath how you are going to do this. I am studying your code as we type
October 24, 2014 at 6:20 am
Can you describe the result set you get from this query please? I'm interested in the row count compared with the PlannedRequirement query (they should be the same), and any difference between MIN_keydate and MAX_keydate (I'm expecting them to be different for every row).
SELECT
KeyDepot,
KeyItem,
MIN_keydate = MIN(keydate),
MAX_keydate = MAX(keydate)
FROM ( -- A
SELECT DISTINCT
DM.KeyDepot,
fp.KeyItem,
keydate = fp.KeyDatePlannedRelease -- PlannedRelease.keydate
FROM BI1_DW_Fact_FirmPlannedOrders fp (NOLOCK)
INNER JOIN 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 -- ##
WHERE fp.KeyCompany = 1 AND fp.RecordID NOT LIKE '%Z'
) A
GROUP BY KeyDepot, KeyItem
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 24, 2014 at 6:26 am
this query has almost half the rows 3296, whereas the larger query that you wrote had the correct amount of rows: 6810
October 24, 2014 at 6:42 am
Which table contains column "keyitem" as referenced in the original query?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 24, 2014 at 6:45 am
dbo.BI1_view_Dim_ItemMaster IM = keyitem
BI1_DW_Dim_CalendarDefinition = keydate
October 24, 2014 at 7:00 am
frdrckmitchell7 (10/24/2014)
dbo.BI1_view_Dim_ItemMaster IM = keyitemBI1_DW_Dim_CalendarDefinition = keydate
Here's that query again as a reminder. I need to know which table the column "keyitem" comes from:
/*
dbo.BI1_view_Dim_ItemMaster IM = keyitem
BI1_DW_Dim_CalendarDefinition = keydate
*/
--------------------------------------------------------------------------------------
-- 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,
PlannedRelease.keydate
,CASE WHEN x.DateRange = 1 THEN QuantityPlanned ELSE 0 END As Period1starting20130601
,CASE WHEN x.DateRange = 1 AND Condition2 = 1 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 Condition2 = 1 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 Condition2 = 1 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 Condition2 = 1 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 Condition2 = 1 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 Condition2 = 1 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 Condition2 = 1 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 Condition2 = 1 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 Condition2 = 1 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 Condition2 = 1 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 Condition2 = 1 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 Condition2 = 1 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,
Condition2 = CASE WHEN PlannedDue.TransDateNumeric<>RescheduleMRP.TransDateNumeric AND RescheduleMRP.TransDateNumeric<>0 THEN 1 ELSE 0 END
) x
WHERE KeyCompany = 1 AND BI1_DW_Fact_FirmPlannedOrders.RecordID NOT LIKE '%Z'
) A
GROUP BY KeyDepot, KeyItem
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 24, 2014 at 7:07 am
oh, sorry, it comes from: BI1_DW_Fact_FirmPlannedOrders
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply