I need to fetch the cumulative sum for sales made by a marketeer per day, week and month. With the day I don’t have any problems running the following query:
SELECT SUM(sales_made)
FROM marketeer_sales
WHERE marketeer_id = ?
AND DATE(sales_date) = DATE(NOW())
Where I can’t get my head are the week and month queries. For the week I was thinking of using the following query:
SELECT SUM(sales_made)
FROM marketeer_sales
WHERE marketeer_id = ?
AND WEEKDAY(sale_date) >= 0
AND WEEKDAY(sale_date) <= 6
the problem i have with this is that the value of sales_made should be set to 0 (zero) at the end of the week, but I still need the old values to get the cumulative for the month (I hope this make any sense). How would you guys approach this? I have no idea where to start and/or end?
Should I use a cronjob to make this happen or are there other ways I could use?
AND WEEKDAY(sale_date) >= 0
AND WEEKDAY(sale_date) <= 6
To me this doesn’t make sense because WEEKDAY() always returns a value between 0 and 6 so this condition always evaluates to true and therefore is redundant.
Other than that I don’t understand what you are trying to accomplish. If you want the number of sales per specific week or month then why don’t you simply use this condition:
AND sale_date BETWEEN date1 AND date2
To me this is analogous to your first sample where you select sales from a single day.
Hi these queries should serve the telemarketing manager to see the results from the different telemarketeers, so clicking on a name, should give him the statistics day, per week and per month, The counting for each week should of course start at 0 again at the beginning of the week but he should still be able to see the results from the weeks before but I also need the sales made per month, Like I said I have no idea how to tackle this. How would you handle this? I know I need an extra table but how set this up, I have really no idea?
you just need a more accurate WHERE condition to isolate the week you want
did you by any chance want the current week? i.e. sales from the first day of the current week, which is a sunday, up to and including today’s date, whatever it is?
so with today being sunday sept 21, for the weekly report you’d get just today’s sales
or did you perhaps mean in the last 7 days? this would give you sales from monday sept 15 up to and including today
you see why you have to be really precise in your ~requirements~ before attempting to write the sql, right?
I understand what you say Rudy, but I can’t get my head around how to handle this, since this should be a returning feature. So how should the query looks like I without hard coding the dates. For example If the Manager click on a marketeers name he will get a dropdown where he could choose for the sales per week and the sales per month. Lets say he chooses for the sales per week he should get a listing like:
SELECT SUM(sales_made), YEAR(sales_date) AS year_number, WEEKOFYEAR(sales_date) AS week_number
FROM marketeer_sales
WHERE marketeer_id = ?
GROUP BY year_number, week_number
ORDER BY year_number, week_number
Then the only thing left would be to convert year_number and week_number to a range of dates for display but this should be pretty easy either in your client language (like php) or with a mysql function. You may also look at the WEEK() function as an alternative to WEEKOFYEAR().
By the same pattern you can select and group by MONTH(sales_date) to get calculations per each month.
EDIT: Added YEAR for date ranges spanning multiple years.
Can I ask you one question about the above reply. I have been thinking about this earlier reply today. If this query doesn’t make sense, how would a query per week should look like in such a way that the week start on Monday and ends on Sunday
Sorry for my ignorance, but I always have problems with queries where dates are involved.
If you want a query that selects results per week then you need to group by weeks. You cannot simply use a condition in the WHERE clause because then at best you will be able to sum up number of sales on all Mondays, Tuesdays, etc. or - like in the example above - on all days of week (all year round), which means all days - and then you will always receive just one row whereas you want several rows each representing a separate week. You need to change the date to a value that will uniquely identify each period from Monday to Sunday within a year and group by that value. The query I posted above does just that - WEEKOFYEAR function effectively splits the date into one-week chunks each of which starts on a Monday and ends on a Sunday. A similar WEEK() function can be used instead if you want some more control of how the periods are split into chunks - for example, you might want a week to be Sunday-Saturday. If you want some other fancy splitting like Wednesday-Tuesday then you’d have to write your own function (or an equivalent logic built into the query) that will do just that.
