Thanks. Can you check this one please? I'm interested in the row count compared with the original, and also the difference between the min and max dates. "The original" in this context means the query in my last post, i.e. just the PlannedRequirement query. Check the rowcount in #PlannedRequirement.
--------------------------------------------------------------------------------------
-- PlannedRequirement (cut down)
--------------------------------------------------------------------------------------
SELECT
KeyDepot,
KeyItem,
MIN_keydate = MIN(keydate),
MAX_keydate = MAX(keydate)
FROM ( -- A
SELECT DISTINCT
fpo.KeyDepotPlannedOrder as KeyDepot,
fpo.KeyItem,
PlannedRelease.keydate
FROM BI1_DW_Fact_FirmPlannedOrders fpo (NOLOCK)
INNER JOIN dbo.BI1_DW_Dim_DepotMaster DM (NOLOCK)
ON DM.KeyDepot = fpo.KeyDepotPlannedOrder
LEFT JOIN BI1_DW_Dim_CalendarDefinition PlannedRelease (NOLOCK)
ON PlannedRelease.keydate = fpo.KeyDatePlannedRelease -- ##
LEFT JOIN BI1_DW_Dim_CalendarDefinition PlannedDue (NOLOCK)
ON PlannedDue.keydate = fpo.KeyDatePlannedDue -- ##
LEFT JOIN BI1_DW_Dim_CalendarDefinition RescheduleMRP (NOLOCK)
ON RescheduleMRP.keydate = fpo.KeyDateRescheduleMRP -- ##
WHERE fpo.KeyCompany = 1
AND fpo.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