Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2009
    Posts
    15

    Unanswered: Help selecting the "date before" an event

    Hi,

    I've been struggling with different ways to figure out a solution to my problem, and late last night when my final solution very intricately led me to right back to the unmodified data I started with, I knew I needed some help.

    My sample database, for the purpose of my question, is like this:

    O_Id C_ID Date Action Amt
    1 1 1/01 1 100
    2 1 1/02 1 150
    3 1 1/10 2 200
    4 2 1/11 1 250
    5 2 2/1 1 300
    6 2 2/2 2 350

    Where O_id = order, C_id = customer, Date is obvious, Action 1 = paid and Action 2 = not paid, and Amt is also obvious.

    My goal is to first query to find the customer id's that have not paid (query Action = 2) and then to find the immediately preceeding date where they paid and return that date and the amount.

    So I would query not-paid action = 2 and get C_ID "1" and "2"
    I would then like to ultimately return the information for C_ID1: 1/02, 150 and for c_id2: 2/1, 300


    I've been trying to make a single query to return the "1" and "2" and then using that as well as a last(date) command, which hasn't given me anything but a headache and mild temper-tantrum.

    Any ideas to get me pointed in the right direction?

    Thank you so much.
    Chris

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I think the following would work :
    Code:
    select d1.C_ID,
            ( select max( d3.Date ) from MyTable d3 
              where d3.Action=1 and d3.C_ID = d1.C_ID ) as last_paid,
            ( select d2.Amt from MyTable d2 
             where d2.Action=1 and d2.C_ID = d1.C_ID and d2.Date = (
                     select max( d4.Date ) from MyTable d4 
                     where d4.Action=1 and d4.C_ID = d1.C_ID ) ) as last_amt
    from MyTable d1
    where d1.Action=2;
    PS : Your date formats are a mess.

  3. #3
    Join Date
    Feb 2009
    Location
    Iceland
    Posts
    14
    Hi.

    You could also try something like:
    Code:
    SELECT 
      `C_ID`, 
      MAX(`Date`),
      `Amt`
    FROM `dbTest`
    WHERE C_ID IN(
          SELECT `C_ID` FROM `dbTest`
          WHERE `Action` = 2
        )
    AND `Action` = 1
    GROUP BY `C_ID`;
    And I agree with Mike, your dates are messed up.
    Stick with DATE, TIME, DATETIME or TIMESTAMP. They are all much much easier to work with.

  4. #4
    Join Date
    Feb 2009
    Posts
    15
    Thank you so much for the help. I will try very soon.

    And the actual dates are full dates + timestamps... I just abbreviated it for the purpose of the example. I've been reading the forum for a little while trying to learn proper database design...

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Atli
    I'm interested how you got it to work in such a small amount of SQL - does it really show the amount paid on the previous date (it looks a bit suspicious to me)?
    Mike
    Last edited by mike_bike_kite; 02-11-09 at 14:25.

  6. #6
    Join Date
    Feb 2009
    Location
    Iceland
    Posts
    14
    Quote Originally Posted by mike_bike_kite
    Alti
    I'm interested how you got it to work in such a small amount of SQL - does it really show the amount paid on the previous date (it looks a bit suspicious to me)?
    Mike
    Well spotted.

    After setting up a small test, it appears that my query does in fact not show the correct amount. It show the first amount listed.
    It gives the correct Date, but only because it bypasses the sorting by using the MAX function.

    I did find a few other ways to achieve the desired results, but the only way that was of any use was:
    Code:
    SELECT 
        `first`.`C_ID`,
        `first`.`Date`,
        `first`.`Amt`
    FROM `dbTest` AS `first`
    INNER JOIN `dbTest` AS `second`
        ON `first`.`C_ID` = `second`.`C_ID`
        AND `second`.`Action` = 2
        AND `first`.`Action` = 1
    WHERE `first`.`O_id` = (
        SELECT `third`.`O_id` FROM `dbTest` AS `third`
        WHERE `third`.`C_ID` = `first`.`C_ID`
        AND `third`.`Action` = 1
        ORDER BY `O_id` DESC
        LIMIT 1
    );
    Out of all the queries I tried, this was the only one I found that did not require a temporary table and/or filesort to function properly.

    This one and the one you posted should both work fine.
    Add an index to the Action column and they will both work slightly faster
    Last edited by Atli; 02-11-09 at 14:57.

Posting Permissions

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