Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2013
    Posts
    1

    Unanswered: Should be a simple sub query - I thought

    Hi all,

    I am new to this forum so sorry if I've posted this question in the wrong place. I have tried searching but I don't quite know how to ask the question in the search engine.

    So here goes.

    The company I work for has recently deployed an off the shelf third part datamart for it's call centre. It is only new and we've had some issues so I don't want to go playing with it's structure just yet and are trying to use the default structure to achieve what I'm trying to.

    I have 4 tables: 1 fact, 2 dim and 1 linking table.

    The fact contains the detail about the call, call_ID, Resource that managed the call, measurements about the call.

    Dim 1 contains the details about the resource: name, staff_id
    Dim 2 contains details about the groups that the resource belongs to: group name, group type

    The link table contains 2 foreign keys to link the resource to the group type (since the resource can belong to many groups). It also contains audit keys on when the resource key was given a different group key indicating the resource moved from one group to another.

    The challenge I have is I need use dim2 and the link table to return the most recent group changed based on a filter and then use the resource key from the same link table to link display the resource details. The fact table is then linked to the resource table by the resource key to show the details of the call.

    Usually when I'm querying this data I am selecting the groups i am interested in but for this one query I need to see how many calls have been transferred to another group (for which I don't know the value). And because the resource can belong to multiple groups (for historical reporting) I am getting multiple results.

    What I am doing is limiting all of the group values from dim2 to only those with the string 'Team' in them at a specified location (substr(group_name,4,4)='Team').

    I then link those this subquery back to the link table and do another subquery to return the audit key value for when changes occurred.

    I then want to return the maximum value for the audit key and I think this is where I'm coming unstuck. Because I have the group key each value is unique so I'm getting multiple maximum values.

    I'm just not sure how to limit the dim2 values and then calculate the maximum audit key value.

    here is a sample of the SQL I'm using.

    SELECT AG_Grp_2.AGENT_NAME AS XFER_INIITATED_AG,
    AG_Grp_2.GROUP_NAME AS XFER_INIITATED_GRP,
    TRUNC(fact1.START_TS_TIME) AS XFER_INITIATED_DATE,
    fact1.RECEIVED_FROM_IXN_RESOURCE_ID AS XFER_INITIATED_KEY,
    TO_CHAR(fact1.START_TS_TIME, 'dd/mm/yy hh24:mi:ss') AS XFER_INITATED_DATE_TIME,
    AG_Grp_2.EMPLOYEE_ID AS XFER_INITIATED_ID
    FROM
    (dim2.GROUP_NAME,
    dim1.AGENT_NAME,
    res.Res_key,
    dim1.EMPLOYEE_ID
    FROM
    (link1.RESOURCE_KEY AS Res_key,
    MAX(link1.START_TS) AS MAX_TS_START
    FROM link1
    INNER JOIN link1
    ON link1.GROUP_KEY = dim2.GROUP_KEY
    WHERE SUBSTR(dim2.GROUP_NAME, 4, 4) = 'Team'
    GROUP BY link1.RESOURCE_KEY
    ) res
    INNER JOIN link1
    ON res.MAX_TS_START = link1.START_TS
    AND res.Res_key = link1.RESOURCE_KEY
    INNER JOIN dim1
    ON dim1.RESOURCE_KEY = link1.RESOURCE_KEY
    INNER JOIN dim2
    ON link1 = dim2.GROUP_KEY
    ) AG_Grp_2
    INNER JOIN fact1
    ON fact1.RESOURCE_KEY = AG_Grp_2.Res_key

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Since we don't have your tables or data, we can't do anything with the posted code.

    How will you, I, or anyone recognize that correct solution has been posted?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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