Home » SQL & PL/SQL » SQL & PL/SQL » Help with PL SQL query - Analytical Function or group by (12c)
Help with PL SQL query - Analytical Function or group by [message #664982] Tue, 15 August 2017 13:23 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 65249
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.

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 Go to previous messageGo to next message
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.



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 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
BlackSwan
Messages: 25742
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
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 Go to previous message
Flyby
Messages: 181
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
Previous Topic: Wanted to delete duplicate records
Next Topic: Compare and contrast TRUNCATE and DELETE for a table
Goto Forum:
  


Current Time: Tue Dec 12 09:29:00 CST 2017

Total time taken to generate the page: 0.06822 seconds