Query + Variable problem
-
Hey all, I have a need to create a retention report in mysql/sql server that I can use to find out how many customers have canceled within 30,60,90 days of their sale date. So my query is something like this in mysql:
SELECT t1.sale_date, t1.primary_phone, t1.cancel_date FROM n.orders as t1 where t1.sale_date between "2007-08-01" and "2007-08-31" order by t1.date_enteredI need to be able to say something like and t1.cancel_date <= 30 days + sale_date. Does this make sense? I'm not sure how to do this without adding a more complex script, but you guys are the experts! Please help! Thanks -
Hey all, I have a need to create a retention report in mysql/sql server that I can use to find out how many customers have canceled within 30,60,90 days of their sale date. So my query is something like this in mysql:
SELECT t1.sale_date, t1.primary_phone, t1.cancel_date FROM n.orders as t1 where t1.sale_date between "2007-08-01" and "2007-08-31" order by t1.date_enteredI need to be able to say something like and t1.cancel_date <= 30 days + sale_date. Does this make sense? I'm not sure how to do this without adding a more complex script, but you guys are the experts! Please help! ThanksIn SQL-Server you would use the DateAdd function to add 30 days.
-
Hey all, I have a need to create a retention report in mysql/sql server that I can use to find out how many customers have canceled within 30,60,90 days of their sale date. So my query is something like this in mysql:
SELECT t1.sale_date, t1.primary_phone, t1.cancel_date FROM n.orders as t1 where t1.sale_date between "2007-08-01" and "2007-08-31" order by t1.date_enteredI need to be able to say something like and t1.cancel_date <= 30 days + sale_date. Does this make sense? I'm not sure how to do this without adding a more complex script, but you guys are the experts! Please help! Thanks -
Use the dateadd function
where t1.cancel_date <= dateadd("d", 30, sale_date)Hope it helpsHabetis bona deum
Thanks guys! I still have a problem with my formatting somewhere because in mysql I get the error "no database is selected". I played around with the query and got another error saying that "n.dateadd does not exist". It seems like I need some parenthesis or something. Does anyone know the specifics of how to use this function right in mysql? I've searched around, and I've had problem fitting it to my situation. Thanks all!
-
Thanks guys! I still have a problem with my formatting somewhere because in mysql I get the error "no database is selected". I played around with the query and got another error saying that "n.dateadd does not exist". It seems like I need some parenthesis or something. Does anyone know the specifics of how to use this function right in mysql? I've searched around, and I've had problem fitting it to my situation. Thanks all!
Ok so dateadd in mysql is just date_add! Doh! My syntax is still off though because I'm getting 1064 errors. I've got:
SELECT * FROM orders where date_entered between "2007-08-01" and "2007-08-31" and cxldate <> "0" and cxldate <= date_add('day', 30, date_entered)cxldate stands for cancel date I get this error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '30, date_entered)' at line 2 -
Ok so dateadd in mysql is just date_add! Doh! My syntax is still off though because I'm getting 1064 errors. I've got:
SELECT * FROM orders where date_entered between "2007-08-01" and "2007-08-31" and cxldate <> "0" and cxldate <= date_add('day', 30, date_entered)cxldate stands for cancel date I get this error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '30, date_entered)' at line 2