(A) | (B) | (C)
Mark | 1 | 100
Mark | 2 | 90
Mark | 3 | 80
Kate | 1 | 70
Kate | 2 | 75
Kate | 4 | 90
John | 2 | 100
John | 3 | 95
John | 4 | 105
I'm trying to query a given week(B) for each employee(A) and trying to determine the AVG miles traveled(C) for the most recent week. The missing weeks are vacation weeks so I like to include the week before in the average where relevant:
e.g. - A query for week 3 would return:
Mark 3 80
Kate 2 75 (most recent)
John 3 95
=83.33
i was trying with the MAX function for col B but probably missing something simple
Mark | 1 | 100
Mark | 2 | 90
Mark | 3 | 80
Kate | 1 | 70
Kate | 2 | 75
Kate | 4 | 90
John | 2 | 100
John | 3 | 95
John | 4 | 105
I'm trying to query a given week(B) for each employee(A) and trying to determine the AVG miles traveled(C) for the most recent week. The missing weeks are vacation weeks so I like to include the week before in the average where relevant:
e.g. - A query for week 3 would return:
Mark 3 80
Kate 2 75 (most recent)
John 3 95
=83.33
i was trying with the MAX function for col B but probably missing something simple