March 5, 2018 at 2:34 pm
This was working, but with a few Duplicate rows from the ZATS table (which i can live with if i have to)
Now i am getting the crazy execution plan with MILLIONS of rows.
Can anyone figure this out?
CTE_1 has a Part_Num and HTS code, and an Entry Date.
I want to pull the HTS_NUM in the row with the Maximum Created_Date from CTE_2 that is prior to the ENTRY_DATE in CTE_1.
I am to the point I will pay someone! LolWith CTE_1 AS (
SELECT
H.CUSTOMS_ENTRY_NUM as [Entry Num]
,L.Entry_Line_ID as Line
,H.[Broker_Invoice]
,H.Entry_Type
,L.PART_NUM
,L.HTS_NUM
,CAST(L.ENTRY_DATE AS DATE) AS [Entry_Date]
,C.[CLASS_VALUE_1] as [P/C_HTS]
,C.[CLASSIFIED_DATE] as [P/C Class Date]
FROM
ADHOC.ATS_ESH H
INNER JOIN ADHOC.ATS_ESL L
ON H.TRANS_SK = L.TRANS_SK
/* Joining on Prod_Class */
LEFT JOIN [TSC].[EMS_TVC_PROD_CLASS] C
ON L.[PART_NUM] = Replace(C.[PROD_ID],'-','') AND C.[CTRY_CODE] = 'US' AND C.CLASS_TYPE = 'HS' and C.BUS_UNIT = 'FORD' AND C.[CLASS_DETAIL_LEVEL] = 'Import_Export'
WHERE H.Importer = 'FORD'
AND H.Entry_Summary_Date > (GetDate () - 30)
AND L.HTS_Num NOT LIKE '98%'
AND L.Part_Num <> 'NPN'
AND H.Com_Desc_Code = 'Parts'
AND Substring(L.Part_Num,4,1) <> 'Z'
AND L.HTS_NUM <> ISNULL(C.[CLASS_VALUE_1], '')
) --SELECT * FROM cte_1 ORDER BY [Entry Num], Line
, CTE_2 AS (
SELECT DISTINCT
cte_1.Part_Num
,CTE_1.Entry_Date
,Z.[COMPOSITE_PART]
,Z.HTS_NUMBER
,MAX(Z.CREATED_DATE) as Zats_Date
--,Z.CREATED_DATE as Zats_Date
FROM [TSI].[ZATS_BROKER_FEED] Z
INNER JOIN CTE_1
ON CTE_1.PART_NUM = Z.COMPOSITE_PART
AND Z.CREATED_DATE <= CTE_1.ENTRY_DATE
WHERE Z.SUB_ORG='FORD'
GROUP BY cte_1.Part_Num,
CTE_1.Entry_Date,
Z.COMPOSITE_PART
,Z.HTS_NUMBER
) --SELECT * FROM CTE_2
Select
[Entry Num]
,Line
,Broker_Invoice
,Entry_Type
,c1.Part_Num
,HTS_Num
,CTE_2.Entry_Date
,CTE_2.[COMPOSITE_PART]
,CTE_2.HTS_NUMBER as Zats_HTS_NUMBER
,MAX(Zats_Date)
--,Zats_Date
From CTE_1 c1
left join CTE_2
on c1.PART_NUM = CTE_2.COMPOSITE_PART
group by [Entry Num]
,Line
,Broker_Invoice
,Entry_Type
,c1.Part_Num
,HTS_Num
,CTE_2.Entry_Date
,CTE_2.[COMPOSITE_PART]
,CTE_2.HTS_NUMBER
thanks
Jeff
March 5, 2018 at 5:09 pm
jeffshelix - Monday, March 5, 2018 2:34 PMThis was working, but with a few Duplicate rows from the ZATS table (which i can live with if i have to)
Now i am getting the crazy execution plan with MILLIONS of rows.
Can anyone figure this out?
CTE_1 has a Part_Num and HTS code, and an Entry Date.
I want to pull the HTS_NUM in the row with the Maximum Created_Date from CTE_2 that is prior to the ENTRY_DATE in CTE_1.I am to the point I will pay someone! Lol
With CTE_1 AS (
SELECT
H.CUSTOMS_ENTRY_NUM as [Entry Num]
,L.Entry_Line_ID as Line
,H.[Broker_Invoice]
,H.Entry_Type
,L.PART_NUM
,L.HTS_NUM
,CAST(L.ENTRY_DATE AS DATE) AS [Entry_Date]
,C.[CLASS_VALUE_1] as [P/C_HTS]
,C.[CLASSIFIED_DATE] as [P/C Class Date]
FROM
ADHOC.ATS_ESH H
INNER JOIN ADHOC.ATS_ESL L
ON H.TRANS_SK = L.TRANS_SK
/* Joining on Prod_Class */
LEFT JOIN [TSC].[EMS_TVC_PROD_CLASS] C
ON L.[PART_NUM] = Replace(C.[PROD_ID],'-','') AND C.[CTRY_CODE] = 'US' AND C.CLASS_TYPE = 'HS' and C.BUS_UNIT = 'FORD' AND C.[CLASS_DETAIL_LEVEL] = 'Import_Export'
WHERE H.Importer = 'FORD'
AND H.Entry_Summary_Date > (GetDate () - 30)
AND L.HTS_Num NOT LIKE '98%'
AND L.Part_Num <> 'NPN'
AND H.Com_Desc_Code = 'Parts'
AND Substring(L.Part_Num,4,1) <> 'Z'
AND L.HTS_NUM <> ISNULL(C.[CLASS_VALUE_1], '')
) --SELECT * FROM cte_1 ORDER BY [Entry Num], Line, CTE_2 AS (
SELECT DISTINCT
cte_1.Part_Num
,CTE_1.Entry_Date
,Z.[COMPOSITE_PART]
,Z.HTS_NUMBER
,MAX(Z.CREATED_DATE) as Zats_Date
--,Z.CREATED_DATE as Zats_Date
FROM [TSI].[ZATS_BROKER_FEED] Z
INNER JOIN CTE_1
ON CTE_1.PART_NUM = Z.COMPOSITE_PART
AND Z.CREATED_DATE <= CTE_1.ENTRY_DATE
WHERE Z.SUB_ORG='FORD'
GROUP BY cte_1.Part_Num,
CTE_1.Entry_Date,
Z.COMPOSITE_PART
,Z.HTS_NUMBER
) --SELECT * FROM CTE_2Select
[Entry Num]
,Line
,Broker_Invoice
,Entry_Type
,c1.Part_Num
,HTS_Num
,CTE_2.Entry_Date
,CTE_2.[COMPOSITE_PART]
,CTE_2.HTS_NUMBER as Zats_HTS_NUMBER
,MAX(Zats_Date)
--,Zats_Date
From CTE_1 c1
left join CTE_2
on c1.PART_NUM = CTE_2.COMPOSITE_PART
group by [Entry Num]
,Line
,Broker_Invoice
,Entry_Type
,c1.Part_Num
,HTS_Num
,CTE_2.Entry_Date
,CTE_2.[COMPOSITE_PART]
,CTE_2.HTS_NUMBER
thanks
Jeff
We could use the DDL (CREATE TABLE statement) for the tables involved, including indexes, and the execution plan (as an *.sqlplan file).
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply