Summarizing data in SQL / Joining Multiple 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

General SQL Server Forums

New to SQL Server Programming

Summarizing data in SQL / Joining Multiple tables

Author

Topic

clinton_eg
Yak Posting Veteran

60 Posts

Posted - 2011-05-19 : 10:25:05

Hi

I want to know how do i summarise data onto one table, if i have a different table maintaing data for each metric.

Example: for Calls, Audits, Customer Experience etc.

Calls Table has Emp_ID, Emp_Name, Calls
Audit table has Emp_Name, AuditScore
CE table has Name, CE% (Here the Name format is different from Emp_Name)

So how do i get a master table to show

Emp_ID, Emp_Name, Calls, AuditScore, CE%

Thanks

Ewan Gilby

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-19 : 10:42:19

Design fault. You should have emp_id as forign key in Audit and CE tables. Then joining all three tables and getting your required result will be possible by simple query.

--------------------------
http://connectsql.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-05-19 : 10:43:11

[/code]

CREATE VIEW v_Master (
AS
SELECT Driver.Emp_ID, c.Emp_Name, c.Calls, a.AuditScroe, CE.CE%
FROM (Emp_ID FROM Calls UNION SELECT Emp_ID FROM AuditScore UNION SELECT Emp_ID FROM CE) AS Driver
LEFT JOIN Calls c
ON Driver.Emp_ID = c.Emp_ID
LEFT JOIN AuditScore a
ON Driver.Emp_ID = a.Emp_ID
LEFT JOIN CE
ON Driver.Emp_ID = CE.Emp_ID
[/code]
????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

clinton_eg
Yak Posting Veteran

60 Posts

Posted - 2011-05-19 : 11:31:23

Thanks will try this out.. :)

Ewan Gilby

clinton_eg
Yak Posting Veteran

60 Posts

Posted - 2011-05-19 : 11:56:03

Hi Brett

what if i dont have Emp_ID in all tables?
in this case in CE

Ewan Gilby

X002548
Not Just a Number

15586 Posts

Posted - 2011-05-19 : 12:24:17

quote:
Originally posted by clinton_eg

Hi Brett

what if i dont have Emp_ID in all tables?
in this case in CE

Ewan Gilby

I would say that you should put it there

are you going to try and join on name?

good luck with John Smith

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

clinton_eg
Yak Posting Veteran

60 Posts

Posted - 2011-05-23 : 05:11:47

yes you are right, i should not be using names to do a join. Will add the Emp_ID into the tables where it is missing.. Thanks

Ewan Gilby

clinton_eg
Yak Posting Veteran

60 Posts

Posted - 2011-05-27 : 10:56:54

Hi I have come accross a road block. if i want to further summarise data based on date wise or month wise, how do i include this
considering that all my tables have date fields?

Ewan Gilby

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-06-02 : 09:11:46

There are date functions that can help you summarize by day, month, year, etc. Specifically DATEADD and DATEDIFF

Jim

Everyday I learn something that somebody else already knew

clinton_eg
Yak Posting Veteran

60 Posts

Posted - 2011-06-02 : 10:24:43

thanks jim

Ewan Gilby

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources