Colum - SQL Server Forums

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

Colum

Author

Topic

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-06-29 : 23:18:21

Hi,

I have 10 tables. All tables just have 2 columns, Employee Name and Overall Score. The only different with each table is the where statement in which they are sorted with different date ranges. Also, Employee Names could be 1 or more. Is there a way to create 1 table that is arranged by this:

Employee Name Week1 Week2 Week3 Week 4

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-29 : 23:21:11

[code]
select e.EmployeeName, sum(w1.Overall_Score) as Week1, sum(w2.Overall_Score) as Week2
from employee e
left join table_w1 w1 on e.EmployeeName = w1.EmployeeName
left join table_w2 w2 on e.EmployeeName = w2.EmployeeName
group by e.EmployeeName
[/code]

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

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-06-30 : 04:34:26

I'm not sure if that would work. Here's my concern.

I have one database that has 3 columns. These are , Employee Name, Date, Score. I want to make a sorting of the dates. Like Week 1, Week 2 and Week 3. In this case, I want a table that has these columns: Employee Name, Week1Score, Week2Score and Week3Score.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-30 : 04:36:01

why don't you try it out ?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-30 : 04:37:40

please elaborate with some sample data what exactly you're looking for.

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-06-30 : 04:39:41

Base on my issue. How can I create a view that have these columns:

Employee Name, Week1Score, Week2Score and Week3Score.

WeekScore is computed as SUM per sorted dates.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-30 : 04:53:54

quote:
Originally posted by cutiebo2t

Base on my issue. How can I create a view that have these columns:

Employee Name, Week1Score, Week2Score and Week3Score.

WeekScore is computed as SUM per sorted dates.

how is data existing on your tables? can you show some sample?

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-06-30 : 05:54:50

Employee Name Date Score
Employee1 5/12/2008 69
Employee2 6/1/2008 56
Employee1 6/12/2008 75
Employee3 5/27/2008 56
Employee4 5/12/2008 59

etc

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-06-30 : 06:42:55

any help?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-30 : 06:46:08

quote:
Originally posted by cutiebo2t

Employee Name Date Score
Employee1 5/12/2008 69
Employee2 6/1/2008 56
Employee1 6/12/2008 75
Employee3 5/27/2008 56
Employee4 5/12/2008 59

etc

this is the sample for all the 10 supposedly 2 column tables ? How about the other tables ?

And what is your expected output for the sample data ?

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

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-06-30 : 06:55:54

I trim my example into 1 table. I just need to combine 3 where statements into one view that would results:

Employee Name Week1 Week2 Score

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-30 : 07:10:13

[code]
select e.EmployeeName, w1.Score as Week1, w2.Score as Week2
from employee e
left join
(
select EmployeeName, sum(Score) as Score
from week_1
group by EmployeeName
) w1 on e.EmployeeName = w1.EmployeeName
left join
(
select EmployeeName, sum(Score) as Score
from week_2
group by EmployeeName
) w2 on e.EmployeeName = w2.EmployeeName
[/code]

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

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-06-30 : 07:29:47

I'm getting error near by Group By:

SELECT EmployeeName, week1.Score AS Week1, week2.Score AS Week2
FROM dbo.employee2 LEFT OUTER JOIN
(SELECT EmployeeName, SUM(Score) AS Score
FROM dbo.week1
GROUP BY EmployeeName) week1.EmployeeName = week1.EmployeeName LEFT OUTER JOIN
(SELECT EmployeeName, SUM(Score) AS Score
FROM dbo.week2
GROUP BY EmployeeName) week2.EmployeeName = week2.EmployeeName

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-30 : 07:39:34

[code]SELECT EmployeeName,YEAR(Date),MONTH(Date),
SUM(CASE WHEN DATEPART(wk,Date)=1 THEN Score ELSE 0 END) AS Week1Score,
SUM(CASE WHEN DATEPART(wk,Date)=2 THEN Score ELSE 0 END) AS Week2Score,
SUM(CASE WHEN DATEPART(wk,Date)=3 THEN Score ELSE 0 END) AS Week3Score,
SUM(CASE WHEN DATEPART(wk,Date)=4 THEN Score ELSE 0 END) AS Week4Score
FROM
(SELECT EmployeeName,Date,Score
FROM Table1
UNION ALL
SELECT EmployeeName,Date,Score
FROM Table2
UNION ALL
SELECT EmployeeName,Date,Score
FROM Table3
...
UNION ALL
SELECT EmployeeName,Date,Score
FROM Table10)t
GROUP BY EmployeeName,YEAR(Date),MONTH(Date)[/code]

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-06-30 : 07:57:51

I'm not getting this results:

Employee Name Week1 Week2 Score

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-30 : 08:25:16