SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 2 table join to return unique records
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wldodds
Starting Member

20 Posts

Posted - 09/05/2013 :  08:43:22  Show Profile  Reply with Quote
I have 2 tables with the following data:

select Year1, PeriodId from TableA
----------------------
Year1, PeriodId
----------------------
2013, 0
2013, 1
2013, 2
2013, 3
2014, 0
2014, 1
2014, 2
2014, 3
2014, 4

select Actindx, Year1, PeriodId, Balance from TableB
-----------------------------------
Actindx, Year1, PeriodId, Balance
-----------------------------------
1, 2013, 0, 32982
1, 2013, 2, 1524
1, 2013, 3, 2897
1, 2014, 1, 457
1, 2014, 4, 3655

What I'm trying to get as a result set is the following:

-------------------------------------
Actindx, Year1, PeriodId, Balance
-------------------------------------
1, 2013, 0, 32982
1, 2013, 1, 32982
1, 2013, 2, 1524
1, 2013, 3, 2897
1, 2014, 0, 2897
1, 2014, 1, 457
1, 2014, 2, 457
1, 2014, 3, 457
1, 2014, 4, 3655

I tried a left outer join but it seems to only return values where periodid and year1 match.

I know this isn't that complicated, any help is greatly appreciated.

Thanks


James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 09/05/2013 :  08:50:06  Show Profile  Reply with Quote
You should do something like shown below. I may not have the conditions in the WHERE clause exactly right. If this does not give you the right answers, try adjusting those conditions to meet the criteria that you are looking for:
SELECT b.actindx,
	a.year1,
	a.periodid,
	b.balance
FROM
	TableA a
	OUTER APPLY
	(
		SELECT TOP (1) actindex, Balance
		FROM TableB b
		WHERE b.year1 = a.year1
		AND b.PeriodId <= a.PeriodId
		ORDER BY PeriodId DESC
	) b;
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000