I think you should use VladRUS.ca's solution.  But, just in case you want another approach that is FAR less efficient... 

CREATE TABLE #TEST( SOR_CODE varchar(8))

INSERT INTO #TEST VALUES( 'AS4000WA')...

Here is one possible solution using Sushila's DDL & sample data and the maximum #QUALITY_PASS_PERCENT.EVALUATION_DTE, (you can decide how to choose that date, but you need to GROUP upon that...

Someone will probably be able to come up with a better way to handle those PersonID's that do not have a zero, but here is my crack at it.  This...

This should work.  If your "Other_Columns" come from Branches, you will probably need to add those to the sub-select which joined upon. 

SELECT [File_ID],

              B.Branch_ID,

              Other_Columns

FROM Main_Table...

Sushila is absolutely correct. 

I am guessing you are getting the following: 

ASPECT_ID, AGENT_NAME, [ACW %], [ATT in sec], [Average Compliance %], [Total Scores], ADHERENCE_DTE, EVALUATION_DTE, AUDIT_DTE

7609             Alexander, Antuan,...

Could you give us the same output, but chose one or two ASPECT_ID's (which should match the HSCID)?  Thanks. 

That way I can see the dates you are hoping to...

That is excellent Phil!  Thanks for reading and passing on that article... 

CREATE TABLE #Test( [ID] integer IDENTITY(1,1),

                                    NewDate datetime)

INSERT INTO...

I am not understanding the question.  Should the dates in the final 3 fields of this query be the same for each record?  What is the problem with the output? ...

If the id's from the two tables do not match, you may be able to handle it this way: 

SELECT O.[NAME], HRL.[NAME]

FROM ORGANIZATION O

   INNER JOIN HRL ON( O.[NAME]...

It sounds like a recursive Manager/Employee table...

I am a bit confused with this question.  It looks as if you simply need to alter your existing data to match the actual events... 

The following will yield the...

Noel is correct. 

Mine orders this like an Index to a document and that is not what you want.  (I did not notice that in your desired order). 

I think this may work for you

CREATE TABLE #Numbers( Number varchar(15))

INSERT INTO #Numbers VALUES( '1')

INSERT INTO #Numbers VALUES( '2')

INSERT INTO #Numbers VALUES( '2.1')

INSERT INTO #Numbers...

Here's a potential option: 

IF( NOT EXISTS( SELECT diwor FROM MATTER_DATES

                         WHERE MATTER_DATES.MATTER_DIWOR = @matterDiwor

                            AND MATTER_DATES.DATE_TYPE_CODE = 'INIREP'))

     BEGIN

          EXECUTE sp_diwor 'MATTER_DATES',@diwor OUTPUT

          INSERT...

Exactly, noeld!!  Those were questions raised in that post. 

This type of solution, (and we can refine the code if you need that) MUST have that format be consistent. 

Please...