title: SQL - partially too high values as result - syntax issue?
body: <p>I have got a resource planning query, which is supposed to show the <strong>actual work, the remaining work and the total work</strong> per resource per fiscal month for the current year.</p>
  
  <p>However, all those rows, where there is remaining work - i.e. in the <strong>present</strong> month or in a <strong>future</strong> month, but <strong>not</strong> in a <strong>past</strong> month - show too high values for work.</p>
  
  <p>I.e. for January 2016 all values (remaining, actual and total work) are correct. From February to December 2016 none of the values are correct.</p>
  
  <p>The too-high values are not multiplied by the same factor and there's not a constant number added to all erroneous values.
  This is why I suspect that there might be some fanout issue (some column multiplying by another or the number of times one item appears) caused by the numerous joins.</p>
  
  <p>I am wondering whether this is a syntax issue or whether I am querying the wrong fields. If you could tell me if there is something in my query, which causes aggregation of sums to be faulty it would be great if you could let me know :)</p>
  
  <hr>
  
  <p><em>Further possibly important information to note:</em></p>
  
  <p>Work is aggregated from projects, assignments and tasks, as the query is designed to show this level of detail as well. However, I have removed this from SELECT and GROUP to narrow the query down a bit down for the purpose of finding the error.</p>
  
  <p>Originally, work is recorded in time and not in fiscal time. Therefore there is a part in the query joining the work time frame to a table, which contains both the normal and the fiscal calendar.
  At that point of the query I had the issue of a 'fanout', which is why I put the separate SELECT statement in the LEFT JOIN. Back then, however, all values (and not just present/future values) were significantly too high.
  The sub-SELECT statement fixed that issue, but there still are erroneous values...</p>
  
  <pre><code>SELECT 
  MSP_EpmResource_UserView.ResourceName,
  totals.[FiscalMemberKeyPeriod] AS FiscalMonth,
  SUM(MSP_EpmAssignmentByDay_UserView.AssignmentWork) AS Work,
  SUM(MSP_EpmAssignmentByDay_UserView.AssignmentActualWork) AS ActualWork,
  SUM(MSP_EpmAssignmentByDay_UserView.AssignmentRemainingWork) AS RemainingWork
  
  FROM MSP_EpmResource_UserView 
       INNER JOIN ((MSP_EpmTask_UserView 
                INNER JOIN MSP_EpmProject_UserView 
            ON MSP_EpmTask_UserView.ProjectUID = MSP_EpmProject_UserView.ProjectUID) 
  
                    INNER JOIN MSP_EpmAssignment ON MSP_EpmTask_UserView.ProjectUID = MSP_EpmAssignment.ProjectUID 
            AND MSP_EpmTask_UserView.TaskUID = MSP_EpmAssignment.TaskUID) 
  
       ON MSP_EpmResource_UserView.ResourceUID = MSP_EpmAssignment.ResourceUID 
       INNER JOIN MSP_EpmAssignmentByDay_UserView 
       ON MSP_EpmAssignment.AssignmentUID = MSP_EpmAssignmentByDay_UserView.AssignmentUID 
      LEFT JOIN (SELECT MSP_TimeByDay_OlapView.CalendarMemberKeyMonth, MSP_TimeByDay_OlapView.FiscalMemberKeyPeriod, MSP_TimeByDay_OlapView.CalendarMemberKeyYear
  FROM MSP_TimeByDay_OlapView
  GROUP BY CalendarMemberKeyMonth, FiscalMemberKeyPeriod, CalendarMemberKeyYear)
  AS totals
  ON MONTH (MSP_EpmAssignmentByDay_UserView.TimeByDay) = totals.CalendarMemberKeyMonth
  
  WHERE (MSP_EpmAssignmentByDay_UserView.TimeByDay BETWEEN '2016-01-01' AND '2016-12-31') 
  AND totals.CalendarMemberKeyYear='2016'
  GROUP BY
  
  MSP_EpmResource_UserView.ResourceName,
  totals.[FiscalMemberKeyPeriod]
  ORDER BY
  MSP_EpmResource_UserView.ResourceName
  </code></pre>