Home » SQL & PL/SQL » SQL & PL/SQL » Cross tab SQL query (Oracle 9i)
Cross tab SQL query [message #638612] |
Mon, 15 June 2015 20:00 |
|
tarasasu
Messages: 9 Registered: December 2011
|
Junior Member |
|
|
Hi,
I am looking for a cross tab query in sql. The requirement is to have summary of tickets created in past 24 hrs.
The desired output should look as below
------------------------------------------------------------------
Status P1 P2 P3 P4
Total Tickets 0 8 4 0
Open Tickets 0 5 3 0
Resolved Tickets 0 3 1 0
Average of Resolve tickets 0 45 0 0
------------------------------------------------------------------
Here average should be calculated only on resolved tickets (sum(abc.field1)/count(resolved tickets)).
Below is my query
SELECT ( CASE
WHEN status IN ( '0', '1', '2', '3', '6' ) THEN 'Open Tickets'
WHEN status = '4' THEN 'Resolved'
ELSE 'Total'
END ) AS status,
Count(( CASE
WHEN priority = '3'
AND status IN ( '0', '1', '2', '3', '6' ) THEN 1
WHEN priority = '3'
AND status = '4' THEN 1
END )) AS P1,
Count(( CASE
WHEN priority = '2'
AND status IN ( '0', '1', '2', '3', '6' ) THEN 1
WHEN priority = '2'
AND status = '4' THEN 1
END )) AS P2,
Count(( CASE
WHEN priority = '1'
AND status IN ( '0', '1', '2', '3', '6' ) THEN 1
WHEN priority = '1'
AND status = '4' THEN 1
END )) AS P3,
Count(( CASE
WHEN priority = '0'
AND status IN ( '0', '1', '2', '3', '6' ) THEN 1
WHEN priority = '0'
AND status = '4' THEN 1
END )) AS P4,
Nvl(( SUM(CASE
WHEN ( priority = '3'
AND status = '4' ) THEN abc.field1
END) / Count(CASE
WHEN ( priority = '3'
AND status = '4' ) THEN 1
END) ), 0) AvgP1,
Nvl(( SUM(CASE
WHEN ( priority = '2'
AND status = '4' ) THEN abc.field1
END) / Count(CASE
WHEN ( priority = '2'
AND status = '4' ) THEN 1
END) ), 0) AvgP2,
Nvl(( SUM(CASE
WHEN ( priority = '1'
AND status = '4' ) THEN abc.field2
END) / Count(CASE
WHEN ( priority = '1'
AND status = '4' ) THEN 1
END) ), 0) AvgP3,
Nvl(( SUM(CASE
WHEN ( priority = '0'
AND status = '4' ) THEN abc.field2
END) / Count(CASE
WHEN ( priority = '0'
AND status = '4' ) THEN 1
END) ), 0) AvgP4
FROM abc
WHERE ( create_date >= To_date(SYSDATE + interval '-24' hour)
AND create_date < SYSDATE )
AND status IN ( '0', '1', '2', '3',
'4', '6' )
GROUP BY rollup ( CASE
WHEN status IN ( '0', '1', '2', '3', '6' ) THEN 'Open Tickets'
WHEN status = '4' THEN 'Resolved'
ELSE 'Total'
END )
My current output
------------------
STATUS P1 P2 P3 P4 AvgP1 AvgP2 AvgP3 AvgP4
--------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -
Open Tickets (24 hrs) 0 5 3 0 0 0 0 0
Resolved 0 3 1 0 0 45 0 0
0 8 4 0 0 45 0 0
Please suggest how to fetch the desired output in oracle sql.
Regards,
Tara
[Updated on: Mon, 15 June 2015 21:40] Report message to a moderator
|
|
|
|
Re: Cross tab SQL query [message #638616 is a reply to message #638614] |
Tue, 16 June 2015 02:33 |
|
tarasasu
Messages: 9 Registered: December 2011
|
Junior Member |
|
|
Hi,
Please find the update query and attached is the test case script.
SELECT ( CASE WHEN status IN ( '0', '1', '2', '3', '6' ) THEN 'Open Tickets'
WHEN status = '4' THEN 'Resolved'
ELSE 'Total'
END ) AS status,
Count(( CASE
WHEN priority = 'P1'
AND status IN ( '0', '1', '2', '3', '6' ) THEN 1
WHEN priority = 'P1'
AND status = '4' THEN 1
END )) AS P1,
Count(( CASE
WHEN priority = 'P2'
AND status IN ( '0', '1', '2', '3', '6' ) THEN 1
WHEN priority = 'P2'
AND status = '4' THEN 1
END )) AS P2,
Count(( CASE
WHEN priority = 'P3'
AND status IN ( '0', '1', '2', '3', '6' ) THEN 1
WHEN priority = 'P3'
AND status = '4' THEN 1
END )) AS P3,
Count(( CASE
WHEN priority = 'P4'
AND status IN ( '0', '1', '2', '3', '6' ) THEN 1
WHEN priority = 'P4'
AND status = '4' THEN 1
END )) AS P4,
Nvl(( SUM(CASE
WHEN ( priority = 'P1'
AND status = '4' ) THEN field1
END) / Count(CASE
WHEN ( priority = 'P1'
AND status = '4' ) THEN 1
END) ), 0) AvgP1,
Nvl(( SUM(CASE
WHEN ( priority = 'P2'
AND status = '4' ) THEN field1
END) / Count(CASE
WHEN ( priority = 'P2'
AND status = '4' ) THEN 1
END) ), 0) AvgP2,
Nvl(( SUM(CASE
WHEN ( priority = 'P3'
AND status = '4' ) THEN field2
END) / Count(CASE
WHEN ( priority = 'P3'
AND status = '4' ) THEN 1
END) ), 0) AvgP3,
Nvl(( SUM(CASE
WHEN ( priority = 'P4'
AND status = '4' ) THEN field2
END) / Count(CASE
WHEN ( priority = 'P4'
AND status = '4' ) THEN 1
END) ), 0) AvgP4
FROM SPTEST
WHERE (create_date >= '14-JUN-2015'
AND create_date < SYSDATE )
and status IN ( '0', '1', '2', '3',
'4', '6' )
GROUP BY cube ( CASE
WHEN status IN ( '0', '1', '2', '3', '6' ) THEN 'Open Tickets'
WHEN status = '4' THEN 'Resolved'
ELSE 'Total'
END )
query output
------------
"STATUS" "P1" "P2" "P3" "P4" "AVGP1" "AVGP2" "AVGP3" "AVGP4"
NULL 1 6 4 4 8 7.5 5 4
"Resolved" 1 2 1 1 8 7.5 5 4
"Open Tickets" 0 4 3 3 0 0 0 0
Desired Output
--------------
"STATUS" "P1" "P2" "P3" "P4"
NULL 1 6 4 4
"Resolved" 1 2 1 1
"Open Tickets" 0 4 3 3
AVG 8 7.5 5 4
Thanks in advance
-
Attachment: export.sql
(Size: 2.94KB, Downloaded 1301 times)
|
|
|
|
Re: Cross tab SQL query [message #638640 is a reply to message #638620] |
Tue, 16 June 2015 06:39 |
|
tarasasu
Messages: 9 Registered: December 2011
|
Junior Member |
|
|
Hi Michel,
Average is calculated based only on resolved tickets.
Here average for P1 tickets = (sum(field1)/count(resolved tickets of P1))
average for P2 tickets = (sum(field1)/count(resolved tickets of P2))
average for P3 tickets = (sum(field2)/count(resolved tickets of P3))
average for P4 tickets = (sum(field2)/count(resolved tickets of P4))
ex: in test data field1 value for P2 (only resolved tickets) is 10 and 5, count(resolved tickets of P2) is 2;
so average will be (sum(10+5)/2) = 7.5
Thanks,
Tara
[Updated on: Tue, 16 June 2015 06:50] Report message to a moderator
|
|
|
Re: Cross tab SQL query [message #638641 is a reply to message #638640] |
Tue, 16 June 2015 07:17 |
|
Michel Cadot
Messages: 68625 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
I have no more 9i database to test the query but I think I didn't use any function or syntax not existing in this version.
SQL> with
2 data as (
3 select priority,
4 case
5 when status in ( '0', '1', '2', '3', '6' ) then 'Open Tickets'
6 when status ='4' then 'Resolved'
7 end status,
8 count(*) nb_status,
9 count(decode(status, '4', 1)) nb_resolved,
10 sum(case
11 when status ='4' and priority in ('P1', 'P2') then field1
12 when status ='4' and priority in ('P3', 'P4') then field2
13 else 0
14 end) sum_field,
15 sum(count(*)) over (partition by priority) total
16 from sptest
17 where create_date between to_date('14-JUN-2015','DD-MON-YYYY') and sysdate
18 and status in ( '0', '1', '2', '3', '4', '6' )
19 group by priority,
20 case
21 when status in ( '0', '1', '2', '3', '6' ) then 'Open Tickets'
22 when status ='4' then 'Resolved'
23 end
24 ),
25 lines as (select level line from dual connect by level <= 4)
26 select decode(line, 1,'Total', 2,'Resolved', 3,'Open Tickets', 4,'Average') status,
27 decode(line,
28 1, max(decode(priority, 'P1',total)),
29 2, max(decode(priority, 'P1',decode(status,'Resolved',nb_status))),
30 3, max(decode(priority, 'P1',decode(status,'Open Tickets',nb_status))),
31 4, max(decode(priority, 'P1',sum_field/greatest(nb_resolved,1)))
32 ) "P1",
33 decode(line,
34 1, max(decode(priority, 'P2',total)),
35 2, max(decode(priority, 'P2',decode(status,'Resolved',nb_status))),
36 3, max(decode(priority, 'P2',decode(status,'Open Tickets',nb_status))),
37 4, max(decode(priority, 'P2',sum_field/greatest(nb_resolved,1)))
38 ) "P2",
39 decode(line,
40 1, max(decode(priority, 'P3',total)),
41 2, max(decode(priority, 'P3',decode(status,'Resolved',nb_status))),
42 3, max(decode(priority, 'P3',decode(status,'Open Tickets',nb_status))),
43 4, max(decode(priority, 'P3',sum_field/greatest(nb_resolved,1)))
44 ) "P3",
45 decode(line,
46 1, max(decode(priority, 'P4',total)),
47 2, max(decode(priority, 'P4',decode(status,'Resolved',nb_status))),
48 3, max(decode(priority, 'P4',decode(status,'Open Tickets',nb_status))),
49 4, max(decode(priority, 'P4',sum_field/greatest(nb_resolved,1)))
50 ) "P4"
51 from data, lines
52 group by line
53 order by line
54 /
STATUS P1 P2 P3 P4
------------ ---------- ---------- ---------- ----------
Total 1 6 4 4
Resolved 1 2 1 1
Open Tickets 4 3 3
Average 8 7.5 5 4
I let you add the NVL function if you want 0 instead of NULL when there is no tickets of some kinds.
[Updated on: Tue, 16 June 2015 07:18] Report message to a moderator
|
|
|
|
|
|
|
|
|
Re: Cross tab SQL query [message #638674 is a reply to message #638672] |
Wed, 17 June 2015 14:06 |
|
Michel Cadot
Messages: 68625 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote:the same query is executed in linked server
What is a "linked server"?
Quote:we are facing problems
Which help us in no way to what is the "problem".
Quote:which i believe is due to CTE used in query
Sorry, I don't know what is CTE.
Quote:That's the reason our reporting team is asking for a stored procedure with parameters
Without knowing what are the inputs and outputs of the procedure we can't help more.
Post the specification.
[Updated on: Wed, 17 June 2015 14:06] Report message to a moderator
|
|
|
|
Goto Forum:
Current Time: Thu Mar 28 19:29:50 CDT 2024
|