How to join data - can't figure this one out

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 2005 Forums

Transact-SQL (2005)

How to join data - can't figure this one out

Author

Topic

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2008-05-22 : 16:01:46

Hi -

I have three tables that I am trying to join in order to produce some report output. I have tried numerous ways, but they all don't give the results I need to provide.

Below is the table creation, some inserts for data and the output I am looking for.


CREATE TABLE [SalesAcct]
	(
	[EmpName] [varchar] (100),
	[Acct_Num] [varchar](15),
	[Acct_Name] [varchar] (50)
	
	)


CREATE TABLE [Perf]
	(
	[Acct_Num] [varchar](15),
	[PerfDate] [smalldatetime],
	[Channel] [varchar](25),
	[Medium] [varchar](25),
	[Clicks] [int],
	[Impressions] [int]
	)



CREATE TABLE [Revenue]
	(
	[Acct_Num] [varchar](15),
	[RevenueDate] [smalldatetime],
	[Channel] [varchar](25),
	[Medium] [varchar](25),
	[ServiceLevel] [varchar],
	[TransactionType] [varchar],
	[GrossRev] [decimal](18, 2)
	)

INSERT INTO SalesAcct (EmpName, Acct_Num, Acct_Name)
	VALUES ('BobSmith', '100XYZ', 'My Account')

INSERT INTO Perf (Acct_NUm, PerfDate, Channel, Medium, Clicks, Impressions)
 	VALUES ('100XYZ', '05-11-2008', 'US', 'Net', 240, 500)

INSERT INTO Revenue (Acct_Num, RevenueDate, Channel, Medium, GrossRev)
	VALUES ('100XYZ', '05-11-2008', 'US', 'Net',500.50)
INSERT INTO Revenue (Acct_Num, RevenueDate, Channel, Medium, GrossRev)
	VALUES ('100XYZ', '05-11-2008', 'US', 'Cable',23.75)

What I need to provide to mgmt is two reports...one is rolled up by EmpName and AcctNum and Date, and the other is details like:

Suumary_Acct_By_Day:
EMPName | Acct_Num | Acct_Name | Date Rev $ | Clicks | Impressions
BobSmith| X0000005 | My Account | 2008-05-11 | 524.25 | 240 500

Detail_Acct_By_Day:
EMPName | Acct_Num | Channel | Medium | Date | Rev $ | Clicks | Impressions
BobSmith | X0000005 | US | Net | 2008-05-11 | 500.50 |240 | 500
BobSmith | X0000005 | US | Cable | 2008-05-11 | 23.75 | 0 | 0

My latest query, which doesn't work is:

select S.EmpName, S.acct_num, R.RevenueDate as Date, coalesce(R.Channel, P.channel)as channel,
      coalesce(R.medium, P.medium), GrossRev, coalesce(P.clicks, ''), coalesce(P.impressions, '')
 FROM SalesAcct AS S
 left outer join Revenue as R on S.acct_num = R.acct_num
 left outer join Perf as P on S.acct_num = P.acct_num

Yields two rows...but the Medium duplicates and the Clicks and Impressions also get duplicated when they should be zero.

Any help would be appreciated.

Thank you,
Will

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-05-22 : 16:21:04

Will -- Look into using UNION ALL to union the two transactional tables together. More here:

http://weblogs.sqlteam.com/jeffs/archive/2006/06/19/10270.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2008-05-22 : 17:08:51

Hi Jeff,
This article looks like it might help with the one issue...of rolling up the data, but I am not sure if it will work for the other requirement (to have a row for each channel/medium/ transcation.

i'll check it out some more.

thanks
- will

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-05-22 : 19:33:44

dhw -- it works for both. You use the UNION ALL to create one big transactional result set with the union of your two tables. The UNION result has all the data you need for your reports. I would even create this UNION as permanent View in your database so you can query the View any time you need:

create view PerfAndRevenue
as
select Acct_NUm, PerfDate, Channel, Medium, Clicks, Impressions, 0 as GrossRev
from Perf
union all
select Acct_Num, RevenueDate, Channel, Medium, 0 as Clicks, 0 as Impressions, GrossRev
from Revenue

With that view created, you can now just query the view joined to your SalesAcct table with a GROUP BY on what you need for each report, and doing a SUM() of the Clicks, Impressions and GrossRev columns. Let me know if you need any further assistance.

- Jeff
http://weblogs.sqlteam.com/JeffS

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2008-05-26 : 12:53:34

Thanks Jeff. I'll try your idea out...it looks like it will solve my issue after all.

- will

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources