Help!!! need query for latest record

[KRichardson1127]

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!!!

Thanks!!

[cgyrob]

You should use code blocks as it makes it alot easier to read.

[/code}

Did you try an inline view? something like this maybe

[code=sql]
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'
,(Select top 1 ast.application_status_description 
	from application_status ast where ast.application_status_idd = asl.status 
	order by entered_date desc) as 'Status'
,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 can't test so not sure if this syntax will work. The other syntax that you can use is to join a subquery.

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'
,subquery.Status-description
,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 
inner join (Select top 1 ast.application_status_description,   application_status_id 
                 from application_status   
                  order by entered_date desc
) subquery on asl.status = subquery.application_status_id

WHERE C.name not like 'URAC%'
order by 
'Company Name'

A variation on one of these syntax's will work you will just have to play around with them to find out which one works for you.

[KRichardson1127]

That did not work for me....

But I got the following coding and it's not working for me either:

Ok...this is the coding I have compiled

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](select application_Status_Description 
	from 
		(select Application_ID, application_Status_Description, row_Number() 
		 over (partition by Application_ID order by Entered_Date DESC) rn 
			from Application_Status_Log INNER JOIN Application_Status on Status = application_Status_ID) X 
		where rn = 1) 
[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'

But when I execute it, I get the following status message:

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

All I need is the current status for the application and it's company..

[cgyrob]

Have you tried joining the subquery in the from clause instead of using the inline view.

[KRichardson1127]

No I have not...but I will try to do so now...