如何根据上次开始日期获取最新行 [英] How do i get the latest row based on the last start date

问题描述

您好b $ b

任何人都可以帮助我使用ssas中的计算成员检索最新的Startdate记录及其相应的记录。我有四个维度，其中两个是日期维度，最后一个是部门。

Hi
Can any one plese help me in retreiving the latest Startdate record with its corresponding record using calculated member in ssas. I have four dimension out of which two are date dimension and the last is department.

EmployeeId StartDate EndDate Dept
101 03/02/2013 01/01/2014 Dept1
102 04/07/2012 25/05/2005 Dept5
102 25/05/2005 15/06/2012 Dept7
101 02/01/2014 27/09/2015 Dept6
102 15/06/2012 20/10/2015 Dept4

每列都是单独的维度。

我需要的结果应该是最新的开始日期及其对应的行如下

Each column is maintatined individual dimensions.
My result required should be latest start date and its corresponding row like below

EmployeeId StartDate EndDate Dept
101 02/01/2014 27/09/2015 Dept6
102 15/06/2012 20/10/2015 Dept4

提前谢谢



我的尝试：

Thank you in advance

What I have tried:

WITH
MEMBER [Measures].[LatestEmpStartDate] AS
 TAIL(NONEMPTY([Emp_StartDate].[Full Date].[Full Date].MEMBERS, [Measures].[No_of_Emp]),1).ITEM(0).MEMBER_NAME
 
MEMBER [Measures].[LatestEmpEndDate] AS
 TAIL(NONEMPTY(
 {LINKMEMBER(TAIL(NONEMPTY([Emp_StartDate].[Full Date].[Full Date].MEMBERS, [Measures].[No_of_Emp]),1).ITEM(0)
 ,[Emp_EndDate].[Full Date]) : null}
 , [Measures].[No_of_Emp]),1).ITEM(0).MEMBER_NAME


MEMBER [Measures].[LatestDept] AS
  TAIL(NONEMPTY(
 {LINKMEMBER(TAIL(NONEMPTY([Emp_StartDate].[Full Date].[Full Date].MEMBERS, [Measures].[No_of_Emp]),1).ITEM(0)
 , [DIM_Dept].[Dept_Code])} 
 , [Measures].[No_of_Emp]),1).ITEM(0).MEMBER_NAME

SELECT
 {
  [Measures].[LatestEmpStartDate]
 , [Measures].[LatestEmpEndDate]
  , [Measures].[LatestDept] 
 } on columns
 
,NON EMPTY
 [Employee].[EmpID].members
 on ROWS
 From Cub_

推荐答案

只需使用JOIN：

Just use a JOIN:

SELECT a.EmployeeId, a.StartDate, a.EndDate, a.Dept FROM MyTable a
JOIN (SELECT EmployeeID, MAX(StartDate) AS maxSD FROM MyTable
      GROUP BY EmployeeId) b
ON a.EmployeeId = b.EmployeeId AND a.StartDate = b.maxSD

这篇关于如何根据上次开始日期获取最新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！