MySQL Calculating The Difference Between two dates but with a twist

Question

I have 2 tables: Product, User.

Product:
    Date(Datetime)
    Phone_Number(Number).

User: 
    Date (Datetime)
    Phone_Number(Number)
    Type(Value of N or C).

When a new user is added to the User table, it is given a date, Phone Number and value of "N" (N for New). When a user gets cancelled, it is given a date, the Phone Number and value of "C" (C for Cancelled).

Using MySQL, I need to find out the date difference between the Date the user was created and the date the user was cancelled and if they had a product (can match the user to the product by Phone Number) between the date of the creation and the date of the cancellation.

Any assistance would be greatly appreciated as my Query writing isn't terrible but this I can't work out.

Thanks.

I have tried using a LEFT JOIN to the Product table with the User table as the main table but it left me all confused and the query wouldn't run. — StefanHanotin, Sep 12 '12 at 17:18
re-think your db design. A product does not have a phone number, or a date. Create tables for user, product and order and identify by id, this will be more maintainable, imho — Dirk McQuickly, Sep 12 '12 at 17:24

score 1 · Answer 1 · answered Sep 12 '12 at 17:18

SELECT
    /* Anything you want from the User table */
    /* CASE statement that checks if prod.phone_number is not null which means a product was present */
FROM
    User user_new
    JOIN User user_can
    ON user_new.phone_number = user_can.phone_number
    LEFT JOIN Product prod
    ON prod.phone_number = user_new.phone_number
WHERE
    (prod.Date IS NULL) OR -- Either there was no record.
    (prod.Date BETWEEN user_new.Date AND user_can.Date) -- or a product existed between the two dates.

Hi Vikdor, thanks I will give this a shot in the morning and let you know how it goes. You help is much appreciated. — StefanHanotin, Sep 12 '12 at 17:19

score 1 · Accepted Answer · answered Sep 12 '12 at 17:19

You need to start by self-joining the User table to find the created and cancelled dates. Then join to the Product table to see if anything is there.

SELECT u.Phone_Number, u.Created, u.Cancelled, p.Date as 'ProductDate'
FROM (
    SELECT u1.Phone_Number, u1.Date as 'Created', u2.Date as 'Cancelled'
    FROM User u1
      JOIN User u2 on u1.Phone_Number=u2.Phone_Number
    WHERE u1.Type = 'N'
      and u2.Type = 'C'
  ) u
  LEFT JOIN Product p on u.Phone_Number = p.Phone_Number
                     and p.Date between u.Created and u.Cancelled

thanks for the reply chezy and the explanation seems good. I will try this. — StefanHanotin, Sep 12 '12 at 17:24

score 0 · Answer 3 · answered Sep 12 '12 at 17:20

It doesn't sound like your table will have the required data to determine the difference between the date the user is created and cancelled if you're only storing one date field. But if you had 2 columns, user.createdDate and user.cancelledDate you could use SELECT DATEDIFF(user.createdDate, user.cancelledDated) AS DiffDate I don't know if DATEDIFF works with datetime objects, I've used it with plain dates formatted like "year-mm-dd" and it will give you a Query result object with DiffDate, an integer of the number of days. If user.cancelledDate is before user.createdDate, you will get a negative integer.

MySQL Calculating The Difference Between two dates but with a twist

3 Answers3