Derived table query

  • 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

  • No problem - can you post the whole query?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • 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 DateDiffTotalHPOFROM

    -- 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

  • frdrckmitchell7 (10/24/2014)


    Hi Chris

    Thanks in advance....

    No problem. Can you confirm that you are using SQL Server 2008?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • Yes I 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

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

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • 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!!!

  • 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?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • Yep. I await with baited breath how you are going to do this. I am studying your code as we type

  • 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

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • this query has almost half the rows 3296, whereas the larger query that you wrote had the correct amount of rows: 6810

  • Which table contains column "keyitem" as referenced in the original query?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • dbo.BI1_view_Dim_ItemMaster IM = keyitem

    BI1_DW_Dim_CalendarDefinition = keydate

  • frdrckmitchell7 (10/24/2014)


    dbo.BI1_view_Dim_ItemMaster IM = keyitem

    BI1_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

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • 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