OraFAQ Forum: SQL & PL/SQL » Help with PL SQL query

Home » SQL & PL/SQL » SQL & PL/SQL » Help with PL SQL query - Analytical Function or group by (12c)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Help with PL SQL query - Analytical Function or group by [message #664982]

Tue, 15 August 2017 13:23

namitanamburi
Messages: 35
Registered: March 2009

Member

Below is the emp table with emp_id as primary key and emp_job_id being the foreign key to emp_job table.

Below is the table data for three employees with Emp_job_id IN (100,200,300)



Emp_ID	entered	       Emp_job_id      Status
1	1/1/2017 8:00	100	        JOINED
2	1/2/2017 10:00	100	        REVIEWED
3	1/3/2017 7:00	100	        REVIEW ON HOLD
4	1/4/2017 9:00	100	        REVIEW APPROVED
5	1/5/2017 9:00	100	        REVIEW DENIED

Emp_ID	entered	       Emp_job_id    Status
6	2/10/2017 8:00	200	      JOINED
7	2/11/2017 0:00	200	      REVIEWED
8	2/12/2017 7:00	200	      REVOKED
9	2/13/2017 9:00	200	      REJECTED


Emp_ID	entered	        Emp_job_id   Status
10	1/1/2017 8:00	300	      REVIEW ON HOLD
11	1/2/2017 10:00	300	      IN PROGRESS
12	1/3/2017 7:00	300	      REVOKED
13	1/4/2017 9:00	300	      REJECTED
14	1/5/2017 9:00	300	      REVIEW DENIED


If status IN (REVIEW ON HOLD,REVIEW APPROVED,REVIEW DENIED) THEN I want to pick min(entered) date.

Emp_ID	entered	       Emp_job_id      Status
1	1/1/2017 8:00	100	        JOINED
2	1/2/2017 10:00	100	        REVIEWED
3	1/3/2017 7:00	100	        REVIEW ON HOLD
4	1/4/2017 9:00	100	        REVIEW APPROVED
5	1/5/2017 9:00	100	        REVIEW DENIED

Here I pick min entered which is  1/3/2017 7:00 (emp_id = 3)


If status IN (REVOKED,REJECTED) THEN I want to pick max(entered) date.

Emp_ID	entered	       Emp_job_id    Status
6	2/10/2017 8:00	200	      JOINED
7	2/11/2017 0:00	200	      REVIEWED
8	2/12/2017 7:00	200	      REVOKED
9	2/13/2017 9:00	200	      REJECTED

Here I pick max of entered which is 2/13/2017 9:00 (emp_id = 9)

If an employee has status IN (REVIEW ON HOLD,REVIEW APPROVED,REVIEW DENIED) and 
status IN (REVOKED,REJECTED) then (REVOKED,REJECTED) takes priority, so pick only (REVOKED,REJECTED) then use max(entered of REVOKED,REJECTED).

Emp_ID	entered	        Emp_job_id   Status
10	1/1/2017 8:00	300	      REVIEW ON HOLD
11	1/2/2017 10:00	300	      IN PROGRESS
12	1/3/2017 7:00	300	      REVOKED
13	1/4/2017 9:00	300	      REJECTED
14	1/5/2017 9:00	300	      REVIEW DENIED


Here I pick max of entered BETWEEN (REVOKED,REJECTED)
)which is 1/4/2017 9:00
 (emp_id = 13)

Any help would be highly appreciated.

[Updated on: Tue, 15 August 2017 13:30]

Report message to a moderator

Re: Help with PL SQL query - Analytical Function or group by [message #664983 is a reply to message #664982]

Tue, 15 August 2017 14:00

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Report message to a moderator

Re: Help with PL SQL query - Analytical Function or group by [message #665127 is a reply to message #664983]

Tue, 22 August 2017 17:10

namitanamburi
Messages: 35
Registered: March 2009

Member

my apologies for the delay.

here is the code.


CREATE TABLE emp
  (
    emp_id     NUMBER ,
    entered    DATE,
    emp_job_id NUMBER,
    status     VARCHAR2(100)
  );
  
  
  select * from emp;

  
INSERT INTO emp VALUES  (1,TO_TIMESTAMP('01-JAN-2017 08:00:00:00','DD-MON-YYYY HH24:MI:SS:FF'), 100, 'JOINED'  );
INSERT INTO emp VALUES  (2,TO_TIMESTAMP('02-JAN-2017 10:00:00:00','DD-MON-YYYY HH24:MI:SS:FF'), 100, 'REVIEWED'  );
INSERT INTO emp VALUES  (3,TO_TIMESTAMP('03-JAN-2017 07:00:00:00','DD-MON-YYYY HH24:MI:SS:FF'), 100, 'REVIEW ON HOLD'  );
INSERT INTO emp VALUES  (4,TO_TIMESTAMP('04-JAN-2017 09:00:00:00','DD-MON-YYYY HH24:MI:SS:FF'), 100, 'REVIEW APPROVED'  );
INSERT INTO emp VALUES  (5,TO_TIMESTAMP('05-JAN-2017 09:00:00:00','DD-MON-YYYY HH24:MI:SS:FF'), 100, 'REVIEW DENIED'  );



INSERT INTO emp VALUES  (6,TO_TIMESTAMP('10-FEB-2017 08:00:00:00','DD-MON-YYYY HH24:MI:SS:FF'), 200, 'JOINED'  );
INSERT INTO emp VALUES  (7,TO_TIMESTAMP('11-FEB-2017 00:00:00:00','DD-MON-YYYY HH24:MI:SS:FF'), 200, 'REVIEWED'  );
INSERT INTO emp VALUES  (8,TO_TIMESTAMP('12-FEB-2017 07:00:00:00','DD-MON-YYYY HH24:MI:SS:FF'), 200, 'REVOKED'  );
INSERT INTO emp VALUES  (9,TO_TIMESTAMP('13-FEB-2017 09:00:00:00','DD-MON-YYYY HH24:MI:SS:FF'), 200, 'REJECTED'  );



INSERT INTO emp VALUES  (10,TO_TIMESTAMP('01-JAN-2017 08:00:00:00','DD-MON-YYYY HH24:MI:SS:FF'), 300, 'REVIEW ON HOLD'  );
INSERT INTO emp VALUES  (11,TO_TIMESTAMP('02-JAN-2017 10:00:00:00','DD-MON-YYYY HH24:MI:SS:FF'), 300, 'IN PROGRESS'  );
INSERT INTO emp VALUES  (12,TO_TIMESTAMP('03-JAN-2017 07:00:00:00','DD-MON-YYYY HH24:MI:SS:FF'), 300, 'REVOKED'  );
INSERT INTO emp VALUES  (13,TO_TIMESTAMP('04-JAN-2017 09:00:00:00','DD-MON-YYYY HH24:MI:SS:FF'), 300, 'REJECTED'  );
INSERT INTO emp VALUES  (14,TO_TIMESTAMP('05-JAN-2017 09:00:00:00','DD-MON-YYYY HH24:MI:SS:FF'), 300, 'REVIEW DENIED'  );


commit;

I want to write query that can return

max of entered date if status IN ('REVOKED','REJECTED')
min of entered date if status IN ('REVIEW ON HOLD','REVIEW APPROVED','REVIEW DENIED')
max of entered date if status has both ('REVOKED','REJECTED') and ('REVIEW ON HOLD','REVIEW APPROVED','REVIEW DENIED')


SELECT MIN(entered)
FROM emp
WHERE status  IN ('REVIEW ON HOLD','REVIEW APPROVED','REVIEW DENIED')
AND emp_job_id = 100;

SELECT MAX(entered)
FROM emp
WHERE status  IN ('REVOKED','REJECTED') 
AND emp_job_id = 200;

WITH emp_data as (
SELECT entered,status,
ROW_NUMBER()
   OVER (PARTITION BY emp_job_id
         ORDER BY CASE WHEN status  IN ('REVOKED','REJECTED') THEN 1
         WHEN status  IN ('REVIEW ON HOLD','REVIEW APPROVED','REVIEW DENIED') THEN 2 
                       ELSE 3
                       END)
FROM emp
WHERE status  IN ('REVIEW ON HOLD','REVIEW APPROVED','REVIEW DENIED','REVOKED','REJECTED')
AND emp_job_id = 300) 
select max(entered) from emp_data where status IN ('REVOKED','REJECTED');

Any help is highly appreciated.

Report message to a moderator

Re: Help with PL SQL query - Analytical Function or group by [message #665208 is a reply to message #665127]

Fri, 25 August 2017 15:52

namitanamburi
Messages: 35
Registered: March 2009

Member

Would someone please help me achieve this in one pass.

Currently I do them separately as three queries and it is costing me a lot of time as three queries joined to other tables is taking time X 3 times.

Any help will be highly appreciated.

Report message to a moderator

Re: Help with PL SQL query - Analytical Function or group by [message #665222 is a reply to message #665208]

Sat, 26 August 2017 06:14

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

all the TO_TIMESTAMP function calls should be replaced by TO_DATE since target column is DATE datatype

Report message to a moderator

Re: Help with PL SQL query - Analytical Function or group by [message #665228 is a reply to message #665222]

Sat, 26 August 2017 07:47

Flyby
Messages: 188
Registered: March 2011
Location: Belgium

Senior Member

I came up with

WITH aggregatebyjobidandstatus
     AS (  SELECT emp_job_id
                 ,status
                 ,MAX (entered) entered_date_latest_by_status
                 ,MIN (entered) entered_date_oldest_by_status
                 ,CASE WHEN STATUS IN ('REVOKED', 'REJECTED') THEN 1 ELSE 0 END
                      NOREVIEW_COUNT
                 ,CASE
                      WHEN STATUS IN
                               ('REVIEW ON HOLD'
                               ,'REVIEW APPROVED'
                               ,'REVIEW DENIED') THEN
                          1
                      ELSE
                          0
                  END
                      REVIEW_COUNT
             FROM emp
            WHERE status IN ('REVIEW ON HOLD'
                            ,'REVIEW APPROVED'
                            ,'REVIEW DENIED'
                            ,'REVOKED'
                            ,'REJECTED')
         GROUP BY emp_job_id, status)
  SELECT emp_job_id
        ,CASE
             WHEN SUM (REVIEW_COUNT) > 0 AND SUM (NOREVIEW_COUNT) > 0 THEN
                 (SELECT MAX (entered_date_latest_by_status)
                    FROM aggregatebyjobidandstatus
                   WHERE     pickmydate.emp_job_id = aggregatebyjobidandstatus.emp_job_id
                         AND NOREVIEW_COUNT = 1)
             WHEN SUM (REVIEW_COUNT) > 0 THEN
                 (SELECT MIN (entered_date_oldest_by_status)
                    FROM aggregatebyjobidandstatus
                   WHERE     pickmydate.emp_job_id = aggregatebyjobidandstatus.emp_job_id
                         AND REVIEW_COUNT = 1)
             ELSE
                 (SELECT MAX (entered_date_latest_by_status)
                    FROM aggregatebyjobidandstatus
                   WHERE     pickmydate.emp_job_id = aggregatebyjobidandstatus.emp_job_id
                         AND NOREVIEW_COUNT = 1)
         END
             DATE_AND_TIME_TO_PICK
    FROM aggregatebyjobidandstatus pickmydate
GROUP BY emp_job_id
order by emp_job_id

Result (DD/MM/YYYY)

EMP_JOB_ID	DATE_AND_TIME_TO_PICK
100	03/01/2017
200	13/02/2017
300	04/01/2017

Report message to a moderator

Previous Topic:	Wanted to delete duplicate records
Next Topic:	Cursor within a cursor

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 07:32:11 CDT 2024