• 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
  • Tim Cooke
  • paul wheaton
  • Jeanne Boyarsky
  • Ron McLeod
Sheriffs:
  • Paul Clapham
  • Liutauras Vilda
  • Devaka Cooray
Saloon Keepers:
  • Tim Holloway
  • Roland Mueller
Bartenders:

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.
 
Yes, of course, and I accept that blame. In fact, i covet that blame. As does this tiny ad:
Smokeless wood heat with a rocket mass heater
https://woodheat.net
reply
    Bookmark Topic Watch Topic
  • New Topic