• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

sql query problem

 
Ranch Hand
Posts: 53
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
Ranch Hand
Posts: 102
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
reply
    Bookmark Topic Watch Topic
  • New Topic