Results 1 to 1 of 1
  1. #1
    Join Date
    Dec 2012
    Posts
    63

    Unanswered: Calculating time across records and grouping them

    Hello all!

    I have two related problems that I am trying to find a solution to.

    We have a service desk system which we want to query and understand how long my group is in ownership of request.
    We want to be able to calculate the time in which we hold the request and the time in which we do not hold the request.

    The ideal scenario would be a list of requests which includes the open and closed time, the difference between them and the time it was within our responsibility and the time that it wasnt within our responsibility. I will give an example of what I have and what I would like.

    No code has to be inserted here.

    The result is the following
    No code has to be inserted here.

    this calculation was done in Excel and its not too bad to do because its one record, however I do not wish to show the entire history of the record, only the final result's... like so:

    No code has to be inserted here.


    The SQL for the first table is as follows:
    (NOTE: The "Time Taken" and "Group Handled Ticket #" were added by me to show you guys what I wanted to create in the end.)

    Code:
    SELECT
    	BTT_HPDR_HELP_DESK.Request_Number,
    	dbo.fn_adjusted_date(BTT_HPDR_HELP_DESK.Reported_Date),
    	BTT_HPDR_HELP_DESK.Assigned_Group,
    	dbo.fn_adjusted_date(HPDR_ChangedFields.MODIFICATION_DATE),
    	BTT_CHGT_FIELDS_STATUS.Request_STATUS,
    	HPDR_ChangedFields.TM_AssignedGroupBefModif,
    	HPDR_ChangedFields.ASSIGNEDGROUP
    FROM
    	(SELECT DISTINCT 
    			field_enum_values.enumId,
    			field_enum_values.value AS Request_Status
    	FROM   	field_enum_values
    	WHERE  	field_enum_values.fieldId = 700000001
        AND		field_enum_values.schemaId = (SELECT schemaId FROM arschema WHERE viewname = 'HPDR_ChangedFields') 
    	) as BTT_CHGT_FIELDS_STATUS
    	
    RIGHT OUTER JOIN HPDR_ChangedFields
    	ON (BTT_CHGT_FIELDS_STATUS.enumId=HPDR_ChangedFields.RequestStatus)
    RIGHT OUTER JOIN (SELECT * FROM HPD_Help_Desk) as BTT_HPDR_HELP_DESK
    	ON (BTT_HPDR_HELP_DESK.Request_Number=HPDR_ChangedFields.RequestID)
    WHERE
      BTT_HPDR_HELP_DESK.Request_Number  =  'REQ0000231556'
    For me, there are two issues here that I cannot resolve:

    1. obtaining this "Time Taken" field.
    I did this by taking the [modification date] - [modification date of previous record] except for the first record which I did [modification date] - [Request Reported Date] I do not know if it is possible to calculate this using SQL

    2. Grouping them together so that I would be able to show them on just one line per incident.

    obtaining either of the answers would help a great deal towards the resolution.

    Thanks very much for your guys help!


    EDIT: It appears that the BBCODE for tables does not work? I am unable to create a table using BBCODE, I will be working on uploading an excel or something
    EDIT 2: Attached below!
    Book1.zip
    Last edited by penfold1992; 01-20-16 at 09:16. Reason: BBCODE for tables not working

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •