The reason I am being asked to do it in SQL is because my manager did not like/agree with the results he saw from using the IncidentDurationIncidentDurationGetDuration() function in Crystal. (The code that comes in the IncidentDurationbyDays report.
He performed tests and thought that the CRUFL was not getting it right. So I have to reproduce it so that we can trace down problems if we get results that we don't expect.
I would sure love it if the CRUFL could be proven useful enough so that I could avoid all this, but I'm not really a "reports" user, and I don't know if I would be able to do any better than he did... thus I am tackling this in SQL.
I'm sorry but I've also found that the CRUFL does not calculate my incident duration correctly, and I don't even use stop the clock. Mostly I agree with the CRUFL calculation, but it gives incorrect results when dealing with Bank Holidays and/or weekends.
Is this even with the patched version they brought out?
i'm actually counting the incident duration by using the calculation of getbusinesstime, the result of the duration will be subtracted by the pending duration which exist in the main object (incident, request, change or problem that is). the pending duration is counting the start and stop clock duration with respect to the calendar, so by subtracting the duration of incident and pending duration i get a total exact incident duration, just one problem though is that the pending duration will only appear after the stop clock.