Specific Query Based on Date Difference

[bdmovies]

I need 3 queries. I can figure out the 1st one, but need help with the other 2.

 

I've got a column that stores the dateReceived of a job. I need to get total number of jobs where the dateReceived is greater than 10 days ago.  I also need the same thing for jobs received more than 7 days ago but less than 10. And last but not least I need all the jobs received 7 days or less. I think I've got 1 query done so far.

 

SELECT COUNT(DATEDIFF(serviceJob.dateReceived, DATE_SUB(CURRENT_DATE(), INTERVAL 10 DAY))) AS over10 FROM serviceJob WHERE serviceStatus = '0'

 

To be honest, I'm sure there is a much better way to do this, but I'm having quite a bit of trouble. Also, I have no idea how to go about the greater than 7 under 10 query.

[fenway]

Does this help?  You might as well use multiple queries... or subqueries if you prefer.

 

I need to get total number of jobs where the dateReceived is greater than 10 days ago.

SELECT COUNT(*) FROM .... WHERE serviceJob.dateReceived < CURDATE() - INTERVAL 10 DAY

 

I also need the same thing for jobs received more than 7 days ago but less than 10.

SELECT COUNT(*) FROM .... WHERE serviceJob.dateReceived BETWEEN CURDATE() - INTERVAL 10 DAY AND CURDATE() - INTERVAL 7 DAY

 

And last but not least I need all the jobs received 7 days or less.

SELECT COUNT(*) FROM .... WHERE serviceJob.dateReceived >= CURDATE() - INTERVAL 7 DAY

 

[sniperscope]

Does this help?  You might as well use multiple queries... or subqueries if you prefer.

 

I need to get total number of jobs where the dateReceived is greater than 10 days ago.

SELECT COUNT(*) FROM .... WHERE serviceJob.dateReceived < CURDATE() - INTERVAL 10 DAY

 

I also need the same thing for jobs received more than 7 days ago but less than 10.

SELECT COUNT(*) FROM .... WHERE serviceJob.dateReceived BETWEEN CURDATE() - INTERVAL 10 DAY AND CURDATE() - INTERVAL 7 DAY

 

And last but not least I need all the jobs received 7 days or less.

SELECT COUNT(*) FROM .... WHERE serviceJob.dateReceived >= CURDATE() - INTERVAL 7 DAY

 

I want to ask you, what if i want to get date greater then 60 days which means one year earlier.

Sorry for bad english, let me i try to explain.

I want to get person who registered in LAST 60 days, but we are January now so i need get the data from 2008/11/21 to 2009/01/21. with your query i can get only 4 members instead of 12 members.

[fenway]

Then use the last query and switch 7 to 60.