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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply