SQL & PL/SQL

Something like this:

SQL> with mm_range as
  2    (
  3            select  to_date('010810','ddmmyy') + (rownum - 1) as dt
  4            from    dual
  5            connect by level <= to_date('310810','ddmmyy') - to_date('010810','ddmmyy') + 1
  6    )
  7    select count(case when to_char(dt,'dy','nls_date_language=english') = 'sun' then 1 else null 
end) as sunday_count
  8    from    mm_range
  9  /

SUNDAY_COUNT
------------
           5

Try this:

SQL> ed
Wrote file afiedt.buf

  1  SELECT TO_CHAR(dat,'MON-RRRR'),COUNT(*) FROM
  2  (SELECT TRUNC(SYSDATE,'MM') + level - 1 dat FROM dual
  3  connect by level <= LAST_DAY(TRUNC(SYSDATE)) - TRUNC(SYSDATE,'MM') + 1)
  4  WHERE TO_CHAR(dat,'DY') = 'SUN'
  5* GROUP BY TO_CHAR(dat,'MON-RRRR')
SQL> /

TO_CHAR(DAT,'M   COUNT(*)
-------------- ----------
AUG-2010                5

SQL> 

This should work too, although I haven't extensively tested it.

SQL> var mnth_dt VARCHAR2(10);
SQL> exec :mnth_dt := '08/01/2010';

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*) AS num_sundays
  2  FROM   dual
  3  CONNECT BY LEVEL <= (LAST_DAY(TO_DATE(:mnth_dt,'MM/DD/YYYY')) - NEXT_DAY(TRUNC(TO_DATE(:mnth_dt,'MM/DD/YYYY')) - 1,'SUNDAY'))/7 + 1
  4  /

         NUM_SUNDAYS
--------------------
                   5

Thanks to all for your input.


Other than turning this into a function, is there anyway to get the result without using connect by level - which I suspect will struggle on the volume of data in the table I am looking at.


thanks,


Robert.

The connect by is used to generate dates for the specified month, and is separate to your data, so it shouldn't matter how many rows you have in your table.

If you're not sure how to fit the above solutions into your scenario, you'll need to provide sample input/output data and the logic of what you're trying to achieve.

Slight variation:

SQL> select count(*) from dual connect by level <= 5
group by trunc(next_day(trunc(sysdate,'mm')-1, 'sunday') + (level -1)*7,'mm')
having count(*) > 1
/
       COUNT(*)
---------------
              5
1 row selected.

Maybe if you can post your table structure as well as some sample data to represent the problem we could come up with a more efficient solution. To my knowledge the only way to get this information is to loop through all the days in a month as the other two posters did, or use a subset of the days like I did.

If you can identify actual volumes of data that would probably help as well.

Try this:

SQL> ed
Wrote file afiedt.buf

  1  SELECT TO_CHAR(dat,'MON-RRRR'),COUNT(*) FROM
  2  (SELECT TRUNC(SYSDATE,'MM') + rownum - 1 dat, rownum rn
  3   FROM all_objects where rownum <= TO_CHAR(LAST_DAY(SYSDATE),'DD'))
  4  WHERE TO_CHAR(dat,'DY') = 'SUN'
  5* GROUP BY TO_CHAR(dat,'MON-RRRR')
SQL> /

TO_CHAR(DAT,'M   COUNT(*)
-------------- ----------
AUG-2010                5

SQL> 

Hi, Robert,

A clearer and more efficient way:

SELECT	CASE
		WHEN  TO_CHAR ( LAST_DAY (SYSDATE)
			      , 'Dy DD'
			      , 'NLS_DATE_LANGUAGE=ENGLISH'
			      ) IN ( 'Sun 29'
				   , 'Sun 30', 'Mon 30'
				   , 'Sun 31', 'Mon 31', 'Tue 31'
				   )
		THEN  5
		ELSE  4
	END	AS sundays_in_month
FROM    dual;

but it's not likely that you'll notice the difference, and a solution that generates all the dates can easily be adapted to finding the number of Sundays in a quarter, a year, or an arbitrary period.