Associate unequal number of rows from two tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Associate unequal number of rows from two tables

Author

Topic

atlzbest
Starting Member

7 Posts

Posted - 2010-06-24 : 13:14:40

Hi

I am having trouble putting the following query together
I have a customer's before and after plans in two separate tables. i am trying to associate them together and see which ones were kept/dropped/added. A full outer join isnt working for me, any ideas?

DECLARE @before_table table (CustNum varchar(10), Event_Date datetime, Before_plan varchar(10))
DECLARE @after_table table (CustNum varchar(10), Event_Date datetime, Before_plan varchar(10))

INSERT INTO
@before_table
SELECT '1000','2010-06-01','AAB'
UNION
SELECT '1000','2010-06-01','AAC'
UNION
SELECT '1000','2010-06-01','AAD'
UNION
SELECT '1000','2010-06-02','AAD'
UNION
SELECT '1001','2010-06-02','AAD'

INSERT INTO
@after_table
SELECT '1000','2010-06-01','AAB'
UNION
SELECT '1000','2010-06-01','AAD'
UNION
SELECT '1000','2010-06-02','AAE'
UNION
SELECT '1001','2010-06-02','AAF'

select *
FROM @before_table

select *
FROM @after_table

The output i want needs to look like this

CustNum EVENT_Date B_Plan1 A_Plan1 B_plan2 A_plan2 B_3 A3.....
1000 2010-06-01 AAB AAB AAC NULL AAD AAD
1001 2010-06-02 AAD NULL NULL AAF

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-06-25 : 03:02:07

Diplaying your results sideways will be difficult. If you can overcome/ignore that limitation (business requirement), the following may get you moving. You may also be able to transform the output from below into a sideways layout, either via your front end or via from SQL garnered elsewhere here (search for "rows into columns")

select
b.custnum, b.event_date, b.before_plan, a.before_plan
from @before_table b
left join @after_table a on a.custnum = b.custnum and a.event_date = b.event_date and a.before_plan = b.before_plan
union all
select
a.custnum, a.event_date, null, a.before_plan
from @after_table a
left join @before_table b on b.custnum = a.custnum and b.event_date = a.event_date and b.before_plan = a.before_plan
where b.custnum is null

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-25 : 03:12:44

if you does not have a max number of plan per cust + day, you will need to use Dynamic SQL to do it


; with 
combined_table
as
(
	select	CustNum, Event_Date, plan_code, type,
		rowno = dense_rank() over (partition by CustNum, Event_Date order by plan_code)
	from
	(
	select 	CustNum, Event_Date, plan_code = Before_plan, type = 'B'
	FROM 	@before_table
	union all
	select 	CustNum, Event_Date, plan_code = Before_plan, type = 'A'
	FROM 	@after_table
	) p
)
select	CustNum, Event_Date,
	B_Plan1 = max(case when type = 'B' and rowno = 1 then plan_code end),
	A_Plan1 = max(case when type = 'A' and rowno = 1 then plan_code end),
	B_Plan2 = max(case when type = 'B' and rowno = 2 then plan_code end),
	A_Plan2 = max(case when type = 'A' and rowno = 2 then plan_code end),
	B_Plan3 = max(case when type = 'B' and rowno = 3 then plan_code end),
	A_Plan3 = max(case when type = 'A' and rowno = 3 then plan_code end)
from	combined_table
group by CustNum, Event_Date

KH
[spoiler]Time is always against us[/spoiler]

atlzbest
Starting Member

7 Posts

Posted - 2010-06-25 : 13:03:06

thanks all! this worked great

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources