October 24, 2014 at 7:14 am
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
October 24, 2014 at 7:18 am
the row count is: 3296.
October 24, 2014 at 7:20 am
it's the same as in #plannedrequirement
October 24, 2014 at 7:32 am
sorry, forgot about the difference in dates. Here is sample data
(shortened as for space):
KeyDepotKeyItemMIN_keydateMAX_keydate
511031221712541
1075181223412524
6194751221812218
6322101223712541
1580991224712509
624951222712541
6165581221812218
15367241221012503
580271234212523
4363041221912219
4108341221912403
October 24, 2014 at 7:39 am
Thanks.
Here's that same query reduced even more so it's much easier to understand:
--------------------------------------------------------------------------------------
-- PlannedRequirement (cut down even more)
--------------------------------------------------------------------------------------
SELECT
KeyDepot = fpo.KeyDepotPlannedOrder,
fpo.KeyItem,
MIN_keydate = MIN(fpo.KeyDatePlannedRelease),
MAX_keydate = MAX(fpo.KeyDatePlannedRelease)
FROM BI1_DW_Fact_FirmPlannedOrders fpo (NOLOCK)
INNER JOIN dbo.BI1_DW_Dim_DepotMaster DM (NOLOCK)
ON DM.KeyDepot = fpo.KeyDepotPlannedOrder
WHERE fpo.KeyCompany = 1
AND fpo.RecordID NOT LIKE '%Z'
GROUP BY fpo.KeyDepotPlannedOrder, fpo.KeyItem
If you run it, you will see that it returns the same results as the last query.
You can see very clearly now that for each partition of KeyDepot and keyitem, there are a whole load of dates. The query only captures the minimum and maximum for the partition. The gap between them appears to be up to a year or so. 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 7:41 am
yes, that is quite correct
October 24, 2014 at 7:48 am
So now you understand that if you were to include this date column in your result set, it would increase your output rowcount by quite a lot - which was one of your observations in your first post. Now you can see exactly why this is the case.
You're left with three options:
1) Forget about including the keydate in your result set because it's not in the same scope - it's a different level of granularity
2) Include the min and max keydate for each output row, as I showed you in the last few queries - you'd have to propagate it to the outer query
3) capture all of the keydates into a comma-delimited string, which could contain up to a year's worth of dates.
Can I help you with any of these options?
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:52 am
can you assist me with option two, please?
I have to be out of office in the next ten minutes and then should be at home in about three hours, where I will fire up my pc again. I would appreciate that greatly!
THANKS A MILLION FOR ALL THE HELP!!!!
October 24, 2014 at 7:58 am
You're welcome:
--------------------------------------------------------------------------------------
-- 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,
MIN_keydate = MIN(keydate),
MAX_keydate = MAX(keydate)
,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
--------------------------------------------------------------------------------------
-- 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.
PlannedRequirement.MIN_keydate,
PlannedRequirement.MAX_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 28, 2014 at 12:05 am
Hi Chris
Can I, for the last time, just run some idea with you, if you don't mind.
Kind regards
Fred
October 28, 2014 at 2:45 am
Sure, go for it Fred.
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 28, 2014 at 3:47 am
Thanks Chris.
The problem that I have is that in the main query them MIN_keydate and MAX_keydate have null values, which creates a problem for me. Is there any way that we can store some sort of variable(s), temp table to store something that, when between date1 and date2 store this as, say a, or 1. If it was then next date, store b, or 2, like you so nicely built into your cross apply. What I was thinking was to built something in the main query, say, if a, or 1, find the first of that month's date and fill the null values.
Does that make any sense?
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply