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