Home » SQL & PL/SQL » SQL & PL/SQL » Pivot Select query for a Report
Pivot Select query for a Report [message #186445] Tue, 08 August 2006 00:31 Go to next message
zulu99
Messages: 4
Registered: August 2006
Junior Member
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
Re: Pivot Select query for a Report [message #187615 is a reply to message #186445] Mon, 14 August 2006 13:00 Go to previous messageGo to next message
markmal
Messages: 113
Registered: April 2006
Location: Toronto, Canada
Senior Member
I think you can get everything from a second query. However I do not see a complete join between REQUEST and ACTIVITY. Is it true that "and REQUEST.STATUS = ACTIVITY.STATUS" predicate completely identifies ACTIVITY rows? Other words, should it be there something like REQUEST.ID = ACTIVITY.REQUEST_ID as well?
Re: Pivot Select query for a Report [message #187840 is a reply to message #186445] Tue, 15 August 2006 23:14 Go to previous messageGo to next message
zulu99
Messages: 4
Registered: August 2006
Junior Member
Hi Markmal

You are right on that one it should be REQUEST.REQUEST_ID = ACTIVITY.REQUEST_ID.

But the problem is when I try to merge the two queries I get absurd results for total count, sum & averages.

Could you give me an insight how to go about merging the two queries


Regards
Re: Pivot Select query for a Report [message #187976 is a reply to message #187840] Wed, 16 August 2006 10:21 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Hi, with create and insert scripts for the tables, I could have tested it, but could it be you're looking for:

SELECT a.company_name
      ,AVG(decode(r.status
                 ,'Open'
                 ,ac.activity_date - r.request_date
                 ,0)) avg_open
      ,AVG(decode(r.status
                 ,'Sent'
                 ,ac.activity_date - r.request_date
                 ,0)) avg_sent
      ,AVG(decode(r.status
                 ,'Dead'
                 ,ac.activity_date - r.request_date
                 ,0)) avg_dead
      ,SUM(decode(r.status
                 ,'Open'
                 ,1
                 ,0)) over(PARTITION BY a.company_name) sum_open
      ,SUM(decode(r.status
                 ,'Sent'
                 ,1
                 ,0)) over(PARTITION BY a.company_name) sum_sent
      ,SUM(decode(r.status
                 ,'Dead'
                 ,1
                 ,0)) over(PARTITION BY a.company_name) sum_dead
FROM   request  ac
      ,activity ac
      ,AGENT    a
WHERE  a.state = 'ACT'
       AND r.request_type = 'B'
       AND r.confirmation = 'Y '
       AND r.dealer_number = a.dealer_id
       AND r.request_id = ac.request_id
GROUP  a.company_name


Regards,
Sabine

PS I eliminated the grouping sets, since I couldn't see the use of it in this query, but maybe I'm missing something?

[Updated on: Wed, 16 August 2006 10:22]

Report message to a moderator

Re: Pivot Select query for a Report [message #188077 is a reply to message #186445] Wed, 16 August 2006 23:59 Go to previous message
zulu99
Messages: 4
Registered: August 2006
Junior Member
Hi Sabine

Thanks for showing an easier option. But in the query which you have provided it fails saying r.status is not a group by function.

Here is what i did to combine the 2 queries into one.
select a.company_name, sum(a.total), sum(a.open), avg (b.avgopen)
from
(--- 1st query ----) a ,
(---- 2nd query ---) b
where a.company_name = b.company_name
group by grouping sets(a.compnay_name,())

Now the problem I am encountering is a typical outer join scenario. I am retreving only those requests which are handled by agents but suppose an agent has not handled any requests I would even those agents to be shown in the report

Do post your thoughts on the same

Regards
Previous Topic: how to find the sql statement and how long it is running
Next Topic: tough query,help me
Goto Forum:
  


Current Time: Fri Dec 09 13:59:38 CST 2016

Total time taken to generate the page: 0.08068 seconds