Calculating Business Days in Crosstab Crystal Report

Jun 13, 2017 at 02:43 AM


avatar image

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?


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


10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Ian Waterman 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')
Show 1 Share
10 |10000 characters needed characters left characters exceeded

Above works for Oracle only.

MSSQL this link will give you some guidance but I can not find anything similar to above



Ian Waterman 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


Show 1 Share
10 |10000 characters needed characters left characters exceeded

How to add working days using SQL?

Thanks Ian

Skip to Content