Query to for latest record entry...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query to for latest record entry...

Author

Topic

KRichradson1127
Starting Member

20 Posts

Posted - 2009-08-05 : 09:01:14

Hello all,

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:

select
application_id
from application_status_log
where entered_date = (select max(entered_date) from application_status_log)

This just pulls back one record though, I need multiple records.

Any suggestions?

Thanks in ADVANCE!!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-05 : 09:11:53

Refer point 3
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

Madhivanan

Failing to plan is Planning to fail

KRichradson1127
Starting Member

20 Posts

Posted - 2009-08-05 : 10:29:44

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'
,ast.application_status_description 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 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!!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-05 : 12:18:30

I will assume that if you remove ",ast.application_status_description as 'Status'" as well as the INNER JOIN to [application_status] then you get the correct number of rows. And then you just want to get the most recent status for that application.

so replace the INNER JOIN to your ast table with a CROSS APPLY:


cross apply (
       select top 1 application_status_description as status 
       from   application_status 
       where  applicationid = a.applicationid 
       order by [entered_date] desc
       ) ast

Be One with the Optimizer
TG

KRichradson1127
Starting Member

20 Posts

Posted - 2009-08-05 : 12:47:25

No TG,

I get the same number of rows.

The way this query works is it pulls the companies in question along with each application associated with the company. There is a status associated with each application, but what I need is the most current status. So what I end up pulling is all of the statuses associated with that application. What I need is the most current status, which can be found by looking at the entered_date from the application_status_log table.

This is some other coding I tried...but I keep getting the:

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.

whenever I execute it....

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-05 : 13:36:24

did you try my suggestion (of replacing the inner join with the cross apply)?

Be One with the Optimizer
TG

KRichradson1127
Starting Member

20 Posts

Posted - 2009-08-05 : 13:41:14

Yea I tried that....and it returned the same number of rows.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-05 : 13:55:06

You mean you were still getting an extra row for each [application_status_description] associated with an application? But without any references to the [application_status] table, either by an inner join or cross apply, you got the correct number of rows?

Be One with the Optimizer
TG

KRichradson1127
Starting Member

20 Posts

Posted - 2009-08-05 : 13:55:07

The following query I have pulls the records:

(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)

I attempted plugging it into this query in the area indicated with the ******:

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',
***********
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 it would not allow me to pull the most current status...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-05 : 14:11:02

It's hard to tell where your problem is because I don't know the relation ships between your tables. It seems like application_status_log and application_status will both have many rows for each row that you want returned.

Does this return the correct number of rows (even though some columns are missing)?


select 
       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]
       ,i.firstName + ' ' + i.lastName                         as [Primary Contact]
       ,i.email                                                as [Primary Contact Email]
       ,i.title                                                as [Primary Contact Title]
       ,am.firstName + ' ' + am.lastName                       as [Account Manager]

       ,convert(varchar(10), a.accreditation_expiration, 101)  as [Expiration 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

left outer join individual i
       on i.individualid = aag.pc_id_mysql

left outer join individual am
       on am.individualid = aag.am_id_mysql

WHERE C.name not like 'URAC%'
order by c.[name]

Be One with the Optimizer
TG

KRichradson1127
Starting Member

20 Posts

Posted - 2009-08-05 : 14:42:39

The application_status table just holds the status_id and the status_description, while the application_status_log table has the app_id, status_id, entered_date and a range of other fields, but the key fields are the ones that are mentioned.

The entered_date field is key because it will show the most current status...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-05 : 14:47:15

I see...so what was the answer to this:
"Does this return the correct number of rows (even though some columns are missing)?"

Be One with the Optimizer
TG

KRichradson1127
Starting Member

20 Posts

Posted - 2009-08-05 : 14:48:57

Yes it does....

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-05 : 14:52:26

Ok then how about this:


select 
       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]
       ,i.firstName + ' ' + i.lastName                         as [Primary Contact]
       ,i.email                                                as [Primary Contact Email]
       ,i.title                                                as [Primary Contact Title]
       ,am.firstName + ' ' + am.lastName                       as [Account Manager]

       ,ca.Status
       ,convert(varchar(10), a.accreditation_expiration, 101)  as [Expiration Date]
       ,ca.[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

cross apply (
       select top 1 
              ast.application_status_description        as [Status]
              ,convert(varchar(10), asl.user_date, 101) as [AC Decision Date]
       from   application_status_log asl 
       inner join application_status ast 
              on ast.application_status_id = asl.status
       where  asl.application_id = a.application_id
       order by asl.[entered_date] desc
       ) ca

left outer join individual i
       on i.individualid = aag.pc_id_mysql

left outer join individual am
       on am.individualid = aag.am_id_mysql

WHERE C.name not like 'URAC%'
order by c.[name]

Be One with the Optimizer
TG

KRichradson1127
Starting Member

20 Posts

Posted - 2009-08-05 : 15:12:16

It still returned the same number of rows...:(

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-05 : 15:16:38

same number of rows as what?
You mean you now have duplicate rows compared to this test:

TG: Does this return the correct number of rows (even though some columns are missing)?
KRichradson1127: Yes it does....?

Be One with the Optimizer
TG

KRichradson1127
Starting Member

20 Posts

Posted - 2009-08-05 : 15:19:21

No TG sorry for the confusion.

It returns the correct number of rows in regards to showing the various statuses (which is actually too many)....but if the rows returned are only of those with the most current status, then less rows should be returned.

I'm currently getting 3266 rows, when ideally I need to be getting back 1518 rows

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-05 : 16:23:40

So the "KRichradson1127: Yes it does....?" result returned 1518 rows but my latest change returned 3266?

Be One with the Optimizer
TG

KRichradson1127
Starting Member

20 Posts

Posted - 2009-08-06 : 12:33:13

No...TG....the 1518 rows resulted from the following derived table:

(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)

This table isolated the records with the most current status...but I'm not sure as to how exactly join this derived table to the query I created...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-06 : 13:24:28

That is functionally identical to my cross apply statement, right? They should accomplish the same thing

>>but I'm not sure as to how exactly join this derived table to the query I created
Assuming that the correlating column is [Application_ID] then just this:


inner join  (
       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) as asl
              on asl.application_ID = a.application_id

Be One with the Optimizer
TG

KRichradson1127
Starting Member

20 Posts

Posted - 2009-08-07 : 09:16:25

I keep getting a syntax error; it keeps saying there is incorrect syntax by the "where"

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources