Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Number of weekdays in a month

thinkingeyeJul 30 2010 — edited Jul 30 2010
Hi All
I asked this question in my previous post, please bear with me on this as this question is almost same with a slight variation. In my last post i said i wanted the count of weekdays for each month prior to the date the query is run, but now i just want the solution to get the count of weekdays in a month. I have the following query:
SELECT   TO_CHAR (TRUNC (date_read, 'mm'),
                  'MON-YYYY',
                  'nls_date_language=english'
                 ) month_year,
                

         COUNT (CASE
                   WHEN speed_avg <= 45
                   and speed_avg!=0
                   and vol_sum!=0
                   AND lane_type = 'm'
                   AND (hour_read || qtrhr_read) IN
                          ('61', '62', '63', '64', '71', '72', '73', '74', '81', '82', '83',
             '84','153', '154', '161', '162', '163',
             '164', '171', '172', '173', '174', '181', '182')
                      THEN 1
                END
               ) count_rows_less_than_45,
         (  COUNT (CASE
                      WHEN speed_avg <= 45
                      and speed_avg!=0 
                      and vol_sum!=0
                      AND lane_type = 'm'
                      AND (hour_read || qtrhr_read) IN
            ('61', '62', '63', '64', '71', '72', '73', '74', '81', '82', '83',
             '84','153', '154', '161', '162', '163',
             '164', '171', '172', '173', '174', '181', '182')

                         THEN 1
                   END
                  )
          / COUNT (DISTINCT asset_id||direction)
          / 24
         ) count_ratio,
         COUNT (DISTINCT asset_id||direction)
    FROM its_sensor_15min
Where TO_CHAR (date_read, 'fmday') NOT IN ('saturday', 'sunday')
GROUP BY TRUNC (date_read, 'mm')
All i need is count of weekdays so that i can divide the count_ratio in the sql with count of weekdays per each month, in my earlier all the users gave me invaluable ideas and approach user SBH created a function and asked me to call it in my query but for some reason its taking forever ( i have index on the field). Frank greatly assisted me as well, but please if anyone else can find a solution or direct me in a direction it would be great.

Thanks
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 27 2010
Added on Jul 30 2010
19 comments
2,446 views