How do i get the latest row based on the last start date

Question

0.00/5 (No votes)

See more:

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:

SQL

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_

Posted 15-Feb-16 6:16am

Member 12328896

Updated 15-Feb-16 6:48am

Maciej Los

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2016-02-15T06:34:00

Solution 1

Just use a JOIN:

SQL

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

Posted 15-Feb-16 6:34am

OriginalGriff