Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2002
    Posts
    84

    Red face Unanswered: How to improve this SQL statement?

    I have a query in MS Access that will select the only record from a table DECISION, given three input parameters: pMonth, pLoansize and pLTV. The record should have the maximal possible value for MONTH, LOAN_SIZE and PERCENTAGE, in this order.

    My query is working, but a bit slow since it seems joining four tables. I'd appreicate for any idea about how to improve this query, which is as
    follow. Thanks.

    -----------------------------------------------------------------------------
    SELECT distinct C.DECISION_ID, C.DECISION_NAME_ID, C.MONTH, C.LOAN_SIZE, C.percentage, C.ADMIN_FEE_HL_ID, C.PNTY_ID, C.OFFER_RATE_ID
    FROM DECISION AS A, DECISION AS B, DECISION AS C, DECISION AS D
    WHERE
    (D.MONTH = (SELECT max(MONTH) FROM DECISION D WHERE pLoansize>=D.LOAN_SIZEand pMONTH >= D.MONTH AND pLTV >= D.percentage))
    AND
    (B.LOAN_SIZE= (SELECT max(loan_size) FROM DECISION B WHERE B.MONTH = D.MONTH AND pLoansize>=B.LOAN_SIZEAND pLTV >= B.percentage))
    AND
    (A.percentage = (SELECT max(percentage) FROM DECISION A WHERE A.MONTH = D.MONTH and A.loan_size=B.LOAN_SIZE
    AND pLTV >= A.percentage))
    AND C.MONTH = D.MONTH
    AND C.LOAN_SIZE= B.loan_size
    AND C.percentage = A.percentage

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    total shot in the dark, please let me know if this works:
    Code:
    SELECT DECISION_ID
         , DECISION_NAME_ID
         , MONTH
         , LOAN_SIZE
         , percentage
         , ADMIN_FEE_HL_ID
         , PNTY_ID
         , OFFER_RATE_ID
      FROM DECISION        AS A
     WHERE MONTH = (
                SELECT max(MONTH)
                  FROM DECISION 
                   )
       AND LOAN_SIZE = (
                SELECT max(loan_size) 
                  FROM DECISION 
                 WHERE MONTH = A.MONTH 
                       )
       AND percentage = (
                SELECT max(percentage)
                  FROM DECISION  
                 WHERE MONTH = A.MONTH 
                   and loan_size = a.LOAN_SIZE 
                        )
    rudy
    http://r937.com/

  3. #3
    Join Date
    Apr 2002
    Posts
    84
    Hi r937,

    Thanks for the try. But I am afraid that it is not working. Your SQL may end up with no record selected. For example, a simplified DECISION table:

    Month, Loan_size, Percentage
    8, 100, 70
    4, 200, 50
    6, 150, 80

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    did you try it?

    i tried it on your sample of 3 and it gave the row with

    8, 100, 70


    if you just wanted the max of all three colulmns, that's a totally different query (and a lot simpler, too)

    i thought you wanted row integrity

    for example, suppose there are 80 rows in the table, of which 20 belong to the highest month

    then out of those 20 rows which have the highest month, 6 of those rows have the highest loan_size for that month

    then out of those 6 rows which have the highest loan_size for the highest month, one of them has the highest percentage

    my query will always return a row, as long as there is at least one row in the table


    perhaps you did not explain your problem correctly?

    i tried to see what your query was attempting to do, but it's seriously messed up

    or maybe i totally misunderstood you


    rudy

  5. #5
    Join Date
    Apr 2002
    Posts
    84

    Thumbs up

    Hi Rudy,

    Sorry it was my mistake. The query is working now since I missed
    out something. Thank you very much.

Posting Permissions

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