Solved

Query and Sum

Posted on 2011-09-06
3
447 Views
Last Modified: 2012-05-12
I am working on developing a query against Microsoft Project Server database.  I want the ability to calculate the amount of hours an individual will be working within a given period of time.  I've developed the code noted below. and it works, for the most part.  

However, as you will see, if a 'resource' has multiple tasks during that week, it will return that resource multiple times.  I was to return only 1 row for each individuals.

Here's what I need:
Resource Name                     Work Sum
V                                                44
W                                                20
M                                                14
B                                                 0

Can someone assist?
SELECT     MSP_EpmAssignment.AssignmentStartDate, MSP_EpmAssignment.AssignmentFinishDate, SUM(MSP_EpmAssignmentByDay_UserView.AssignmentWork) 
                      AS WorkSum, MSP_EpmResource_UserView.ResourceName
FROM         MSP_EpmAssignmentByDay_UserView INNER JOIN
                      MSP_EpmProject_UserView ON MSP_EpmAssignmentByDay_UserView.ProjectUID = MSP_EpmProject_UserView.ProjectUID INNER JOIN
                      MSP_EpmTask_UserView ON MSP_EpmAssignmentByDay_UserView.ProjectUID = MSP_EpmTask_UserView.ProjectUID AND 
                      MSP_EpmAssignmentByDay_UserView.TaskUID = MSP_EpmTask_UserView.TaskUID INNER JOIN
                      MSP_EpmAssignment ON MSP_EpmAssignmentByDay_UserView.AssignmentUID = MSP_EpmAssignment.AssignmentUID AND 
                      MSP_EpmAssignmentByDay_UserView.ProjectUID = MSP_EpmAssignment.ProjectUID AND 
                      MSP_EpmAssignmentByDay_UserView.TaskUID = MSP_EpmAssignment.TaskUID INNER JOIN
                      MSP_EpmResource_UserView ON MSP_EpmAssignment.ResourceUID = MSP_EpmResource_UserView.ResourceUID
GROUP BY MSP_EpmAssignment.AssignmentStartDate, MSP_EpmAssignment.AssignmentFinishDate, MSP_EpmResource_UserView.ResourceName
HAVING      (MSP_EpmAssignment.AssignmentStartDate >= CONVERT(DATETIME, '2011-08-28 00:00:00', 102)) AND 
                      (MSP_EpmAssignment.AssignmentFinishDate <= CONVERT(DATETIME, '2011-09-04 00:00:00', 102)) AND 
                      (NOT (MSP_EpmResource_UserView.ResourceName IS NULL))

Open in new window

Query.jpg
0
Comment
Question by:eciabattari
  • 3
3 Comments
 
LVL 6

Expert Comment

by:markterry
ID: 36489826
Your problem is not the multiple tasks, but rather more clearly, the multiple start and finish dates.

You can build a table of weeks, and join to that table where assignment start and end date are withing that week, and group on week.
0
 
LVL 6

Expert Comment

by:markterry
ID: 36489881
group on week instead of assignment start and end date i mean.

So that might look like

TableWeeks

Week                                       Start            End
2011-09-04 to 2011-09-10      2011-09-04  2011-09-10
etc.


Select Week, Sum(Amount) from tasks
inner join TablesWeek
on tasks.assigment_start  >= Week.Start
and task.assignment_end <= week.end


However, this will be a problem when an assignment lasts longer than a week, so maybe you only want to worry about the week start date only and call it WeekStarted.
0
 
LVL 6

Accepted Solution

by:
markterry earned 500 total points
ID: 36489933
Shoot, I should also add into that, that even if it is just weekstart, you also have to say that assignment start less than weekend, and if it is definitively in a week, then also assignment end must be > week.start.

Only care about week start:
Select Week, Sum(Amount) from tasks
inner join TablesWeek
on tasks.assigment_Start  >= Week.Start
and task.assignment_Start <= week.end

Open in new window


Must lie in a week:
Select Week, Sum(Amount) from tasks
inner join TablesWeek
on tasks.assigment_Start  >= Week.Start
and task.assignment_Start <= week.end
and tasks.assigment_End  >= Week.Start
and task.assignment_End <= week.end

Open in new window

0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Using this function 4 38
Insert msdb.dbo.sysmail_event_log Process_ID into table 4 37
Can I skip a node in XML? 9 29
SQL- GROUP BY 4 21
Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question