Add months equal to a field value to another field

Question

I'm trying to write a query to extract the rows "not expired"

I have a database with a table in which are there :

IdActivity    lifespan (months)

And another table in which I have:

IdSubject    IdActivity    StartDate

Now I want to extract all IdSubject with not expired yet activity

I need something like (I know it's wrong: it's only to make clear what I want):

SELECT IdSubject FROM Tab2 INNER JOIN Tab1 USING(IdActivity)
WHERE DATEADD(StartDate, INTERVAL lifespan MONTS)>NOW()

I made many attempts but nothing worked!

Thanks in advance for your time.

Can't you save expire date instead of lifespan? Then you can easily compare startdate with expiredate. — Olle Härstedt, Mar 20 '15 at 18:11
Sorry, I thought the opposite and reversing thought I solved: `SELECT IdSubject FROM Tab2 INNER JOIN Tab1 USING(IdActivity) WHERE StartDate > NOW - INTERVAL (Select lifespan ......) MONTH ` — genespos, Mar 20 '15 at 18:17

Bohemian · Accepted Answer · 2015-03-20T18:34:32.980

0

You were really close - just one character wrong: Use < NOW() instead of > NOW() to find expired dates.

SELECT IdSubject
FROM Tab2
JOIN Tab1 USING(IdActivity)
WHERE DATE_ADD(StartDate, INTERVAL lifespan MONTH) < NOW()

edited Mar 20 '15 at 18:34

answered Mar 20 '15 at 18:17

Bohemian

You were really close too ;-) – Strawberry Mar 20 '15 at 18:24
Maybe it's my fault but I can't make it work in this way. In any case I solved as I wrote in a comment to my post. Thanks for your time. – genespos Mar 20 '15 at 18:26
@Strawberry missing the underscore? :) – Bohemian Mar 20 '15 at 18:29
@genespos Well, follow the link to the SQLFiddle to see this query working (note that there was a minor error in my initial version). – Bohemian Mar 20 '15 at 18:36

1 Answers1