need help urgent - 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

SQL Server 2005 Forums

Transact-SQL (2005)

need help urgent

Author

Topic

krish001
Yak Posting Veteran

61 Posts

Posted - 2009-10-22 : 06:44:48

BEGIN
IF @TeamMember = 'e2eACD21-F783-484D-915B-01EF661F5E3B'
BEGIN

select [BU],ResourceName as TeamMember, sum(capacity)as capacity,sum(AssignmentBaseline0Work) as Planned,
(sum(AssignmentBaseline0Work)/sum(capacity*200))*100 as PerUtilization
from MSP_EPMPROJECT_USERVIEW P,MSP_EPMRESOURCE_USERVIEW R, MSP_EPMASSIGNMENT_USERVIEW A,
MSP_EpmResourceByDay_UserView D
WHERE P.PROJECTUID= A.PROJECTUID AND A.RESOURCEUID= R.RESOURCEUID and R.RESOURCEUID= D.RESOURCEUID
and ResourceName =@TeamMember and
ProjectStartDate>= @FromDate and ProjectFinishdate<= @ToDate and R.RESOURCEUID in (@TeamMember)--and date between FromDate= @FromDate and ToDate= @ToDate

GROUP BY [BU], ResourceName

END

IF @TeamMember <> 'e2eACD21-F783-484D-915B-01EF661F5E3B'
BEGIN

select [BU], ResourceName as TeamMember, sum(capacity)as capacity,sum(AssignmentBaseline0Work) as Planned,
(sum(AssignmentBaseline0Work)/sum(capacity*200))*100 as PerUtilization
from MSP_EPMPROJECT_USERVIEW P,MSP_EPMRESOURCE_USERVIEW R, MSP_EPMASSIGNMENT_USERVIEW A,
MSP_EpmResourceByDay_UserView D
WHERE P.PROJECTUID= A.PROJECTUID AND A.RESOURCEUID= R.RESOURCEUID and R.RESOURCEUID= D.RESOURCEUID
and ResourceName =@TeamMember and
ProjectStartDate>= @FromDate and ProjectFinishdate<= @ToDate ---and RESOURCENAME in (@TeamMember)--and date between FromDate= @FromDate and ToDate= @ToDate

GROUP BY [BU], ResourceName

END
END

by this query iam getting allocation when it is not overall but when i run it for overall it is showing nothing????

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-22 : 07:03:30

What do you mean by overall?

Madhivanan

Failing to plan is Planning to fail