August 1, 2019 at 6:47 pm
i should get a few thousand rows back, but when i try pull in Duty Rates, i get hundreds of thousands. Thoughts?
;WITH cte_DutyRates AS (
SELECT DISTINCT
ROW_NUMBER() OVER (PARTITION BY Year, HTS8 ORDER BY Duty_Rate DESC) AS RowNum
,Year
,HTS8
,Duty_Rate
FROM NAFTAXXXX.ITC_DUTY_RATES
where Year is not null
)
select top 200
rates.RowNum
,h.Customs_entry_num
,' ' as [Liquidation Date]
,Entry_type
,Port_code as Port
,H.Entry_num
,h.customs_Entry_Num
,h.Importer
,datepart(yyyy,H.Entry_Date) as Year
,h.Entry_date
,Entry_summary_date
,Supplier
,Destination
,Part_num
,Manuf_id
,HTS_num
,left(hts_num,8) as HTS8
,HTS_value
,HTS_98_num
,HTS_98_value
,h.Other_recon_code
,Version_reason
,Line_item_duty
,SPI
,Ctry_export
,PO_qty
, rates.Duty_Rate
,l.HTS_Value * rates.Duty_Rate as [Duty Amount]
,' ' as [liquidation Date]
,' ' as [PSC Date]
, ' ' as [It Within Psc Time Frame]
from ADHOC.ATS_ESH H
inner join ADHOC.ATS_ESL L
on H.TRANS_SK = L.TRANS_SK
LEFT JOIN cte_DutyRates rates
on HTS8 = rates.HTS8
and YEAR = rates.YEAR
and rates.RowNum = 1 --HIGHER IF DUPLICATE RATES EXIST IN SAME YEAR
where H.Importer = 'XXXX'
and H.Entry_Summary_Date > '7/31/2014'
and H.Entry_Summary_Date < '7/31/2019'
and Supplier in (
'BUR4B',
'BPPXA',
'BAGJA',
'GGNAA',
'DU9AC',
'FJVSA',
'FZT2A',
'GJSVA'
)
and SPI is not null
August 1, 2019 at 6:50 pm
i should get a few thousand rows back, but when i try pull in Duty Rates, i get hundreds of thousands. Thoughts?
Thought #1: Your join is not working as you intended. But without seeing DDL and data, can't really tell.
Also, you should get into the habit of qualifying all of your column names with their associated table aliases.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 1, 2019 at 7:00 pm
i did put brackets around the YEAR field.... the tables over [YEAR]
August 1, 2019 at 9:17 pm
i did put brackets around the YEAR field.... the tables over [YEAR]
I think you may have misunderstood me. Look at these columns, for example:
,Entry_summary_date
,Supplier
,Destination
,Part_num
,Manuf_id
,HTS_num
How can anyone look at this code and know which tables these columns come from?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply