January 8, 2018 at 9:37 am
I was able to get this far from other posts in this forum, but keep getting error . . . invalid column . . Max_Date
The ZATS table often times has the same Composite_Part with different HTS_Num. I need the HTS number from the max Insert_date sent to the Temp table.
SELECT * INTO #TEMP2 -- Max ZATS
from
(
Select
[TSI].[ZATS_BROKER_FEED].[HTS_NUMBER]
,#TEMP1.Part_Num
,row_number() OVER (PARTITION BY Composite_part ORDER BY Insert_Date DESC) AS Max_Date
,Max ([TSI].[ZATS_BROKER_FEED].Insert_Date) as MaxZats
From
#TEMP1
INNER join
[TSI].[ZATS_BROKER_FEED]
on
#TEMP1.Part_Num = [TSI].[ZATS_BROKER_FEED].Composite_Part
Where
[TSI].[ZATS_BROKER_FEED].SUB_ORG= 'FORD'
and Max_Date = 1
GROUP BY
[TSI].[ZATS_BROKER_FEED].HTS_NUMBER
,#TEMP1.Part_Num
) as ZATS
January 8, 2018 at 9:44 am
Column aliases are applied after the rest of the statement has been evaluated. Hence you can't define and use a column alias in the same level of scope.
Put the SELECT that has the row_number into a subquery, and then you'll be able to reference the max_date column in the outer query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 8, 2018 at 9:45 am
Thanks, but not sure how to do a sub query . . 🙁
January 8, 2018 at 9:51 am
jeffshelix - Monday, January 8, 2018 9:45 AMThanks, but not sure how to do a sub query . . 🙁
There's a subquery in the code you posted.
The SELECT * FROM (SELECT...
That inner select is a subquery. So, just add another subquery to define the columns, including the row_number, from whatever table has Composite_part and Insert_Date, have the joins, group by and where in an outer query.
You don't really need the outer subquery that your original code has.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 8, 2018 at 11:37 am
SELECT {column_list, ...}
INTO #TEMP2 -- Max ZATS
from
(
Select
ZBF.[HTS_NUMBER]
,#TEMP1.Part_Num
,row_number() OVER (PARTITION BY Composite_part ORDER BY Insert_Date DESC) AS Max_Date
,Max ([TSI].[ZATS_BROKER_FEED].Insert_Date) as MaxZats
From
#TEMP1
) as TEMP1
INNER join
[TSI].[ZATS_BROKER_FEED] ZBF
on
TEMP1.Part_Num = ZBF.Composite_Part
Where
ZBF..SUB_ORG= 'FORD'
and TEMP1.Max_Date = 1
GROUP BY
ZBF.HTS_NUMBER
,TEMP1.Part_Num
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 9, 2018 at 8:48 am
You're overcomplicating things. You're using two separate methods to find the MAX() date. You only need one.
SELECT
[TSI].[ZATS_BROKER_FEED].[HTS_NUMBER]
, #TEMP1.Part_Num
, 1 -- This will always be 1 based on the filter in the original query.
, Max ([TSI].[ZATS_BROKER_FEED].Insert_Date) as MaxZats
INTO #TEMP2
FROM #TEMP1
INNER JOIN [TSI].[ZATS_BROKER_FEED]
ON #TEMP1.Part_Num = [TSI].[ZATS_BROKER_FEED].Composite_Part
WHERE [TSI].[ZATS_BROKER_FEED].SUB_ORG= 'FORD'
GROUP BY [TSI].[ZATS_BROKER_FEED].HTS_NUMBER
, #TEMP1.Part_Num
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply