OraFAQ Forum: SQL & PL/SQL » Select Query suggestion

Home » SQL & PL/SQL » SQL & PL/SQL » Select Query suggestion (Oracle 12c)

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

Select Query suggestion [message #685067]

Sun, 17 October 2021 11:11

akssre
Messages: 26
Registered: March 2018

Junior Member

Dear Oracle Gurus,
Greetings!

I am looking for a solution for my below issue: -

I have below data set in a table, where I am looking for the changed based on the event_time, so for example employee was on DC position for a while then he temporarily promoted to another position, on which he may be for several months, but I want to find the record where the actual change happened.

Note: Data has several issues, sometimes it is changed is null or sometime revenue is NULL, sometime both are null, so we will ignore those and find out the value which has data.

I hope I could state the problem in an understandable manner.

CREATE TABLE TEST
   (       
    DIVISION VARCHAR2(20),
    EMPLOYEE_ID NUMBER,
    EMPLOYEE_NAME VARCHAR2(20),
    EVENT_TIME DATE,
    POSITION_CHANGE VARCHAR2(20),
    REVENUE NUMBER
   ) ;


Insert into test (DIVISION,EMPLOYEE_ID,EMPLOYEE_NAME,EVENT_TIME,POSITION_CHANGE,REVENUE) Values ('HR',1,'AK',TO_DATE('11/08/2021 00:00:00 ', 'dd/mm/yyyy hh24:mi:ss'),'CL',400);
Insert into test (DIVISION,EMPLOYEE_ID,EMPLOYEE_NAME,EVENT_TIME,POSITION_CHANGE,REVENUE) Values ('HR',1,'AK',TO_DATE('10/08/2021 06:00:00 ', 'dd/mm/yyyy hh24:mi:ss'),'CL',500);
Insert into test (DIVISION,EMPLOYEE_ID,EMPLOYEE_NAME,EVENT_TIME,POSITION_CHANGE,REVENUE) Values ('HR',1,'AK',TO_DATE('10/08/2021 03:00:00 ', 'dd/mm/yyyy hh24:mi:ss'),'','');
Insert into test (DIVISION,EMPLOYEE_ID,EMPLOYEE_NAME,EVENT_TIME,POSITION_CHANGE,REVENUE) Values ('HR',1,'AK',TO_DATE('09/08/2021 00:00:00 ', 'dd/mm/yyyy hh24:mi:ss'),'DC','300');
Insert into test (DIVISION,EMPLOYEE_ID,EMPLOYEE_NAME,EVENT_TIME,POSITION_CHANGE,REVENUE) Values ('HR',1,'AK',TO_DATE('08/08/2021 00:00:00 ', 'dd/mm/yyyy hh24:mi:ss'),'DC',400);
Insert into test (DIVISION,EMPLOYEE_ID,EMPLOYEE_NAME,EVENT_TIME,POSITION_CHANGE,REVENUE) Values ('HR',1,'AK',TO_DATE('08/08/2021 00:00:00 ', 'dd/mm/yyyy hh24:mi:ss'),'DC',400);

Insert into test (DIVISION,EMPLOYEE_ID,EMPLOYEE_NAME,EVENT_TIME,POSITION_CHANGE,REVENUE) Values ('HR',2,'BK',TO_DATE('11/08/2021 00:00:00 ', 'dd/mm/yyyy hh24:mi:ss'),'DC',300);
Insert into test (DIVISION,EMPLOYEE_ID,EMPLOYEE_NAME,EVENT_TIME,POSITION_CHANGE,REVENUE) Values ('HR',2,'BK',TO_DATE('10/08/2021 00:00:00 ', 'dd/mm/yyyy hh24:mi:ss'),'CL',200);

Insert into test (DIVISION,EMPLOYEE_ID,EMPLOYEE_NAME,EVENT_TIME,POSITION_CHANGE,REVENUE) Values ('HR',3,'CK',TO_DATE('11/08/2021 00:00:00 ', 'dd/mm/yyyy hh24:mi:ss'),'TT',1234);
Insert into test (DIVISION,EMPLOYEE_ID,EMPLOYEE_NAME,EVENT_TIME,POSITION_CHANGE,REVENUE) Values ('HR',3,'CK',TO_DATE('10/08/2021 00:00:00 ', 'dd/mm/yyyy hh24:mi:ss'),'TT',200);

Insert into test (DIVISION,EMPLOYEE_ID,EMPLOYEE_NAME,EVENT_TIME,POSITION_CHANGE,REVENUE) Values ('IT',4,'DK',TO_DATE('11/08/2021 09:00:00 ', 'dd/mm/yyyy hh24:mi:ss'),'CL',400);
Insert into test (DIVISION,EMPLOYEE_ID,EMPLOYEE_NAME,EVENT_TIME,POSITION_CHANGE,REVENUE) Values ('IT',4,'DK',TO_DATE('11/08/2021 01:00:00 ', 'dd/mm/yyyy hh24:mi:ss'),'CL',500);
Insert into test (DIVISION,EMPLOYEE_ID,EMPLOYEE_NAME,EVENT_TIME,POSITION_CHANGE,REVENUE) Values ('IT',4,'DK',TO_DATE('09/08/2021 00:00:00 ', 'dd/mm/yyyy hh24:mi:ss'),'DC','');
Insert into test (DIVISION,EMPLOYEE_ID,EMPLOYEE_NAME,EVENT_TIME,POSITION_CHANGE,REVENUE) Values ('IT',4,'DK',TO_DATE('08/08/2021 00:00:00 ', 'dd/mm/yyyy hh24:mi:ss'),'DC',44);
Insert into test (DIVISION,EMPLOYEE_ID,EMPLOYEE_NAME,EVENT_TIME,POSITION_CHANGE,REVENUE) Values ('IT',4,'DK',TO_DATE('08/08/2021 00:00:00 ', 'dd/mm/yyyy hh24:mi:ss'),'DC',567);

Insert into test (DIVISION,EMPLOYEE_ID,EMPLOYEE_NAME,EVENT_TIME,POSITION_CHANGE,REVENUE) Values ('IT',5,'EK',TO_DATE('10/08/2021 00:00:00 ', 'dd/mm/yyyy hh24:mi:ss'),'','');
Insert into test (DIVISION,EMPLOYEE_ID,EMPLOYEE_NAME,EVENT_TIME,POSITION_CHANGE,REVENUE) Values ('IT',5,'EK',TO_DATE('09/08/2021 00:00:00 ', 'dd/mm/yyyy hh24:mi:ss'),'DC',300);
Insert into test (DIVISION,EMPLOYEE_ID,EMPLOYEE_NAME,EVENT_TIME,POSITION_CHANGE,REVENUE) Values ('IT',5,'EK',TO_DATE('07/08/2021 00:00:00 ', 'dd/mm/yyyy hh24:mi:ss'),'','');
Insert into test (DIVISION,EMPLOYEE_ID,EMPLOYEE_NAME,EVENT_TIME,POSITION_CHANGE,REVENUE) Values ('IT',5,'EK',TO_DATE('05/08/2021 00:00:00 ', 'dd/mm/yyyy hh24:mi:ss'),'DC',300);
Insert into test (DIVISION,EMPLOYEE_ID,EMPLOYEE_NAME,EVENT_TIME,POSITION_CHANGE,REVENUE) Values ('IT',5,'EK',TO_DATE('10/07/2021 00:00:00 ', 'dd/mm/yyyy hh24:mi:ss'),'','');
Insert into test (DIVISION,EMPLOYEE_ID,EMPLOYEE_NAME,EVENT_TIME,POSITION_CHANGE,REVENUE) Values ('IT',5,'EK',TO_DATE('09/06/2021 00:00:00 ', 'dd/mm/yyyy hh24:mi:ss'),'DC',300);
Insert into test (DIVISION,EMPLOYEE_ID,EMPLOYEE_NAME,EVENT_TIME,POSITION_CHANGE,REVENUE) Values ('IT',5,'EK',TO_DATE('09/03/2021 15:00:00 ', 'dd/mm/yyyy hh24:mi:ss'),'DC',345);
Insert into test (DIVISION,EMPLOYEE_ID,EMPLOYEE_NAME,EVENT_TIME,POSITION_CHANGE,REVENUE) Values ('IT',5,'EK',TO_DATE('09/03/2021 12:00:00 ', 'dd/mm/yyyy hh24:mi:ss'),'DC',800);

Expected result is :-

Division	Employee_ID	Employee_Name	Event_time	      Position_Changed	Revenue
HR	        1	        AK	        10-Aug-2021 00:00:00	CL	        500
HR	        2	        BK	        14-Aug-2021 00:00:00	DC	        300
HR	        3	        CK	        10-Aug-2021 00:00:00	TT	        200
IT	        4	        DK	        10-Aug-2021 01:00:00	CL	        500
IT	        5	        EK	        09-Mar-2021 12:00:00	DC	        800

Best Regards,

Report message to a moderator

Re: Select Query suggestion [message #685069 is a reply to message #685067]

Sun, 17 October 2021 12:48

John Watson
Messages: 8804
Registered: January 2010
Location: Global Village

Senior Member

From where is this row in your output,

HR	        2	        BK	        14-Aug-2021 00:00:00	DC	        300

supposed to have come?

Also, what SQL have you tried so far?

Report message to a moderator

Re: Select Query suggestion [message #685070 is a reply to message #685067]

Sun, 17 October 2021 12:56

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

Senior Member
Account Moderator

Ignoring row with nulls we have:

SQL> col division format a3
SQL> col EMPLOYEE_NAME format a3
SQL> col POSITION_CHANGE format a3
SQL> with
  2    data1 as (
  3      select DIVISION, EMPLOYEE_ID, EMPLOYEE_NAME, EVENT_TIME, POSITION_CHANGE, REVENUE,
  4             decode(lag(POSITION_CHANGE,1,' ')
  5                      over (partition by DIVISION, EMPLOYEE_ID order by EVENT_TIME),
  6                    POSITION_CHANGE, 0,
  7                    row_number()
  8                      over (partition by DIVISION, EMPLOYEE_ID order by EVENT_TIME)
  9                   ) flag
 10      from test
 11      where POSITION_CHANGE is not null and REVENUE is not null
 12    ),
 13    data2 as ( -- This one to pick up only the last change
 14      select DIVISION, EMPLOYEE_ID, max(flag) mfl
 15      from data1
 16      group by DIVISION, EMPLOYEE_ID
 17    )
 18  select d1.DIVISION, d1.EMPLOYEE_ID, EMPLOYEE_NAME, EVENT_TIME, POSITION_CHANGE, REVENUE
 19  from data1 d1, data2 d2
 20  where d1.DIVISION = d2.DIVISION
 21    and d1.EMPLOYEE_ID = d2.EMPLOYEE_ID
 22    and d1.flag = d2.mfl
 23  order by DIVISION, EMPLOYEE_ID
 24  /
DIV EMPLOYEE_ID EMP EVENT_TIME  POS    REVENUE
--- ----------- --- ----------- --- ----------
HR            1 AK  10-AUG-2021 CL         500
HR            2 BK  11-AUG-2021 DC         300
HR            3 CK  10-AUG-2021 TT         200
IT            4 DK  11-AUG-2021 CL         500
IT            5 EK  09-MAR-2021 DC         800

My result does not match yours but I don't know how you can get a result with 14-Aug-2021 whereas there are no rows with this date in your test data.

Report message to a moderator

Re: Select Query suggestion [message #685073 is a reply to message #685069]

Sun, 17 October 2021 23:19

akssre
Messages: 26
Registered: March 2018

Junior Member

Hello John,

Good morning!

Yes, It is a typing mistake, it should have been as follows

Division	Employee_ID	Employee_Name	Event_time	      Position_Changed	Revenue
HR	        1	        AK	        10-Aug-2021 00:00:00	CL	        500
HR	        2	        BK	        11-Aug-2021 00:00:00	DC	        300
HR	        3	        CK	        10-Aug-2021 00:00:00	TT	        200
IT	        4	        DK	        11-Aug-2021 01:00:00	CL	        500
IT	        5	        EK	        09-Mar-2021 12:00:00	DC	        800

Below is the query i tried before going blank. I wanted to create a simple statement which a new SQL joiner can also understand, but could not do much.

SELECT *
  FROM TEST t
 WHERE EVENT_TIME >
       (select min(EVENT_TIME)
          from (SELECT *
                  FROM TEST T1
                 WHERE EVENT_TIME >=
                       (SELECT MAX(EVENT_TIME)
                          FROM TEST T2
                         WHERE T2.employee_name = T1.employee_name
                           AND T1.position_change != T2.position_change)))
   AND POSITION_CHANGE IS NOT NULL

Report message to a moderator

Re: Select Query suggestion [message #685074 is a reply to message #685070]

Sun, 17 October 2021 23:23

akssre
Messages: 26
Registered: March 2018

Junior Member

Hello Michel,

Good morning!

Yes, It is a typing mistake, it should have been as follows

Division	Employee_ID	Employee_Name	Event_time	      Position_Changed	Revenue
HR	        1	        AK	        10-Aug-2021 00:00:00	CL	        500
HR	        2	        BK	        11-Aug-2021 00:00:00	DC	        300
HR	        3	        CK	        10-Aug-2021 00:00:00	TT	        200
IT	        4	        DK	        11-Aug-2021 01:00:00	CL	        500
IT	        5	        EK	        09-Mar-2021 12:00:00	DC	        800

Your query is giving result as it was required. However i am not sure, i am capable enough to understand it that well. I will try my level best though.

Many thanks for your prompt response and assistance.

Have a good day.

Best Regards,

Report message to a moderator

Re: Select Query suggestion [message #685075 is a reply to message #685074]

Mon, 18 October 2021 00:33

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

Senior Member
Account Moderator

The core part is the LAG function which may get values from a row "before" the current one given the order you specify.

In 12c, you also have the MATCH_RECOGNIZE clause which I don't know, to handle such case, maybe Solomon will see this topic and will provide a solution with it.

Report message to a moderator

Previous Topic:	Is there a way to create a materialized view with datatype and length?
Next Topic:	DBMS_JOB.SUBMIT not appearing

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Mar 25 16:23:22 CDT 2023