Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2009
    Posts
    8

    Unanswered: Query to for latest record entry...

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

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Version of SQL Server?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jul 2009
    Posts
    8
    SQL Server 2005

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    SELECT  *
    FROM    --derived data
            (
                SELECT    *
                        , last_row_by_app   = ROW_NUMBER()  OVER    (PARTITION BY   application_id
                                                                    ORDER BY        entered_date DESC)
                FROM    application_status_log
            ) AS der_t
    WHERE   last_row_by_app = 1
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jul 2009
    Posts
    8
    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!!

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    K,
    I didn't go through the large SQL statement you just posted, but from your first sample you just left a little piece out. Try:

    from application_status_log asl1
    where .....
    asl1.entered_date = (select max(asl2.entered_date) from application_status_log asl2 where asl1.application_id = asl2.application_id)

    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •