1

I have SQL query which returns average utilization for some worker for previous year.

SELECT Name, AVG (hsum)
FROM
(
    SELECT Name,sum((number_hours)/8)*100 AS hsum
    FROM
    T1
    WHERE name='PERSON_A' and bookeddate>='2012-01-01' and booked_date<='2012-12-31'
    GROUP BY name,booked_date
) t

Now I want to exclude weekends for booked date for calculation? how to do it? I am using mysql thank you

Stefke
  • 141
  • 8
  • 19
  • Does this help you? http://stackoverflow.com/questions/1828948/mysql-function-to-find-the-number-of-working-days-between-two-dates – Cynical Jan 08 '13 at 07:47

1 Answers1

8

Add DAYOFWEEK() to your WHERE clause:

 AND DAYOFWEEK(booked_date) <> 1 AND DAYOFWEEK(booked_date)<>7
Bart Friederichs
  • 33,050
  • 15
  • 95
  • 195
  • Even if this is going to work, we are always generate a table with working days into it, and in query like this you join this table. It will solve problem of US holidays and vacations etc. – vittore Jan 08 '13 at 07:50
  • 1
    @vittore he asked for "exclude weekends", not get working days. The problem can be way harder, think people that do work in the weekends, bank holidays, etc. – Bart Friederichs Jan 08 '13 at 07:52