March 21, 2017 at 2:56 pm
I have two tables, first table has 1 record per account number but the second table can have more than one record for a given account number. Below is my schema. I want to get max out of second table , at the bottom is my query what i am trying to get to. I know i can throw in a temp table but that would be a lot of data, i am trying to filter on second table
SELECT [CreateDate]
,[AtNumber]
,[HFK]
FROM [TestDB].[dbo].[Table_1]
SELECT [CreateDate]
,[AtNumber]
,[HFK]
FROM [TestDB].[dbo].[Table_2]
--- Something like this
Select a.CreateDate,MAX(b.createDate),DateDiff(m,a.createdate,max(b.createdate) ,a.AtNumber
from Table_1
join Table_2
on a.AtNumber=b.AtNumber
and a.HFK=b.HFK
March 21, 2017 at 3:07 pm
Something like this should do it:
.
SELECT t1.CreateDate, t2.CreateDate, DATEDIFF(MONTH, t1.CreateDate, t2.CreateDate), t1.AtNumber, t1.HFK
FROM Table_1 t1
LEFT OUTER JOIN (
SELECT *, ROW_NUMBER() OVER(PARTITION BY AtNumber, HFK ORDER BY CreateDate DESC) AS row_num
FROM Table_2
) AS t2 ON t2.AtNumber = t1.AtNumber AND t2.HFK = t1.HFK
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".
March 22, 2017 at 5:54 am
There are several ways to do this. I have an older article on Simple-Talk that explains a bunch of them and shows the performance differences between them. Here are some alternatives too.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply