sql query problem

[Sanny kumar]

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]

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.