Oracle count days per month

I wrote this SQL statement to calculate the days for each month

(select count(*) DAYs FROM
    (
     select trunc(ADD_MONTHS(sysdate,-1),'MM') +  level -1 Dates from dual connect by
      level <= ADD_MONTHS(trunc(sysdate,'MM'),1)-1 -  trunc(sysdate,'MM')+1
   ) Where To_char(dates,'DY') NOT IN ('SA','SO'))

At the moment this statement ignores Saturdays and Sundays and it calculates the days from the month before the sysdate (June).

June has 22 days without weekends but sadly my statement says it has 23. I found out it includes the 1st July, which is wrong.

Do you know how I can tell my little statement it only calculates the days from the month I want to get not including days from another month?

Solution

Doing this sort of thing is always going to look not pretty... here's one way, which does it for the entire current year. You can restrict to a single month by adding an additional statement to the where clause:

 select to_char(trunc(sysdate, 'y') + level - 1, 'fmMON') as month, count(*)
   from dual
  where to_char(trunc(sysdate, 'y') + level - 1, 'fmDY', 'nls_date_language=english') not in ('SAT','SUN')
connect by level <= trunc(add_months(sysdate, 12), 'y') - trunc(sysdate, 'y')
  group by to_char(trunc(sysdate, 'y') + level - 1, 'fmMON')

As I said, not pretty.

Note a couple of things:

Use of the fm format model modifier to remove leading spaces
Explicit use of nls_date_language to ensure it'll work in all environments
I've added 12 months to the current date and then truncated it to the first of January to get the first day of the new year for simplicity
If you want to do this by month it might be worth looking at the LAST_DAY() function

The same statement (using LAST_DAY()) for the previous month only would be:

 select count(*)
   from dual
  where to_char(trunc(sysdate, 'y') + level - 1, 'fmDY', 'nls_date_language=english') not in ('SAT','SUN')
connect by level <= last_day(add_months(trunc(sysdate, 'mm'), -1)) - add_months(trunc(sysdate, 'mm'), -1) + 1