I hate dates, the worst is adding and subtracting them.

[virtuexru]

Hey guys. Mondays suck. I've been asked to write a script to determine how many vehicles have been added by employees in the past WEEK. So basically, I would need a count on employees for who had the most vehicles posted in the past one week.

 

My database is setup like this:

 

Row Name: _author can contain any persons e-mail for example:  "person1@whatever.com" or "person3@whatever.com".

 

Row Name: _added is the DATE the person posted the vehicle in this format: YYYY-MM-DD.

 

Now, how would I go about counting who had the most posts in the past week? Anyone have any ideas? I've tried a few things with mktime but alas, no avail.

 

Here is my SQL for vehicle postings for ALL-TIME (not one week) just for reference:

 

$count  = "SELECT DISTINCT _author, COUNT(_author) AS count FROM dbname GROUP BY _author ORDER BY count DESC LIMIT 10";

[corbin]

What is the data type of _added?

 

 

 

Anyway, assuming it's varchar (ewww..... use date or something else), you could do this:

 

 

SELECT _author, COUNT(*) as total_vehicles FROM table WHERE WEEKOFYEAR(DATE(_added)) = WEEKOFYEAR(NOW())-1 AND YEAR(DATE(_added)) = YEAR(NOW());

 

 

Question, by the way....  Do you want a week ago from the current day, or do you want a week as in Monday-Sunday (or Sunday - Saturday depending on how you look at it)?

 

 

I'm hoping someone will have a better query...  one that takes everything into account lol.

 

 

If you want a week from the current day, that's easy though.

[fenway]

You think mysql has poor date functionality? Seriously??? Try working with mssql.