Skip to Content
avatar image
Former Member

Get average time in SAP Crystal Reports XI

Dear SAP Crystal Reports community,

I would like to get average time calculated in Crystal Reports XI.

This is design of Postgresql database and Crystal Reports that I working on:

1.All data and tables are on central server. Connected with ODBC driver.

2.From 1. all data and table are connected with SQL Manager for Postgresql. SQL Manager will help developers to see data in tables and query SQL Command. In this case is Postgresql SQL Command. It also uses to preview results of data before insert Postgresql SQL Command to Crystal Reports.

I start Crystal Reports. Then, insert Postgresql SQL Command to get data from Postgresql database.

I don’t know how to get average time value in Crystal Reports if I have to use only 1 (one) “SELECT FROM WHERE” statement and cannot use “INSERT” data into any database table.

The report I wish to display will have all of these columns. (This report display in 1-month long)

1.Full name of doctor

2.Sum of patient(s) in morning for current doctor

3.Average number of patient(s) in morning for current doctor

4.Sum of working day(s) in morning for current doctor

5.Sum of patient(s) in afternoon for current doctor

6.Average number of patient(s) in afternoon for current doctor

7.Sum of working day(s) in afternoon for current doctor

8.Sum of patient(s) in morning and afternoon

9.Average Time when current doctor start diagnoses patient

In column no. 9. I don’t know how to get data for this column.

This column will show average start time when current doctor start diagnoses patient. For example, if start time when current doctor start diagnoses patient looks like this:

1/5/2010 1/5/2010 8:16:54AM

1/6/2010 1/6/2010 7:36:54AM

1/7/2010 1/7/2010 7:30:54AM

1/8/2010 1/8/2010 7:31:54AM

1/9/2010 1/9/2010 7:35:54AM

This column will show: 7:30:54AM (7:30:54AM is an average diagnostic time).

I try to use 2 (two) SQL Command. One for gets column 1-8 and another one for get column no.9. But, if I do like this, the result will not on same conditions (WHERE statement conditions and current doctor name for each rows). I also try on this formula (https://archive.sap.com/discussions/message/9361825), but I still don’t know how to adapt the formula to this situation.

How to calculate “Average Time when current doctor start diagnoses patient” column?

This is Postgresql SQL Command that gets adequate information for column no. 1-8. This command successfully executed and gets results as expected.

SELECT a.prefix||' '||a.fName||' '||a.lName AS drFullName -- Displays Full Name of current doctor.

,SUM(CASE WHEN EXTRACT(HOUR FROM b.startTime) BETWEEN 8 AND 12

THEN 1 ELSE 0

END ) AS sumOfMorningPatient -- Extract hour digit from table that record when current doctor start diagnoses patient (Morning Session).

,SUM(CASE WHEN EXTRACT(HOUR FROM b.startTime) BETWEEN 13 AND 16

THEN 1 ELSE 0

END ) AS sumOfEveningPatient -- Extract hour digit from table that record when current doctor start diagnoses patient (Afternoon Session).

,COUNT(DISTINCT( CASE WHEN EXTRACT(HOUR FROM b.startTime) BETWEEN 8 AND 12

THEN DATE(b.startTime)

ELSE NULL

END )) AS sumOfWorkingMorningDayPerDr -- Extract number of working day(s) of current doctor (Morning Session).

,COUNT(DISTINCT( CASE WHEN EXTRACT(HOUR FROM b.startTime) BETWEEN 13 AND 16

THEN DATE(b.startTime)

ELSE NULL

END )) AS sumOfWorkingEveningDayPerDr -- Extract number of working day(s) of current doctor (Afternoon Session).

FROM emp a

INNER JOIN clinicVisit b ON a.empId = b.drId

WHERE b.startTime BETWEEN '2017-06-01 08:00:00' AND '2017-06-30 16:00:00'

GROUP BYa.prefix||' '||a.fName||' '||a.lName

This is Postgresql SQL Command that I added Postgresql SQL Command to get column no.9. It will disturb other “SELECT command”. Therefore, this command is not return results as expected.

SELECT a.prefix||' '||a.fName||' '||a.lName AS drFullName -- Displays Full Name of current doctor.

,SUM(CASE WHEN EXTRACT(HOUR FROM b.startTime) BETWEEN 8 AND 12

THEN 1 ELSE 0

END ) AS sumOfMorningPatient -- Extract hour digit from table that record when current doctor start diagnoses patient (Morning Session).

,SUM(CASE WHEN EXTRACT(HOUR FROM b.startTime) BETWEEN 13 AND 16

THEN 1 ELSE 0

END ) AS sumOfEveningPatient -- Extract hour digit from table that record when current doctor start diagnoses patient (Afternoon Session).

,COUNT(DISTINCT( CASE WHEN EXTRACT(HOUR FROM b.startTime) BETWEEN 8 AND 12

THEN DATE(b.startTime)

ELSE NULL

END )) AS sumOfWorkingMorningDayPerDr -- Extract number of working day(s) of current doctor (Morning Session).

,COUNT(DISTINCT( CASE WHEN EXTRACT(HOUR FROM b.startTime) BETWEEN 13 AND 16

THEN DATE(b.startTime)

ELSE NULL

END )) AS sumOfWorkingEveningDayPerDr -- Extract number of working day(s) of current doctor (Afternoon Session).

,b.startTime -- I try to get date time when current doctor start diagnoses patient. But, didn't get expected result because if this command executed, it will disturb other SELECT commands above.

FROM emp a

INNER JOIN clinicVisit b ON a.empId = b.drId

WHERE b.startTime BETWEEN '2017-06-01 08:00:00' AND '2017-06-30 16:00:00'

GROUP BYa.prefix||' '||a.fName||' '||a.lName

, b.startTime -- If b.startTime is selected, this group by statement must be included.

If possible, Is Crystal Report offer an array or temporary data container to place average time when doctor start diagnoses patient?

If yes, Is Crystal Report able to match those value to each doctor?

Thank you so much in advanced for help.

Best regards,

Chisanupong

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • avatar image
    Former Member
    Aug 07, 2017 at 02:06 PM

    Dear SAP Support,

    Thank you for let me post on this forum. This problem solved by perform calculation outside Crystal Reports first. By convert each HH:MM:SS to seconds. Then use this formula to solve it. (Formula no.2) )(https://archive.sap.com/discussions/message/9361825)

    Chisanupong.

    Add comment
    10|10000 characters needed characters exceeded