• 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:
  • Tim Cooke
  • Campbell Ritchie
  • Ron McLeod
  • Liutauras Vilda
  • Jeanne Boyarsky
Sheriffs:
  • Junilu Lacar
  • Rob Spoor
  • Paul Clapham
Saloon Keepers:
  • Tim Holloway
  • Tim Moores
  • Jesse Silverman
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • Al Hobbs
  • Piet Souris
  • Frits Walraven

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.
 
WHAT is your favorite color? Blue, no yellow, ahhhhhhh! Tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic