Help selecting the "date before" an event

cwebbe2 · #1 (**permalink**) 02-11-09, 10:09

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

mike_bike_kite · #2 (**permalink**) 02-11-09, 10:30

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.

Atli · #3 (**permalink**) 02-11-09, 10:49

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.

cwebbe2 · #4 (**permalink**) 02-11-09, 11:30

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

mike_bike_kite · #5 (**permalink**) 02-11-09, 11:57

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

Atli · #6 (**permalink**) 02-11-09, 13:28

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

Internet Services

Web Development

Digital Marketing

Consumer Tech