Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2014
    Posts
    2

    Unanswered: MYSQL MAX(DATEDIFF) Question

    I have two tables. My problem is that the query keeps giving me a timeout error therefore i know that there is probably a better way... Can some please help me?
    Code:
    SELECT MAX(DATEDIFF(payments.paymentdate, ar.DOT))
    AS payormax, ar.payer
    FROM ar, payments
    GROUP BY ar.payer


    I am trying to get the maximum date difference between DOT from the ar table and the paymentdate from the payments table and group them by payer from the ar table. I will need to do a minimum and an AVG for these same columns as well. I am trying to create a graph curve. Thank you in advance.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm thinking that a cartesian join probably isn't the most efficient way to do what you want, but you will have to be more specific in order for me to help you find a better way.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jun 2014
    Posts
    2
    So this is a database for an ambulance company. Basically there is a table (ar) for account receivables that contain trip number (tripnumber), payer (payer) and date of transport (DOT). I also have a payments table that has trip number (tripnumber), payer (payer) and payment date (paymentdate). I need to get the maximum date difference between date of transport and date of payment. The date of transport is in the ar table and the date of payment is in the payments table for each payer. Thank you for your speedy reply. What i had initially was the query above but it is too general maybe? It would timeout before it would give me anything.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is a guess, but try:
    Code:
    SELECT MAX(DATEDIFF(payments.paymentdate, ar.DOT)) AS payormax, ar.payer
       FROM ar
       JOIN payments
          ON (payments.tripnumber = ar.tripnumber)
       GROUP BY ar.payer
    This ought to work a lot faster and give more meaningful results too!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

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