Ok, I guess wasn't as specific as I needed to be.
Here is essentially what I need.
I have two separate tables application_status and application_status log. Within the application status table there is the application_status_id and the application_status_description. The application_status_log table holds information on applications and the connecting factor between the two tables are the the status and the application_status_id.
So what I need to do is pull the latest status description of each application.
Here is the coding that I have so far:
select distinct c.name as 'Company Name', ss.name as 'Program' ,a.application_number as 'Application Number' ,a.sites as 'Site Count' ,convert(varchar(10), a.accreditation_start_date, 101) as 'Start of Accreditation' ,convert(varchar(10), aag.app_submission_due_date, 101) as 'App Due Date' ,aag.invoice_fee as 'Previous Accreditation Fee Paid' ,'' as 'POS Cycle' ,LTRIM((select i.firstName from individual i where i.individualid = aag.pc_id_mysql)) + ' ' + RTRIM((select i.lastName from individual i where aag.pc_id_mysql = i.individualid)) as 'Primary Contact' ,(select i.email from individual i where i.individualid = aag.pc_id_mysql) as 'Primary Contact Email' ,(select i.title from individual i where i.individualid = aag.pc_id_mysql)as 'Primary Contact Title' , LTRIM((select i.firstName from individual i where i.individualid = aag.am_id_mysql)) + ' ' + RTRIM((select i.lastName from individual i where aag.am_id_mysql = i.individualid)) as 'Account Manager' [B][I][U],ast.application_status_description as 'Status'[/U][/I][/B] ,convert(varchar(10), a.accreditation_expiration, 101) as 'Expiration Date' ,(select convert(varchar(10),asl.user_date, 101)) as 'AC Decision Date' from [application] a inner join accreditation_agreement aag on a.client_id = aag.company_id inner join company c on a.company_id_mysql = c.companyid inner join application_accreditation aa on a.application_id = aa.application_id inner join standard_set ss on aa.standard_set_id = ss.standard_set_id inner join application_status_log asl on a.application_id = asl.application_id inner join application_status ast on asl.status = ast.application_status_id WHERE C.name not like 'URAC%' order by 'Company Name'
I know the bold/italicized portion of my coding is pulling back all of the statuses for each application but what I need is the latest (which can is known via the entered_date which is located within the application_status_log table)
Any assistance would greatly help!!!