Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2005
    Posts
    6

    Unanswered: Simplify (and Correct) a query

    Iíve got a table called tblApplicant_Details with the following fields - Applicant_ID, Application_ID, Net_Income, Loans.

    In this table Iíve got a list of people and their income details (Net_Income) and expenses (loans). In some cases there will be 2 applicants with the same application_ID.

    What I need to do is select one applicant (Applicant_ID) per application (Application_ID). In the case of 2 applicants for an application, I need to select the person with the highest income (net_income - loans), if both of the applicants have the same income I want the one with the lowest Applicant_ID and if only one person applies then that person.

    Below is the code Iíve been using. Iíve noticed that its not always selecting an applicant for each application. I know itís also very long for what I am trying to do but I was hoping someone would be able to tell me how I can fix it and tidy it up a bit.

    Code:
    SELECT     Application_ID, MIN(Applicant_ID) AS Applicant_ID
    FROM         (SELECT     DERIVEDTBL.Application_ID, dbo.tblAPPLICANT_DETAILS.Applicant_ID
                  FROM (SELECT     MAX(APPLICANT.Net_Income - APPLICANT.Loans) AS Income, APPLICATION.Application_ID
                           FROM dbo.tblAPPLICANT_DETAILS APPLICANT INNER JOIN 
                           dbo.tblAPPLICATION_DETAILS APPLICATION ON APPLICANT.Application_ID = APPLICATION.Application_ID
                           GROUP BY APPLICATION.Application_ID) DERIVEDTBL INNER JOIN
                            dbo.tblAPPLICANT_DETAILS ON DERIVEDTBL.Application_ID = dbo.tblAPPLICANT_DETAILS.Application_ID AND 
                            DERIVEDTBL.Income = dbo.tblAPPLICANT_DETAILS.Net_Income - Loans) DERIVEDTBL
    GROUP BY Application_ID
    Thanks

  2. #2
    Join Date
    Jun 2003
    Posts
    269
    Code:
    select tm1.* from 
      (select Applicant_ID,
       Application_ID,
       Net_Income-Loans as Net
      from  APPLICATION 
      ) as tm1 
       join
       (
        select  Application_ID,
         max(Net_Income-Loans) as maxNet
        from  APPLICATION
        group by Application_ID
       )as tm 
       on tm.Application_ID=tm1.Application_ID
       and tm1.Net=tm.maxNet
    where tm1.Applicant_ID=(
                                        select  min(Applicant_ID) 
                                        from  APPLICATION 
                                        where  App.Application_ID=tm1.Application_ID 
                                        and Net_Income-Loans=tm1.Net
                                      )
    
    
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  3. #3
    Join Date
    Oct 2005
    Posts
    6
    Thanks for your reply Mallier. That works perfectly (and looks a lot tidier!).

    C

  4. #4
    Join Date
    Jun 2003
    Posts
    269
    Sorry,alias name 'App' have missed(somehow ) from my previous post.Corrected one is,
    Code:
    select tm1.* from (select Applicant_ID, Application_ID, Net_Income-Loans as Net from APPLICATION ) as tm1 join ( select Application_ID, max(Net_Income-Loans) as maxNet from APPLICATION group by Application_ID )as tm on tm.Application_ID=tm1.Application_ID and tm1.Net=tm.maxNet where tm1.Applicant_ID=( select min(Applicant_ID) from APPLICATION App where App.Application_ID=tm1.Application_ID and Net_Income-Loans=tm1.Net )
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

Posting Permissions

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