CODEWITHSUNDEEP
mysqlsql
charlie
charlie

Reputation: 481

SELECT Query where date column is X ago where X is another column

I'm trying to run a query where a date column is so many days / months ago using NOW() - INTERVAL but the number of days / months is in another column called payment_period

SELECT * 
FROM customer_subscriptions 
WHERE 
    status = 'Active' AND (
        ( auto_renew = '1' AND expiry_date <= '".date("Y-m-d")."') 
        OR (last_charge NOW() - INTERVAL payment_period)
    )

Upvotes: 1

Views: 49

Answers (1)

GMB
GMB

Reputation: 222572

last_charge NOW() - INTERVAL payment_period

You are missing two things:

  • an operator between the dates to compare (>=)
  • a unit for the interval

Assuming that payment_period is expressed as days, you want :

last_charge >= NOW() - INTERVAL payment_period DAY

Another possibility is to store the unit in another column, and then :

last_charge >= CASE
    WHEN unit = 'DAY'   THEN NOW() - INTERVAL val DAY
    WHEN unit = 'MONTH' THEN NOW() - INTERVAL val MONTH
    -- ... other supported units ...
END

Upvotes: 2

Related Questions