/scripts/ahub.form.attachments.js
0

Calculating Business Days in Crosstab Crystal Report

Jun 13, 2017 at 02:43 AM

52

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?

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

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

Thanks

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

http://www.sqlservergeeks.com/find-working-days-between-two-dates-in-sql-server/

Ian

0
Ian Waterman Jun 13, 2017 at 09:39 AM
0

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

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

Ian

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

How to add working days using SQL?

Thanks Ian

0
Skip to Content