Pivot select Query for Report
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Pivot select Query for Report

  1. #1
    Join Date
    Dec 2002
    Posts
    110

    Pivot select Query for Report

    Hi All

    Want your comments on this Pivot select Report query

    I have a table called Agent. Each Agent processes requests.
    So accordingly Request is another table.

    Now the Agent can process the request successfully, fail or it could be work in progress.
    So accordingly I have a status column in the Request table which can have the following values

    New - when the request is allocated to the agent
    Open- When the agent opens the request
    Sent- when the agent acted upon the request
    Dead - when the request is no longer a prospect

    In the Request table there is column called requestDate which is the date when the Request was first inserted (i.e when its New)

    Now whenever the agent acts upon the request from the front end
    the status change is logged in a RequestLog(Activity) table.
    So when the agent changes the status to Open, an entry is logged into Activity table.
    So when the agent open the request an entry in the Activity table would go with the status as open , the reference of the requestId & the date on which the status is changed
    Similar entries will be logged for each request when the agent acts on the respective requests like if its Sold then the status will be sold,the date of sale & the corresponding request Id will be logged into the Activity table.

    In the Request table the status will be changed to "Open" or "Sent" or "Sold" etc but the requestDate will not change & it will remain the date when it was inserted

    Now I want to generate a Report which shows the list of all agents within a state with all total number of requests they have handled, how many are in Opened status,
    how many are in Sold status & how many are in Dead status. I also have to show the average time(in days) it took for each agent to Open the request, make the sale or qualify it
    as Failed. This average time I want it for an agent & not request specific.

    & In the end I have to show the grand total of all the requests handled by all the agents,grand total of opened status requests,grand total of Sold status requests,grand total of Fail status requests as well as the total average time it took to Open ,total average time it took to sale & total average time it takes to Fail.

    This is my first query which returns the total count of request per dealer, how much are in open, sent & dead state & total per status.
    *************************************
    select AGENT.COMPANY_NAME,count(REQUEST.REQUEST_ID) TOTAL ,
    sum(decode(REQUEST.status,'Open',1,0)) OPEN,
    sum(decode(REQUEST.status,'Sent',1,0)) SENT,
    sum(decode(REQUEST.status,'Dead',1,0)) DEAD
    from AGENT, REQUEST
    where
    AGENT.STATE='ACT'
    and REQUEST.REQUEST_TYPE='B'
    and REQUEST.CONFIRMATION='Y'
    and AGENT.DEALER_ID = REQUEST.DEALER_NUMBER
    group by grouping sets(AGENT.COMPANY_NAME, ())
    ************************************************************
    Then I have to retrieve the average number of days it takes to change the request to Open, average number of days it takes to change the request to Sent & average number of days it takes to change the request to Dead
    per Agent. The base date is stored in REQUEST table (request_date column) & the subsequent change of status is stored in activity_date column
    of ACTIVITY table

    Here is what I do get the average number of days it takes to change the status for the Agent
    ************************************************************************
    select AGENT.COMPANY_NAME,
    avg( decode (REQUEST.status,'Open',ACTIVITY.ACTIVITY_DATE - REQUEST.REQUEST_DATE,0)) AVG_OPEN,
    avg( decode (REQUEST.status,'Sent',ACTIVITY.ACTIVITY_DATE - REQUEST.REQUEST_DATE,0)) AVG_SENT,
    avg( decode (REQUEST.status,'Dead',ACTIVITY.ACTIVITY_DATE - REQUEST.REQUEST_DATE,0)) AVG_DEAD
    from REQUEST, ACTIVITY, AGENT
    where
    AGENT.STATE='ACT'
    and REQUEST.REQUEST_TYPE='B'
    and REQUEST.CONFIRMATION='Y '
    and REQUEST.dealer_number = AGENT.DEALER_ID
    and REQUEST.STATUS = ACTIVITY.STATUS
    group by grouping sets(AGENT.COMPANY_NAME, ())

    ********************************************************************************
    I hope this is the right way for calculating average number of days

    Since most part of the queries is the same
    I tried to merge both the queries into one because I need to render the report in that fashion. But the moment I merge the query its giving absurd results.

    Do post your thoughts on the same


    Regards

  2. #2
    Join Date
    Nov 2003
    Location
    Ohio
    Posts
    51
    Assuming your two separate queries work as inteneded, how about something like this ---

    select a.agent, a.company_name, a.total, a.open, a.sent, a.dead,
    b.avg_open, b.avg_sent, b.avg_dead
    from
    (
    select AGENT.COMPANY_NAME,count(REQUEST.REQUEST_ID) TOTAL ,
    sum(decode(REQUEST.status,'Open',1,0)) OPEN,
    sum(decode(REQUEST.status,'Sent',1,0)) SENT,
    sum(decode(REQUEST.status,'Dead',1,0)) DEAD
    from AGENT, REQUEST
    where
    AGENT.STATE='ACT'
    and REQUEST.REQUEST_TYPE='B'
    and REQUEST.CONFIRMATION='Y'
    and AGENT.DEALER_ID = REQUEST.DEALER_NUMBER
    group by grouping sets(AGENT.COMPANY_NAME, ())
    ) a,
    (
    select AGENT.COMPANY_NAME,
    avg( decode (REQUEST.status,'Open',ACTIVITY.ACTIVITY_DATE - REQUEST.REQUEST_DATE,0)) AVG_OPEN,
    avg( decode (REQUEST.status,'Sent',ACTIVITY.ACTIVITY_DATE - REQUEST.REQUEST_DATE,0)) AVG_SENT,
    avg( decode (REQUEST.status,'Dead',ACTIVITY.ACTIVITY_DATE - REQUEST.REQUEST_DATE,0)) AVG_DEAD
    from REQUEST, ACTIVITY, AGENT
    where
    AGENT.STATE='ACT'
    and REQUEST.REQUEST_TYPE='B'
    and REQUEST.CONFIRMATION='Y '
    and REQUEST.dealer_number = AGENT.DEALER_ID
    and REQUEST.STATUS = ACTIVITY.STATUS
    group by grouping sets(AGENT.COMPANY_NAME, ())
    ) b
    where a.agent = b.agent
    and a.company_name = b.company_name;
    ____________________
    Pete

  3. #3
    Join Date
    Dec 2002
    Posts
    110

    Regarding merging the two queries

    Hi Rigatoni

    Thanks for your response. Though the merging of the two queries worked but now the running totals which was getting calculated when the queries were executed individually is not available when I merge the 2 queries
    How can I retain the running totals

    Regards

Posting Permissions

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


Click Here to Expand Forum to Full Width