CODEWITHSUNDEEP
sqlexcelvbams-access
adam
adam

Reputation: 424

SQL Average only weekdays

I have a data table in MS Access as such:

[DateID]   |[NameID]|[ValueID]
01/01/2019 |Ben     |5
01/01/2019 |Ace     |6
01/01/2019 |Ken     |7
01/01/2019 |Jon     |10
.
.
.
03/01/2019 |Ben     |8
03/01/2019 |Ken     |55
03/01/2019 |Jan     |52

Basically multiple ValueID entries in a single day under different names with at least 100+ entries per day.

I need to draw out the average ValueID for weekdays across 2 specific date and the sql code is executed via Excel VB. So far the sql code have cannot differentiate between weekdays and weekends.

Code I have that works so far: SELECT AVG([ValueID]) FROM [TABLE] WHERE [DATEID] BETWEEN #10 Jan 2018# AND #15 Feb 2018#

Have scoured the around for a bit. The closest I've gotten is SELECT AVG([ValueID]) FROM [TABLE] WHERE ([DATEID] BETWEEN #10 Jan 2018# AND #15 Feb 2018#) AND DATENAME(WEEKDAY, [DATEID]) IN ('MONDAY','TUESDAY','WEDNESDAY','THURSDAY','FRIDAY')

However, it gives me an Undefined function 'DATENAME' in expression error. Not sure if I'm even using this function right if I'm totally honest.

I understand I could technically export the range of dates into the Excel sheet then exclude from there but it could potentially be a 10k+ data points export. Therefore would prefer to do it via sql as it looks cleaner that way.

Upvotes: 0

Views: 182

Answers (2)

Gustav
Gustav

Reputation: 55921

Never use literals, as these are localised, thus:

SELECT 
    AVG([ValueID]) 
FROM 
    [TABLE] 
WHERE 
    ([DATEID] BETWEEN #2018/01/10# AND #2018/01/15#) 
    AND 
    Weekday([DATEID], 2) < 6

In function Weekday, the parameter 2 is for Monday as the first day of the week and, then, 6 is Saturday.

Upvotes: 1

forpas
forpas

Reputation: 164139

You need WeekdayName and Weekday, so this will do it:

...AND UCase(WeekdayName(Weekday([DATEID], 2))) NOT IN ('SATURDAY','SUNDAY')

Maybe you need to change 2 to 1 (or other) to get the correct day name (2 stands for Monday as start of week).

Upvotes: 1

Related Questions