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 Week2from employee e left join table_w1 w1 on e.EmployeeName = w1.EmployeeName left join table_w2 w2 on e.EmployeeName = w2.EmployeeNamegroup 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 ScoreEmployee1 5/12/2008 69Employee2 6/1/2008 56Employee1 6/12/2008 75Employee3 5/27/2008 56Employee4 5/12/2008 59etc |
|
|
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 ScoreEmployee1 5/12/2008 69Employee2 6/1/2008 56Employee1 6/12/2008 75Employee3 5/27/2008 56Employee4 5/12/2008 59etc
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 Week2from 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 Week2FROM 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 Week4ScoreFROM (SELECT EmployeeName,Date,ScoreFROM Table1UNION ALLSELECT EmployeeName,Date,ScoreFROM Table2UNION ALLSELECT EmployeeName,Date,ScoreFROM Table3...UNION ALLSELECT EmployeeName,Date,ScoreFROM Table10)tGROUP 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
|
quote: Originally posted by cutiebo2t I'm not getting this results:Employee Name Week1 Week2 Score
why? what error you're getting? |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-07-01 : 16:17:01
|
quote: Originally posted by cutiebo2t I'm not getting this results:Employee Name Week1 Week2 Score
Thats dirfferent from your original questionMadhivananFailing to plan is Planning to fail |
|
|
|