### 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

10|10000 characters needed characters exceeded

• 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')
```
10|10000 characters needed 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

• 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