Calculating Business Days in Crosstab Crystal Report

I would appreciate if someone can help me to find solution.

To calculate TOTAL work days between two dates in crystal reports I am using DateDiff ("d", d1, d2) - (DateDiff ("ww", d1, d2, crSaturday) - DateDiff ("ww", d1, d2, crSunday)).

For example, there are Total 63 work days for January 2017-March 2017(based on formula above).

How can I distribute total work days across a number of months in crosstab?

Month………Jan17……….feb1………march17........Total

Work days…..21……………19……………23................63

Thanks

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Jun 14, 2017 at 07:57 AM
    WITH test_data AS
    (
      SELECT TO_DATE('01-JAN-17') AS start_date,
      TO_DATE('31-DEC-17') AS end_date
      FROM dual
    ), 
    
    
    all_dates AS 
    ( SELECT td.start_date, td.end_date, td.start_date + LEVEL-1 as week_day
      FROM test_data td
      CONNECT BY td.start_date + LEVEL-1 <=  td.end_date
    )
       
    SELECT TO_CHAR(week_day, 'MM') MonthNum, TO_CHAR(week_day, 'MON')MonthName,  COUNT(*)
    
    
      FROM all_dates 
     WHERE to_char(week_day, 'dy', 'nls_date_language=ENGLISH') NOT IN ('sun' , 'sat')
     GROUP BY TO_CHAR(week_day, 'MM'),TO_CHAR(week_day, 'MON')
     ORDER BY TO_CHAR(week_day, 'MM')
    
    Add comment
    10|10000 characters needed characters exceeded

  • Jun 13, 2017 at 09:39 AM

    I don't think you can do this in Crystal.

    Convert report to use a command and add working days using SQL

    Ian

    Add comment
    10|10000 characters needed characters exceeded

Skip to Content