Pivot select Query for Report

[zulu99]

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

[rigatoni]

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;

[zulu99]

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