locked
Average Resolution Time Report RRS feed

  • Question

  • Hi

    I am trying to create a report in CRM and I need a bit of help. My manager asked me to create a report which shows the duration of each case in CRM from case creation to case resolution. I joined the tables and created the report in Visual Studio. I subtracted the time created from the time resolved and now we have a resolution time. My manager has now asked me to create a report which shows average time to resolution for a given period.

    For example, my manager would like to run a report which shows that today for all the cases logged in CRM, the case have been resolved within an average of five hours. I am not quite sure how to go about doing this.

    I would really appreciate any suggestions.

    Many Thanks
    Mark
    Wednesday, February 10, 2010 2:28 PM

Answers

  • Hi.

    Following script will give you average case resolve time:

    SELECT
    	Avg(DateDiff(minute, CRMAF_FilteredIncident.createdon, FilteredIncidentResolution.createdon)) AverageWorkInMinutes
    FROM
    	FilteredIncident AS CRMAF_FilteredIncident 
    	INNER JOIN FilteredIncidentResolution ON FilteredIncidentResolution.incidentid = CRMAF_FilteredIncident.incidentid
    WHERE     
    	(CRMAF_FilteredIncident.statecodename = 'Resolved') 
    	AND (CRMAF_FilteredIncident.casetypecodename = 'Requested by Client')
    

    Truth is opened the prepared mind

    My blog (english)
    Мой блог (русскоязычный)free countersLocations of visitors to this page
    Wednesday, February 10, 2010 2:54 PM
    Moderator

All replies

  • Hi, Mark.

    You can use AVG function of T-SQL. Check this url .

    Truth is opened the prepared mind

    My blog (english)
    Мой блог (русскоязычный)free countersLocations of visitors to this page
    Wednesday, February 10, 2010 2:35 PM
    Moderator
  • Hi

    Thanks so much for all your help. I would like to ask if you would please assist me with my SQL query.  I able to write basic SQL queries however this seems a bit out of my depth.

    My query currently looks like:

    SELECT DISTINCT
                          CRMAF_FilteredIncident.statecodename, CRMAF_FilteredIncident.customeridname, CRMAF_FilteredIncident.createdon, CRMAF_FilteredIncident.title,
                          CRMAF_FilteredIncident.owneridname, CRMAF_FilteredIncident.casetypecodename, CRMAF_FilteredIncident.ticketnumber, FilteredIncidentResolution.createdon
    FROM         FilteredIncident AS CRMAF_FilteredIncident INNER JOIN
                          FilteredIncidentResolution ON FilteredIncidentResolution.incidentid = CRMAF_FilteredIncident.incidentid
    WHERE     (CRMAF_FilteredIncident.statecodename = 'Resolved') AND (CRMAF_FilteredIncident.casetypecodename = 'Requested by Client')

    Where would I go about inserting the average function?

    Thanks very much. Really appreciate it.
    Mark
    Wednesday, February 10, 2010 2:46 PM
  • Hi.

    Following script will give you average case resolve time:

    SELECT
    	Avg(DateDiff(minute, CRMAF_FilteredIncident.createdon, FilteredIncidentResolution.createdon)) AverageWorkInMinutes
    FROM
    	FilteredIncident AS CRMAF_FilteredIncident 
    	INNER JOIN FilteredIncidentResolution ON FilteredIncidentResolution.incidentid = CRMAF_FilteredIncident.incidentid
    WHERE     
    	(CRMAF_FilteredIncident.statecodename = 'Resolved') 
    	AND (CRMAF_FilteredIncident.casetypecodename = 'Requested by Client')
    

    Truth is opened the prepared mind

    My blog (english)
    Мой блог (русскоязычный)free countersLocations of visitors to this page
    Wednesday, February 10, 2010 2:54 PM
    Moderator
  • Hi

    Thanks so much for your reply. I now have an idea as to how to go about this. Just one quick question though. I would like to show 'AverageWorkInMinutes' for a given time period with all the additional fields like CRMAF_FilteredIncident.customeridname, CRMAF_FilteredIncident.createdon, CRMAF_FilteredIncident.title, CRMAF_FilteredIncident.owneridname, CRMAF_FilteredIncident.casetypecodename, CRMAF_FilteredIncident.ticketnumber and FilteredIncidentResolution.createdon.

    Do I need to use a GROUP By clause?

    Thanks again
    Mark
    Thursday, February 11, 2010 6:07 AM
  • Hi

    I am start to get an idea as to how this works. I would not need all the columns listed in the post above but it would be nice if I could have number of cases, period and averageworkinminutes displayed in three seperate columns.

    Would this be possible?

    Thanks
    Mark
    Thursday, February 11, 2010 6:51 AM
  • HI

    I got the number of cases by inserting Count (*), in the begining of the query. I did'nt think it would be that easy.

    Thanks again
    Mark
    Thursday, February 11, 2010 6:59 AM
  • HI

    I got the number of cases by inserting Count (*), in the begining of the query. I did'nt think it would be that easy.

    Thanks again
    Mark
    Yes, Mark. Count(*) will give you number of records. Period you mean dates interval you work with?

    Truth is opened the prepared mind

    My blog (english)
    Мой блог (русскоязычный)free countersLocations of visitors to this page
    Thursday, February 11, 2010 7:11 AM
    Moderator
  • Ya, thats correct.

    Would be great if i could display this but not serious.

    Thanks
    Mark
    Thursday, February 11, 2010 7:30 AM
  • Could you place query you use with parameters (date interval) here. I'll try to help you.
    Truth is opened the prepared mind

    My blog (english)
    Мой блог (русскоязычный)free countersLocations of visitors to this page
    Thursday, February 11, 2010 7:33 AM
    Moderator
  • Not serious

    Thanks for all your help

    Really appreciate it.

    Thanks
    Mark
    Thursday, February 11, 2010 9:35 AM