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

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.

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

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

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.