-- Compute cummulative business days in a week excluding weekend days (SAT, SUN) SELECT level day_of_month, TO_CHAR(TRUNC(SYSDATE,'MON')+level-1,'DY') day_of_week, TRUNC(SYSDATE,'MON')+level-1 month_date, CASE WHEN TO_CHAR(TRUNC(SYSDATE,'MON')+level-1,'DY') IN ('SAT','SUN') THEN 0 ELSE 1 END week_day_idx, SUM(CASE WHEN TO_CHAR(TRUNC(SYSDATE,'MON')+level-1,'DY') IN ('SAT','SUN') THEN 0 ELSE 1 END) OVER(PARTITION BY level) business_day FROM dual CONNECT BY level <= TO_CHAR(LAST_DAY(SYSDATE),'dd') ; -- Zero changed to NULL - business day count will still be the same SELECT level day_of_month, TO_CHAR(TRUNC(SYSDATE,'MON')+level-1,'DY') day_of_week, TRUNC(SYSDATE,'MON')+level-1 month_date, CASE WHEN TO_CHAR(TRUNC(SYSDATE,'MON')+level-1,'DY') IN ('SAT','SUN') THEN NULL ELSE 1 END week_day_idx, SUM(CASE WHEN TO_CHAR(TRUNC(SYSDATE,'MON')+level-1,'DY') IN ('SAT','SUN') THEN NULL ELSE 1 END) OVER(PARTITION BY level) business_day FROM dual CONNECT BY level <= TO_CHAR(LAST_DAY(SYSDATE),'dd') ;
52893