SQL从日期列中提取月份并在JOIN命令中重用

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.

我有一个SQL查询（Microsoft SQL服务器），我在其中从日期列中提取一个月。我想在join命令中重用此提取的月份，以将其用作将结果与另一个表中的另一列连接的标准。

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.

背景是在数据库中使用日历时间，但我需要在财政时间内得到结果。有一个表结合日历时间和财政时间。

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.

从我查询的表中提取月份时，我想将其加入到结婚表的日历月部分，其中包含会计年度和日历年度日期。此表名为MSP_TimeByDay_OlapView，包含CalendarMemberKeyMonth和FiscalMemberKeyPeriod列。

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.

这样，最后我可以将一个包含会计月份的列添加到结果中，并按此而不是按日历月过滤。

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

查询有效，但从日历 - 财务表中的字段返回NULL：

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]

This doesn't work, as the second to last line of the JOIN does not contain the Month command 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:

这不起作用，因为JOIN的倒数第二行不包含SELECT语句中的Month命令。但是，当我添加它时，查询只运行几分钟（所以我取消了它）。这是以下内容：

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]

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.

我已经尝试过实现一个CTE，但是没有成功，因为我不断得到多部分标识符无法绑定的错误。

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]

Is a CTE needed?

需要CTE吗？

1 个解决方案

#1

I've just found the answer to my question :)

我刚刚找到了问题的答案:)

Nevertheless, Thank you to all of you!

不过，谢谢大家！

Adding one line, joining on the year as well, solved the issue. This was probably not solvable, not having the information that other time levels exist as well - I apologise for that, but I hadn't thought that this might be the issue.

添加一行，加入年份也解决了这个问题。这可能是不可解决的，没有其他时间级别存在的信息 - 我为此道歉，但我没想到这可能是问题。

So in case anyone is trying to do the same (using MS Project Server), adding the following helps:

因此，如果有人试图这样做（使用MS Project Server），添加以下内容会有所帮助：

AND YEAR(MSP_EpmAssignmentByDay_UserView.[TimeByDay]) = MSP_TimeByDay_OlapView.CalendarMemberKeyYear

The query now is really slow, but it does work.

查询现在非常慢，但它确实有效。