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 |
|
krish001
Yak Posting Veteran
61 Posts |
 Posted - 2009-10-21 : 13:33:29
|
| SELECT (v.ProjectAuthorName) 'ProjectManagerName', isnull (sum(datediff(day,v.Projectstartdate,v.projectfinishdate)),0) 'CalenderDays', isnull (sum(v.ProjectActualWork)-sum(datediff(day,v.Projectstartdate,v.projectfinishdate)),0) 'AvailableWorkingDays', sum(v.ProjectActualWork) 'ResourceActualWorking' , DBO.FN_MEETING(v.BU,'','','01-01-2009','12-12-2009') 'MEETING', DBO.FN_BENCH(v.BU,'','','01-01-2009','12-12-2009') 'BENCH' FROM MSP_EPMPROJECT_USERVIEW where v.ProjectStartDate >= '01-01-2009' and v.ProjectFinishDate <= '12-12-2009' and v.[Status of Audit]='Completed' group by v.ProjectAuthorName,v.BUBy this query iam getting repeated values of project author names when there is single |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-10-21 : 13:48:59
|
| Since your GROUP BY clause also contains [BU] you will get one for each distinct combination of those two columns.Be One with the OptimizerTG |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-10-21 : 13:49:11
|
| what does DBO.FN_MEETING and other function do?. also you're grouping by both v.ProjectAuthorName & v.BU so duplicates can come if multiple BU values exist for same ProjectAuthorName |
|
|
|
krish001
Yak Posting Veteran
61 Posts |
Posted - 2009-10-21 : 14:06:05
|
quote:
Originally posted by visakh16
what does DBO.FN_MEETING and other function do?. also you're grouping by both v.ProjectAuthorName & v.BU so duplicates can come if multiple BU values exist for same ProjectAuthorName
so how can i remove these duplicates |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-10-21 : 14:13:10
|
| group only by ProjectAuthorName and apply aggregate function over others |
|
|
|
|
|
|
|
|
help on group by