SQL extract month from date column and reuse in JOIN command
<p>I have an SQL query (Microsoft SQL server) in which I am extracting a month from a date column. I would like to reuse this extracted month in a join command to use it as a criterion to join the result with one more column from another table.</p>

<p>The background is that in the database calendar time is used, but that I need the result in fiscal time. There is one table marrying up calendar time and fiscal time.</p>

<p>When extracting the month from a table I query, I would like to join it to the calendar month part of the marry-up-table, which contains fiscal year and calendar year dates. This table is named MSP_TimeByDay_OlapView and contains both the columns CalendarMemberKeyMonth and FiscalMemberKeyPeriod.</p>

<p>This way, in the end I could add a column with the fiscal month to the result and filter by this instead of the calendar month.</p>

<p>Query that works, but returns NULL for the fields from the calendar - fiscal table:</p>

<pre><code>SELECT 
MSP_EpmProject_UserView.ProjectName,
MSP_EpmResource_UserView.ResourceName,
MSP_EpmResource_UserView.Discipline,
MONTH(MSP_EpmAssignmentByDay_UserView.TimeByDay) AS Month,
SUM(MSP_EpmAssignmentByDay_UserView.AssignmentMaterialWork) As Materials,
SUM(MSP_EpmAssignmentByDay_UserView.AssignmentWork) AS Work,
MSP_TimeByDay_OlapView.[CalendarMemberKeyMonth],
MSP_TimeByDay_OlapView.[FiscalMemberKeyPeriod]

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 MSP_TimeByDay_OlapView
     ON MSP_EpmAssignmentByDay_UserView.[TimeByDay] = MSP_TimeByDay_OlapView.CalendarMemberKeyMonth

WHERE (MSP_EpmAssignmentByDay_UserView.TimeByDay BETWEEN '2016-01-01' AND '2016-12-31') 
GROUP BY
MSP_EpmProject_UserView.ProjectName,
MSP_EpmResource_UserView.ResourceName,
MSP_EpmResource_UserView.Discipline, 
MONTH(MSP_EpmAssignmentByDay_UserView.TimeByDay),
MSP_TimeByDay_OlapView.[CalendarMemberKeyMonth],
MSP_TimeByDay_OlapView.[FiscalMemberKeyPeriod]
</code></pre>

<p>This doesn't work, as the second to last line of the JOIN does not contain the <em>Month command</em> I have in the SELECT statement. However, when I add this, the query just runs for minutes (so I cancelled it). This was the following:</p>

<pre><code>SELECT 
MSP_EpmProject_UserView.ProjectName,
MSP_EpmResource_UserView.ResourceName,
MSP_EpmResource_UserView.Discipline,
MONTH(MSP_EpmAssignmentByDay_UserView.TimeByDay) AS Month,
SUM(MSP_EpmAssignmentByDay_UserView.AssignmentMaterialWork) As Materials,
SUM(MSP_EpmAssignmentByDay_UserView.AssignmentWork) AS Work,
MSP_TimeByDay_OlapView.[CalendarMemberKeyMonth],
MSP_TimeByDay_OlapView.[FiscalMemberKeyPeriod]

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 MSP_TimeByDay_OlapView
     ON MONTH(MSP_EpmAssignmentByDay_UserView.TimeByDay) = MSP_TimeByDay_OlapView.CalendarMemberKeyMonth

WHERE (MSP_EpmAssignmentByDay_UserView.TimeByDay BETWEEN '2016-01-01' AND '2016-12-31') 
GROUP BY
MSP_EpmProject_UserView.ProjectName,
MSP_EpmResource_UserView.ResourceName,
MSP_EpmResource_UserView.Discipline, 
MONTH(MSP_EpmAssignmentByDay_UserView.TimeByDay),
MSP_TimeByDay_OlapView.[CalendarMemberKeyMonth],
MSP_TimeByDay_OlapView.[FiscalMemberKeyPeriod]
</code></pre>

<p>I have tried implementing a CTE, but haven't succeeded with that, as I keep getting errors that the multi-part identifier could not be bound.</p>

<pre><code>WITH CTE(Ass)
AS
--Define CTE query.
(
SELECT MONTH(MSP_EpmAssignmentByDay_UserView.TimeByDay) AS Ass
FROM MSP_EpmAssignmentByDay_UserView
)

--Define outer query.
SELECT 
MSP_EpmProject_UserView.ProjectName,
MSP_EpmResource_UserView.ResourceName,
MSP_EpmResource_UserView.Discipline,
MONTH(MSP_EpmAssignmentByDay_UserView.TimeByDay) As Month,
SUM(MSP_EpmAssignmentByDay_UserView.AssignmentMaterialWork) As Materials,
SUM(MSP_EpmAssignmentByDay_UserView.AssignmentWork) AS Work,
MSP_TimeByDay_OlapView.[CalendarMemberKeyMonth],
MSP_TimeByDay_OlapView.[FiscalMemberKeyPeriod],
CTE.Ass

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 
     FULL OUTER JOIN MSP_TimeByDay_OlapView
     ON CTE.Ass = MSP_TimeByDay_OlapView.CalendarMemberKeyMonth

WHERE (MSP_EpmAssignmentByDay_UserView.TimeByDay BETWEEN '2016-01-01' AND '2016-12-31') 
GROUP BY
MSP_EpmProject_UserView.ProjectName,
MSP_EpmResource_UserView.ResourceName,
MSP_EpmResource_UserView.Discipline, 
MONTH(MSP_EpmAssignmentByDay_UserView.TimeByDay),
MSP_TimeByDay_OlapView.[CalendarMemberKeyMonth],
MSP_TimeByDay_OlapView.[FiscalMemberKeyPeriod]
</code></pre>

<p>Is a CTE needed?</p>