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.
| Author |
Topic |
|
antvei
Starting Member
3 Posts |
 Posted - 2011-03-31 : 15:27:48
|
| [code]insert into CompletedTable (Resource_Name,Project_Name,Project_Actual, OT, OB, OQ, DRE, PIR, OverAll_Actual_OT)select b.Resource, a.Project, Cost_Actual, OT = Case When Actual_Install_Date <= Projected_Install_Date then 1 else 0 end, OB = case when Cost_Actual <= (Cost_Budget * 1.1) then 1 else 0 end,OQ_Rating,DRE_Rating, PIR_Rating, SUM(Cost_Actual)from PMV37000 a, PMV37010 bwhere a.Work_ID = b.Work_IDgroup by b.Resource, a.Project, a.Cost_Actual,a.Actual_Install_Date, a.Projected_Install_Date, a.Cost_Budget, a.OQ_Rating, a.DRE_Rating, a.PIR_Rating[/code]I am trying to sum the cost_actual for the projects of the employees by each employee. So each employee has multiple projects with a project cost for each project, and I want to sum the project costs for each employee and insert into new table. The sum(cost_actual) that is there now only sums each project, and it actually is multiplied by 2 instead of just what the cost_actual is. I can't figure it out, maybe im going about this whole thing wrong, any help is appreciated, thanks. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-04-01 : 07:26:29
|
| Hard to say why the doubling occurs just with the information you have provided. Usually I have seen that type of doubling (or tripling etc.) when you don't join on enough columns. For example, is there a Project_ID column in the Resource table? Or a Resource_ID column in the project table? Then you may need to join on those as well in addition to the Work_ID's.If you can post the DDL for the two tables and also some sample data, I am sure that many people on this forum would be able to offer very useful suggestions. Brett's description here http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx has info about how to get the DDL for the tables |
 |
|
|
|
|
|
|
|
insert help