Calculating points from number of days worked in a week/month by each employee

I am trying to calculate the average points for employees for a period of a week (or calendar month if possible) from a range of records. I have a table: daily_schedule which has records like this

I have tried the query:

SELECT WEEK(date)
,employeeid
,AVG(points)
FROM `daily_schedule`
GROUP BY WEEK(date), employeeid

But that is calculating the average on jobs rather than the date per employee The result I want to achieve is

Is there any way to achieve the average by week and/or calendar month?

Thank you in advance.#

Answer

The WEEK(date) function will give you the number of week in the year from that date. And if you want to achieve what you’ve mentioned here, you can simply group by WEEK(date) and then employeeid. You can execute a CTE in this purpose. Then select the aggregated grouped values from CTE and AVG from points/days collected from cte.

So you can execute:

with cte as (select employeeid, sum(points) as points, count(distinct date_val) as days, WEEK(DATE_VAL) as week_no
    from daily_schedule ds group by WEEK(date_val), employeeid)
select week_no, employeeid, points, days, points/days as avg from cte;

From the week no, you can identify which week of the year this is. This will give you the expected output.

Output of this query is:

week_no	employeeid	points	days	avg
26	113877	4.5	2	2.25
26	122396	4.2	2	2.1
26	124514	3.7	1	3.7
27	113877	1.8	1	1.8
27	122396	1.2	1	1.2
27	124514	1.6	1	1.6