weekly query

[mescalito ve]

Hello im new to the forum.

I need help in a weekly query for a mysql table. The table registers the visits that salesmans do to their respective costumers.

Now i need a report that gives out the number of visits made in any or various weeks of the year by each salesman

the date field has the YYYY-mm-dd format and the Db is Mysql 3.2.9

I will aprecciate any help
Thank you

[ZydoSEO]

Perhaps you should give more info about the table and fields - names, types, relationships... Otherwise, your question is very vague and hard to answer other than in generalities.

If you don't want to supply the specifics, then I'd suggest googling 'MySQL Date Functions' and looking at things like Week() & Weekofyear().

[phranque]

welcome to WebmasterWorld [webmasterworld.com], mescalito ve!

something like this should do the trick:
SELECT salesman,WEEK(date) AS weeknum,COUNT(*) AS visits FROM customer_visits GROUP BY weeknum, salesman;

[mescalito ve]

Thank you very much phranque i used the query that you worte and im going in the right way now. perhaps im having trouble in doing the layout of the data per salesman and week mostly because when COUNT(*) as visits is == 0 there is no value for that week so it doesn show me a 0 or something else just doesnt count that value.

i used an if verification using empty() but nothing happens.

Thank you both anyway

[phranque]

you could do two queries - first this one:
SELECT DISTINCT(salesman) FROM customer_visits;

then create the structure for all salesmen & weeks and fill it with the results of the 2nd query.

[mescalito ve]

Thank you again phranque but i cant see the way to solve it, i have to display the data, showing the clients visits per week in this way

week ¦ 1 ¦ 2 ¦ 3 ¦ 4 ¦ 5 ¦ 6 ¦ 7 ¦ 8 ¦
salesman1 ¦ ¦ ¦ ¦ ¦ ¦ ¦ ¦ ¦
salesman2 ¦ ¦ ¦ ¦ ¦ ¦ ¦ ¦ ¦

and so on depending in the number of weeks that has pased in the year.

Im almost done but im having a problem when "COUNT(*) AS total" finds that the salesman didnt do any visit in any week. The problem is that there is no value asigned to that, and if the salesmans didnt do any visit in week 2 and made 3 in week 3, the result for week 3 will locate in the week2 space.

i used an if verification using empty() but nothing happens

here is a sample of my query is in spanich but i think is understandable enough.

$result = mysql_query("SELECT *, COUNT(*) AS total, WEEK(fecha) as semana FROM visitas_2 WHERE id_comercial ='$row_comercial[id_comercial]' AND WEEK(fecha) >= 1 AND WEEK(fecha) <= $week_actual AND YEAR(fecha) = '2008' GROUP BY id_comercial, WEEK(fecha) ORDER BY WEEK(fecha),id_comercial ASC ");

thank you