I have a table entitled Application_Status_Log and this table holds records for multiple applications. What I want to do is pull the latest record for each application. I know I need use the MAX() function.
The query I have:
where entered_date = (select max(entered_date) from application_status_log)
This just pulls back one record though, I need multiple records.
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:
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' ,ast.application_status_description as 'Status'
,convert(varchar(10), a.accreditation_expiration, 101) as 'Expiration Date'
convert(varchar(10),asl.user_date, 101)) as 'AC Decision Date'
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%'
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)