Select newest date after inner join

Question

Using SQL Server, I have a SQL view that I use to integrate data into our financial software. The view currently looks like this, returning one row per employee_id:

SELECT        TOP (100) PERCENT dbo.EmployeeMaster.Employee_ID, dbo.EmployeeMaster.Branch_ID, dbo.EmployeeMaster.ModifyDatetime AS EmployeeMasterModified, 
                         dbo.EmployeePRMaster.ModifyDatetime AS PRMasterModified, dbo.EmployeePRLocal.ModifyDatetime AS EmployeePRLocalModified, dbo.EmployeePRState.ModifyDatetime AS EmployeePRStateModified, 
                         dbo.FMT_CLEAN_STRING(dbo.EmployeeMaster.First_Name, 'abcdefghijklmnopqrstuvwxyz') AS First_Name_Cleaned, dbo.FMT_CLEAN_STRING(dbo.EmployeeMaster.Last_Name, 'abcdefghijklmnopqrstuvwxyz') 
                         AS Last_Name_Cleaned, dbo.EmployeeMaster.Address1, dbo.EmployeeMaster.Address2, dbo.EmployeeMaster.City, dbo.EmployeeMaster.StateCode, dbo.EmployeeMaster.PostalCode, 
                         dbo.EmployeeMaster.Country, dbo.EmployeeMaster.Phone, dbo.EmployeeMaster.Phone2, dbo.EmployeePRMaster.SocialSecurityNumber, dbo.EmployeePRMaster.Gender, dbo.EmployeePRMaster.MaritalStatus, 
                         dbo.EmployeePRMaster.Birthdate, dbo.EmployeePRMaster.HireDate, dbo.EmployeePRMaster.HireDateAdjusted, dbo.EmployeePRMaster.CheckHandlingCode, dbo.EmployeePRMaster.FedExemptions, 
                         dbo.EmployeePRMaster.FedFilingStatus, dbo.EmployeePRMaster.FedAdditionalWith, dbo.EmployeePRLocal.LocalCode, dbo.EmployeePRLocal.Exemptions, dbo.EmployeePRLocal.AdditionalAmount, 
                         dbo.EmployeePRState.StateCode AS StateTaxCode, dbo.EmployeePRState.IsExemptBlind, dbo.EmployeePRState.IsExemptBlindSpouse, dbo.EmployeePRState.IsExemptOver65, 
                         dbo.EmployeePRState.IsExemptSelf, dbo.EmployeePRState.IsExemptSpecial, dbo.EmployeePRState.IsExemptSpouse, dbo.EmployeePRState.IsExemptSpouse65, dbo.EmployeePRState.Dependents, 
                         dbo.EmployeePRState.AdditionalAmount AS StateAdditional, dbo.CodePREthnicOrigin.Description, dbo.EmployeeMaster.Status, dbo.EmployeePRLocal.FilingStatus, dbo.OrderAssignment.End_Actual_Date
FROM            dbo.EmployeeMaster LEFT OUTER JOIN
                         dbo.EmployeePRLocal ON dbo.EmployeeMaster.Employee_ID = dbo.EmployeePRLocal.Employee_ID INNER JOIN
                         dbo.EmployeePRMaster ON dbo.EmployeeMaster.Employee_ID = dbo.EmployeePRMaster.Employee_ID INNER JOIN
                         dbo.EmployeePRState ON dbo.EmployeeMaster.Employee_ID = dbo.EmployeePRState.Employee_ID INNER JOIN
                         dbo.CodePREthnicOrigin ON dbo.EmployeePRMaster.EthnicOrigin = dbo.CodePREthnicOrigin.Code INNER JOIN
                         dbo.OrderAssignment ON dbo.EmployeeMaster.Employee_ID = dbo.OrderAssignment.Employee_ID
WHERE        (dbo.EmployeeMaster.ModifyDatetime > DATEADD(Day, - 14, GETDATE())) AND (dbo.EmployeeMaster.Branch_ID <> 'U') AND (dbo.EmployeeMaster.Status = '1') OR
                         (dbo.EmployeeMaster.Branch_ID <> 'U') AND (dbo.EmployeePRMaster.ModifyDatetime > DATEADD(Day, - 14, GETDATE())) OR
                         (dbo.EmployeeMaster.Branch_ID <> 'U') AND (dbo.EmployeePRLocal.ModifyDatetime > DATEADD(Day, - 14, GETDATE())) OR
                         (dbo.EmployeeMaster.Branch_ID <> 'U') AND (dbo.EmployeePRState.ModifyDatetime > DATEADD(Day, - 14, GETDATE()))

The OrderAssignment.End_Actual_date is a one-to-many and as such returns multiple rows. I want to get only the most recent date (per employee_id). I can accomplish this separately with the following query:

select TOP 1 Employee_ID,End_Actual_Date from OrderAssignment
Order by end_actual_date DESC

However, I'm stumped on how to basically combine the two. Thoughts?

I would suggest that you delete this question. Then ask another question by simplifying the query. That is much more likely to be answered. — Gordon Linoff, Mar 21 '18 at 17:09
Possible duplicate of [SQL join: selecting the last records in a one-to-many relationship](https://stackoverflow.com/questions/2111384/sql-join-selecting-the-last-records-in-a-one-to-many-relationship) — Tab Alleman, Mar 21 '18 at 17:15
I googled "SQL Join to most recent row" to find the duplicate. Learning to Google is going to be your most valuable skill as a programmer. — Tab Alleman, Mar 21 '18 at 17:16
i cant see why you cant just put your order by at the end of the bigger query? — WhatsThePoint, Mar 21 '18 at 17:17
Putting the order by at the end gives the multiple rows, but simply orders the results by end_actual_date. I basically need to maintain the one-to-one. — SaintFrag, Mar 21 '18 at 17:29
This is the query I am working with. It's complex, which I agree is part of the problem. But getting an answer that I can't work with isn't as helpful. — SaintFrag, Mar 21 '18 at 17:30
@TabAlleman I'm not a programmer. And I did Google, just came up with nothing useful, kind of like your responses. If they were useful, then my admittable ignorance is leading me to not understand how to apply them to my query. Hence asking the question. If I'd found something I could have applied, I wouldn't have posted in hopes of being ridiculed. — SaintFrag, Mar 21 '18 at 17:33
@SaintFrag There are many people too lazy to Google for an answer. None of us want to do other people's work for free though. TabAlleman is leading you in the right direction. You might look into `cross apply` which will work with the other query you noted. — shawnt00, Mar 21 '18 at 17:44
If you can accomplish it separate with `select TOP 1 Employee_ID, End_Actual_Date from OrderAssignment Order by end_actual_date DESC` then that should work on the main query. There is something missing in your explanation. — paparazzo, Mar 21 '18 at 17:49
@paparazzo: The tricky part is that I need to return all the results in the main query (one per employee_id), so I can't include the TOP 1 up there or I'd just get one employee_id. — SaintFrag, Mar 21 '18 at 17:53
But that is exactly what you said you need in the question. You mention NOTHING of per employee_id. Answer is only as good as the question. — paparazzo, Mar 21 '18 at 17:55
@paparazzo Sorry, I guess I thought that part was more obvious than it was. I've updated my question to better reflect that per your suggestion. Thank you. — SaintFrag, Mar 21 '18 at 17:58

Mazhar · Accepted Answer · 2018-03-21T17:38:25.043

1

Amend your inner join to dbo.OrderAssignment table like so, use ROW_NUMBER FUNCTION to list the order of multiple rows in End_Actual_Date desc order

INNER JOIN
        ( SELECT OA.EMPLOYEE_Id, oa.End_Actual_Date
             RN= Row_number()over( partition by oa.employee_Id 
            Order by oa.End_Actual_Date desc )
            FROM  dbo.OrderAssignment OA
    ) X ON dbo.EmployeeMaster.Employee_ID = X.Employee_ID
          And X.RN =1

Apologies for formatting, I'm using my phone

edited Mar 21 '18 at 17:38

answered Mar 21 '18 at 17:24

Mazhar

3,797
1
12
29

Thank you, this was the ticket! An additional note is that in the select statement to change from dbo.OrderAssignment.End_ActualDate to X.End_Actual_Date. – SaintFrag Mar 21 '18 at 17:50

Select newest date after inner join

1 Answers1