Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

sql query problem

 
Sanny kumar
Ranch Hand
Posts: 53
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Guys ,

I m strucked with sql query and i m nt getting correct output..

The table script,sample sql query and desired output as follows:

CREATE TABLE OPS_MASTER (
SLNO NUMBER,
COMP_CIRCLE VARCHAR2(10),
COMP_STATUS VARCHAR2(10),
ENTERED_DATE DATE,
CLOSED_DATE DATE)

The Testdata:
----------------
INSERT INTO OPS_MASTER ( SLNO, COMP_CIRCLE, COMP_STATUS, ENTERED_DATE,
CLOSED_DATE ) VALUES (
1, 'A', 'CLOSED', TO_Date( '07/11/2006 05:20:50 PM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '07/11/2006 08:20:50 PM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO OPS_MASTER ( SLNO, COMP_CIRCLE, COMP_STATUS, ENTERED_DATE,
CLOSED_DATE ) VALUES (
2, 'B', 'OPEN', TO_Date( '07/11/2006 03:21:13 PM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '07/11/2006 08:21:13 PM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO OPS_MASTER ( SLNO, COMP_CIRCLE, COMP_STATUS, ENTERED_DATE,
CLOSED_DATE ) VALUES (
3, 'A', 'OPEN', TO_Date( '07/11/2006 07:21:33 PM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '07/11/2006 08:21:33 PM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO OPS_MASTER ( SLNO, COMP_CIRCLE, COMP_STATUS, ENTERED_DATE,
CLOSED_DATE ) VALUES (
4, 'B', 'CLOSED', TO_Date( '07/11/2006 01:21:51 PM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '07/11/2006 08:21:51 PM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO OPS_MASTER ( SLNO, COMP_CIRCLE, COMP_STATUS, ENTERED_DATE,
CLOSED_DATE ) VALUES (
5, 'B', 'CLOSED', TO_Date( '07/11/2006 03:21:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '07/11/2006 08:21:55 PM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO OPS_MASTER ( SLNO, COMP_CIRCLE, COMP_STATUS, ENTERED_DATE,
CLOSED_DATE ) VALUES (
6, 'A', 'CLOSED', TO_Date( '07/11/2006 02:22:03 PM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '07/11/2006 08:22:03 PM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO OPS_MASTER ( SLNO, COMP_CIRCLE, COMP_STATUS, ENTERED_DATE,
CLOSED_DATE ) VALUES (
7, 'B', 'OPEN', TO_Date( '07/11/2006 07:22:15 PM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '07/11/2006 08:22:15 PM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO OPS_MASTER ( SLNO, COMP_CIRCLE, COMP_STATUS, ENTERED_DATE,
CLOSED_DATE ) VALUES (
8, 'A', 'OPEN', TO_Date( '07/11/2006 11:22:22 AM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '07/11/2006 08:22:22 PM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO OPS_MASTER ( SLNO, COMP_CIRCLE, COMP_STATUS, ENTERED_DATE,
CLOSED_DATE ) VALUES (
9, 'A', 'CLOSED', TO_Date( '07/11/2006 08:22:33 PM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '07/11/2006 08:22:33 PM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO OPS_MASTER ( SLNO, COMP_CIRCLE, COMP_STATUS, ENTERED_DATE,
CLOSED_DATE ) VALUES (
10, 'B', 'OPEN', TO_Date( '07/11/2006 08:22:50 PM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '07/11/2006 08:22:50 PM', 'MM/DD/YYYY HH:MI:SS AM'));
commit;

-----------------




The Required Output is:

Comp_circle|| <1 hr(Closed)|| B/W 1 to 2hrs(Closed)|| >=2hrs(Closed) || total(Closed)|| <1hr open || b/w 1 hr and 2 hrs (Open)|| >2hrs(Open)||Total(Open)
=================================
The sample query:
SELECT A.comp_circle,A.LESS_1,A.LESS_2,A.MORE_4,B.OPEN_1,B.OPEN_2,B.OPEN_4 FROM
(SELECT A.comp_circle,A.LESS_1,A.LESS_2,B.MORE_4 FROM
(SELECT A.comp_circle,A.LESS_2,B.LESS_1 FROM
(SELECT comp_circle,COUNT(comp_status) LESS_2 FROM OPS_MASTER WHERE
((CLOSED_DATE - ENTERED_DATE)*24 > 1 and (CLOSED_DATE - ENTERED_DATE)*24<=2)
and COMP_STATUS ='CLOSED' and TRUNC(ENTERED_DATE)=TRUNC(SYSDATE) AND
COMP_CIRCLE IN ('A','B') group by COMP_CIRCLE,COMP_STATUS) A,
(SELECT comp_circle,COUNT(comp_status) LESS_1 FROM OPS_MASTER WHERE
(CLOSED_DATE - ENTERED_DATE)*24 <= 1 and COMP_STATUS ='CLOSED' and
TRUNC(ENTERED_DATE)=TRUNC(SYSDATE) AND COMP_CIRCLE IN ('A','B') group by COMP_CIRCLE,COMP_STATUS) B
WHERE A.COMP_CIRCLE=B.COMP_CIRCLE(+)) A,
(SELECT comp_circle,COUNT(comp_status) MORE_4 FROM OPS_MASTER WHERE
(CLOSED_DATE - ENTERED_DATE)*24 > 2 and COMP_STATUS ='CLOSED' and
TRUNC(ENTERED_DATE)=TRUNC(SYSDATE) AND COMP_CIRCLE IN ('A','B') group by COMP_CIRCLE,COMP_STATUS) B WHERE A.COMP_CIRCLE=B.COMP_CIRCLE(+)) a,
(SELECT A.comp_circle,A.OPEN_1,A.OPEN_2,B.OPEN_4 FROM (SELECT A.comp_circle,A.OPEN_2,B.OPEN_1 FROM (SELECT comp_circle,COUNT(comp_status) OPEN_2 FROM OPS_MASTER WHERE
((SYSDATE-ENTERED_DATE)*24 > 1 and (SYSDATE-ENTERED_DATE)*24<=2) and COMP_STATUS ='OPEN' and TRUNC(ENTERED_DATE)=TRUNC(SYSDATE) AND COMP_CIRCLE IN ('A','B') group by COMP_CIRCLE,COMP_STATUS) A,
(SELECT comp_circle,COUNT(comp_status) OPEN_1 FROM OPS_MASTER WHERE (SYSDATE-ENTERED_DATE)*24 <= 1 and COMP_STATUS ='OPEN' and TRUNC(ENTERED_DATE)=TRUNC(SYSDATE) AND COMP_CIRCLE IN ('A','B') group by COMP_CIRCLE,COMP_STATUS) B WHERE A.COMP_CIRCLE=B.COMP_CIRCLE(+)) A,
(SELECT comp_circle,COUNT(comp_status) OPEN_4 FROM OPS_MASTER WHERE (SYSDATE-ENTERED_DATE)*24 > 2 and COMP_STATUS ='OPEN' and TRUNC(ENTERED_DATE)=TRUNC(SYSDATE) AND COMP_CIRCLE IN ('A','B') group by COMP_CIRCLE,COMP_STATUS) B WHERE A.COMP_CIRCLE=B.COMP_CIRCLE(+)) B WHERE A.comp_circle=B.comp_circle(+)
===================================

can any one help?
 
Susanta Chatterjee
Ranch Hand
Posts: 102
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you reformat you expected result and post it, please? From the table data you have posted, it is not clear exactly what do you require.
 
Don't get me started about those stupid light bulbs.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic