Calculate One Query against another [message #438387] |
Fri, 08 January 2010 09:45  |
andysam23586
Messages: 17 Registered: September 2009
|
Junior Member |
|
|
Hi,
I have two queries that work fine.
The first is
SELECT PASSGRP, COUNT(PASSGRP) AS Total
FROM LCC_TPROBLEM
WHERE (PASSGRP = 'SERVICE DESK 1ST LINE') AND (PCALLTYPE = 'INC') AND ((pstatus = 'SOLVED') OR (pstatus = 'COMPLETE')
OR (PSTATUS = 'CLOSED')) AND (psolvat BETWEEN (TRUNC(ADD_MONTHS(SYSDATE, -1),'MM')-TO_DATE('01-JAN-1970','DD-MON-YYYY'))*(86400)
AND ((TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE, -1)))-TO_DATE('01-JAN-1970','DD-MON-YYYY'))+1)*(86400)-1)
GROUP BY PASSGRP
This gives me the result:
SERVICE DESK 1ST LINE 2649
My second query is:
SELECT PASSGRP, COUNT(PASSGRP) AS TotalRemote
FROM LCC_TPROBLEM
WHERE (PASSGRP = 'SERVICE DESK 1ST LINE') AND (PCALLTYPE = 'INC') AND (PDICT3 <> 106) AND (PDICT3 <> 118)AND (PDICT3 <> 0) AND ((pstatus = 'SOLVED') OR (pstatus = 'COMPLETE')
OR (PSTATUS = 'CLOSED')) AND (psolvat BETWEEN (TRUNC(ADD_MONTHS(SYSDATE, -1),'MM')-TO_DATE('01-JAN-1970','DD-MON-YYYY'))*(86400)
AND ((TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE, -1)))-TO_DATE('01-JAN-1970','DD-MON-YYYY'))+1)*(86400)-1)
GROUP BY PASSGRP
This gives me the result:
SERVICE DESK 1ST LINE 511
What I need is a query that gives me the result of Query 2 (511)as a percentage of the result of Query 1 (2649).
E.G. 511/2649*100 = 19.29%
New query result would be:
SERVICE DESK 1ST LINE 19.29%
Hope this all makes sense.
Can anybody help.
Andy
|
|
|
|
Re: Calculate One Query against another [message #438390 is a reply to message #438387] |
Fri, 08 January 2010 09:49   |
cookiemonster
Messages: 13963 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Make each query an inline view:
SELECT PASSGRP, TotalRemote/Total
FROM (<query1>) q1,
(<query2>) q2
WHERE q1.passgrp = q2.passgrp
There are probably more efficient ways of doing it with analytics mind.
|
|
|
|