January 31, 2019 at 11:40 am
t.Duty_Saved is an INVALID COLUMN Name in the second Select but not the first.
Odd. Thoughts? thanks
use GTM_ODS;
if object_id('TEMPDB..#tempProto') is not null
drop table #tempProto;
--select * from #tempProto
DECLARE @months table (MonthNum int)
DECLARE @i int =1
WHILE (@i<=12)
BEGIN
INSERT INTO @months(MonthNum)
SELECT @i
SET @i=@i+1
END
--SELECT * FROM @months
select *
into #tempProto
from
(
select
m.MonthNum
,H.Entry_Num
, Version_Reason
, L.Entry_Line_ID as Line
, cast(H.Entry_Summary_Date as date) as Summary
, datepart(MM, H.Entry_Summary_Date) as Month
, L.HTS_Num
, L.HTS_Value
, L.HTS_98_Num
, L.HTS_98_Value
, L.HTS_A_Num
, L.HTS_A_Value
, HTS_99_Num
, L.Part_Num
, D.Duty_Rate as [1_97 Duty_Rate]
, L.HTS_A_Value + L.HTS_98_Value + HTS_Value as Proto_Value
, case
when L.HTS_Num like '9817%'
or L.HTS_A_Num like '9817%'
or L.HTS_98_Num like '9817%' then
'Proto'
end as Proto
, case
when L.HTS_Num like '8703%'
or L.HTS_Num like '8704%' then
'Vehicle'
else
'Part'
end as Veh_Parts
, case
when
(
L.HTS_99_Num = '99038801'
and L.Ctry_Origin = 'CN'
) then
'.25'
when
(
L.HTS_99_Num = '99038802'
and L.Ctry_Origin = 'CN'
) then
'.25'
when
(
L.HTS_99_Num = '99038803'
and L.Ctry_Origin = 'CN'
) then
'.10'
when
(
L.HTS_99_Num = '99038804'
and L.Ctry_Origin = 'CN'
) then
'.10'
else
'0'
end as [Chap 99 Rate]
from
@months as m
left join ADHOC.ATS_ESH H
on m.MonthNum = datepart(MM,H.Entry_Summary_Date)
inner join ADHOC.ATS_ESL L
on H.TRANS_SK = L.TRANS_SK
left join [NAFTAFORD].[ITC_DUTY_RATES] D --chapter 1-97
on left(L.HTS_Num, 8) = D.HTS8
Where H.Importer = 'XXXX'
and H.Entry_Summary_Date >= '1/1/2018'
and H.Entry_Summary_Date < '1/1/2019'
and version_Reason = 'PSC'
and
(
HTS_Num like '9817%'
or HTS_98_Num like '9817%'
or HTS_A_Num like '9817%'
)
) as test;
select distinct
m.MonthNum
,Entry_Num
, Version_Reason
, Line
, Summary
, Month
, HTS_Num
, HTS_Value
, HTS_98_Num
, HTS_98_Value
, HTS_A_Num
, HTS_A_Value
, HTS_99_Num
, [Chap 99 Rate]
, Part_Num
, Proto_Value
, Proto
, Veh_Parts
, [1_97 Duty_Rate]
, Proto_Value * [1_97 Duty_Rate] as [1_97 Duty_Saved]
--,Proto_value * [Chap 99 Rate] as [99 Duty Saved]
, [Chap 99 Rate] + [1_97 Duty_Rate] as [Eff Duty Rate]
, (([Chap 99 Rate] + [1_97 Duty_Rate]) * Proto_Value) as DUTY_SAVED
from
@months as m
left join
#tempProto
on m.MonthNum = Month
--where HTS_Num like ('9817%')
-- or HTS_98_Num like ('9817%')
-- or HTS_A_Num like ('9817%');
select
m.MonthNum
,sum (t.Duty_Saved) -----------------------This is valid in the first Select, but not here
from @months m
left join #tempProto t
on m.MonthNum = t.Month
group by m.MonthNum
January 31, 2019 at 11:49 am
Looks like duty_saved is an alias in the first select and not the second
(([Chap 99 Rate] + [1_97 Duty_Rate]) * Proto_Value) as DUTY_SAVED
in the second try changing
sum (t.Duty_Saved)
to
sum((([Chap 99 Rate] + [1_97 Duty_Rate]) * Proto_Value))
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 31, 2019 at 11:52 am
Solved . .. i was calculating DUTY SAVED outside of the temptable
January 31, 2019 at 11:55 am
Thanks Mike!
January 31, 2019 at 2:57 pm
Might be better to just add all the calc'd columns to the underlying table, so that all queries automatically can use those columns:
...
select *
, [1_97 Duty_Saved] AS Proto_Value * [1_97 Duty_Rate]
, [Eff Duty Rate] AS [Chap 99 Rate] + [1_97 Duty_Rate]
, DUTY_SAVED AS (([Chap 99 Rate] + [1_97 Duty_Rate]) * Proto_Value)
into #tempProto
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply