quick date_sub question

[jdulberg]

At the start of every month I'm running a script that outputs customer names and how much they owe per month. What I'm trying to do now is add a one time setup fee for each customer. So I was thinking of using mySQL's IF and DATE_SUB to determine whether or not the person signed up within the month and add the setup fee if they do.

Here's what I have but I don't think my logic is correct. I'm having trouble combining mySQL's IF and DATE_SUB together.

SELECT agentname, signup,
IF((DATE_SUB(CURDATE(),signup <= INTERVAL 1 MONTH),1,0) AS new_customer
FROM agents; 

I based my query from code given here

Am I on the right track here? Is an IF/DATE_SUB even appropriate for what I'm trying to do?

I'm assuming that $signup should be in TIMESTAMP(14) format - correct?

As always... thanks for any help

[freddydoesphp]

Yes your query is a little bit off

Should be something like

SELECT agentname, signup,
IF((CURDATE() - INTERVAL 1 month) <= signup,1,0) AS new_customer
FROM agents; 

[jdulberg]

Thanks Freddy!!
guess I was close... sort of.

I tried your code but it gave me an error:

You have an error in your SQL syntax near '1 month) <= signup,1,0) AS new_customer
FROM agents' at line 2

The signup field is defined as TIMESTAMP(14)

Thanks again!

[freddydoesphp]

Hmm... Should work in MySQL 3.23 at least here this should do the trick.

SELECT agentname, signup, 
IF(DATE_SUB(CURDATE(), INTERVAL 1 month) <= signup,1,0) AS new_customer FROM agents; 

[jdulberg]

Awesom, it worked! I just checked and I have mySQL version 3.22.32 installed... looks like I should be upgrading.

Thanks again for your help!