Home » SQL & PL/SQL » SQL & PL/SQL » Select Query suggestion (Oracle 12c)
Select Query suggestion [message #685730] Tue, 15 March 2022 08:44 Go to next message
akssre
Messages: 26
Registered: March 2018
Junior Member
Dear Team,

Good day,

I am trying to find out running downtime total hours based on action_date and previous action_date and group by code. D stands for Downtime.

I was trying to use LAG function, but as there are difference of less then one day between action_date, it returns values in number and then throws inconsistent datatype error.

create table EQUIP
(
  equip       VARCHAR2(20),
  type        VARCHAR2(20),
  action_date DATE,
  mr          VARCHAR2(20),
  dc          VARCHAR2(20),
  code        VARCHAR2(20)
)

Insert into equip values('PUMP','TE',to_date('24-FEB-2022 10:00:00','dd-MON-yyyy hh:mi:ss'),'W','I','NORMAL OPERATION');
Insert into equip values('PUMP','TE',to_date('24-FEB-2022 08:30:00','dd-MON-yyyy hh:mi:ss'),'','D','LINE TRIP');
Insert into equip values('PUMP','TE',to_date('19-FEB-2022 12:00:00','dd-MON-yyyy hh:mi:ss'),'W','I','NORMAL OPERATION');
Insert into equip values('PUMP','TE',to_date('19-FEB-2022 11:00:00','dd-MON-yyyy hh:mi:ss'),'','D','LINE TRIP');
Insert into equip values('PUMP','TE',to_date('13-FEB-2022 10:00:00','dd-MON-yyyy hh:mi:ss'),'W','I','NORMAL OPERATION');
Insert into equip values('PUMP','TE',to_date('05-FEB-2022 09:00:00','dd-MON-yyyy hh:mi:ss'),'','D','MANAGEMENT');
Insert into equip values('PUMP','EE',to_date('25-FEB-2022 10:00:00','dd-MON-yyyy hh:mi:ss'),'W','I','NORMAL OPERATION');
Insert into equip values('PUMP','EE',to_date('24-FEB-2022 09:00:00','dd-MON-yyyy hh:mi:ss'),'','D','LINE TRIP');
Insert into equip values('PUMP','EE',to_date('19-FEB-2022 10:00:00','dd-MON-yyyy hh:mi:ss'),'W','I','NORMAL OPERATION');
Insert into equip values('PUMP','EE',to_date('18-FEB-2022 10:00:00','dd-MON-yyyy hh:mi:ss'),'','D','LINE TRIP');
Insert into equip values('PUMP','EE',to_date('13-FEB-2022 10:00:00','dd-MON-yyyy hh:mi:ss'),'W','I','NORMAL OPERATION');
Insert into equip values('PUMP','EE',to_date('11-FEB-2022 09:00:00','dd-MON-yyyy hh:mi:ss'),'','D','POWER CUT');

Expected out put is :

EQUIP	TYPE	CODE	        Downtime
PUMP	TE	LINE TRIP	2.5
PUMP	TE	MANAGEMENT	145
PUMP	EE	LINE TRIP	47
PUMP	EE	POWER CUT	47
Kindly suggest.

Best Regards,

[Updated on: Tue, 15 March 2022 10:51]

Report message to a moderator

Re: Select Query suggestion [message #685736 is a reply to message #685730] Tue, 15 March 2022 13:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
 
SQL> select equip, type, code, action_date, dc
  2  from equip
  3  order by equip, type, code, action_date
  4  /
EQUIP                TYPE                 CODE                 ACTION_DATE         DC
-------------------- -------------------- -------------------- ------------------- -----------------
PUMP                 EE                   LINE TRIP            18/02/2022 10:00:00 D
PUMP                 EE                   LINE TRIP            24/02/2022 09:00:00 D
PUMP                 EE                   NORMAL OPERATION     13/02/2022 10:00:00 I
PUMP                 EE                   NORMAL OPERATION     19/02/2022 10:00:00 I
PUMP                 EE                   NORMAL OPERATION     25/02/2022 10:00:00 I
PUMP                 EE                   POWER CUT            11/02/2022 09:00:00 D
PUMP                 TE                   LINE TRIP            19/02/2022 11:00:00 D
PUMP                 TE                   LINE TRIP            24/02/2022 08:30:00 D
PUMP                 TE                   MANAGEMENT           05/02/2022 09:00:00 D
PUMP                 TE                   NORMAL OPERATION     13/02/2022 10:00:00 I
PUMP                 TE                   NORMAL OPERATION     19/02/2022 12:00:00 I
PUMP                 TE                   NORMAL OPERATION     24/02/2022 10:00:00 I
Can you explain in details your output?

Report message to a moderator

Re: Select Query suggestion [message #685737 is a reply to message #685736] Tue, 15 March 2022 13:35 Go to previous messageGo to next message
akssre
Messages: 26
Registered: March 2018
Junior Member
Hello Michel,

Greetings,

Below are the inputs,


1. PUMP is based on two types ( TE, EE), so we will take it TYPE wise. let’s take TE first.

Select EQUIP, TYPE, ACTION_DATE, MR, DC, CODE from EQUIP where TYPE = 'TE' order by action_date desc

         EQUIP	TYPE	ACTION_DATE	MR	DC	CODE
         PUMP	TE	2/24/2022 10:00	W	I	NORMAL OPERATION
         PUMP	TE	2/24/2022 8:30		D	LINE TRIP
         PUMP	TE	2/19/2022 12:00	W	I	NORMAL OPERATION
         PUMP	TE	2/19/2022 11:00		D	LINE TRIP
         PUMP	TE	2/13/2022 10:00	W	I	NORMAL OPERATION
         PUMP	TE	2/5/2022 9:00		D	MANAGEMENT
2. We will order by action_date desc, it will let us know when the pump was down, which is marked DC = 'D', DC = I means pump is working.
3. Data shows that pump was down on 24-02-2022 8.30 and become operational on 24-02-2022 10.00, so in total pump was down for 1.5 hrs.
4. And code of the error is 'Line Trip'
5. Same has happened earlier also on 19-Feb-2022 for 1 hours under same error code
6. Pump was also down on 5th and become operational on 13 so all together 8 days which is 193 hrs. down.

So in total the pup was down for 193+1+1.5, however there are two codes because of which the it was down so we need the following for two codes.

So for TE below will be the output
EQUIP	TYPE	CODE	        Downtime
PUMP	TE	LINE TRIP	2.5
PUMP	TE	MANAGEMENT	193
Similar for EE will follow.

Best Regards,

[Updated on: Tue, 15 March 2022 13:38]

Report message to a moderator

Re: Select Query suggestion [message #685738 is a reply to message #685737] Tue, 15 March 2022 14:02 Go to previous messageGo to next message
piripicchio
Messages: 20
Registered: April 2018
Location: Rome
Junior Member
A very quick try before dinner, is it what you expected?
SQL> WITH DSOURCE AS 
  2  (SELECT EQUIP, TYPE, ACTION_DATE, MR, DC, CODE,
  3         CASE
  4          WHEN DC = 'I' THEN NULL
  5          ELSE LEAD(ACTION_DATE, 1, NULL) OVER (ORDER BY TYPE, ACTION_DATE)
  6         END LEAD_DATE
  7  FROM EQUIP
  8  ORDER BY TYPE, ACTION_DATE)
  9  SELECT EQUIP, TYPE, CODE,
 10         TRUNC(SUM(LEAD_DATE - ACTION_DATE) * 24, 2) DOWNTIME
 11  FROM DSOURCE
 12  WHERE DC = 'D'
 13  GROUP BY EQUIP, TYPE, CODE;

EQUIP                TYPE                 CODE                   DOWNTIME
-------------------- -------------------- -------------------- ----------
PUMP                 EE                   POWER CUT                    49
PUMP                 EE                   LINE TRIP                    49
PUMP                 TE                   MANAGEMENT                  193
PUMP                 TE                   LINE TRIP                   2,5

Report message to a moderator

Re: Select Query suggestion [message #685739 is a reply to message #685737] Tue, 15 March 2022 14:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
 
SQL> select equip, type, action_date, dc, code
  2  from equip
  3  order by equip, type desc, action_date
  4  /
EQUIP                TYPE ACTION_DATE         DC CODE
-------------------- ---- ------------------- -- --------------------
PUMP                 TE   05/02/2022 09:00:00 D  MANAGEMENT
PUMP                 TE   13/02/2022 10:00:00 I  NORMAL OPERATION
PUMP                 TE   19/02/2022 11:00:00 D  LINE TRIP
PUMP                 TE   19/02/2022 12:00:00 I  NORMAL OPERATION
PUMP                 TE   24/02/2022 08:30:00 D  LINE TRIP
PUMP                 TE   24/02/2022 10:00:00 I  NORMAL OPERATION
PUMP                 EE   11/02/2022 09:00:00 D  POWER CUT
PUMP                 EE   13/02/2022 10:00:00 I  NORMAL OPERATION
PUMP                 EE   18/02/2022 10:00:00 D  LINE TRIP
PUMP                 EE   19/02/2022 10:00:00 I  NORMAL OPERATION
PUMP                 EE   24/02/2022 09:00:00 D  LINE TRIP
PUMP                 EE   25/02/2022 10:00:00 I  NORMAL OPERATION
So down times are:
SQL> with
  2    data as (
  3      select equip, type, action_date, dc, code,
  4             lead (action_date)
  5                 over (partition by equip, type order by action_date)
  6             - action_date duration
  7      from equip
  8    )
  9     select equip, type, code, 24*duration downtime
 10     from data
 11     where dc = 'D'
 12  order by equip, type desc, code
 13  /
EQUIP                TYPE CODE                   DOWNTIME
-------------------- ---- -------------------- ----------
PUMP                 TE   LINE TRIP                     1
PUMP                 TE   LINE TRIP                   1.5
PUMP                 TE   MANAGEMENT                  193
PUMP                 EE   LINE TRIP                    24
PUMP                 EE   LINE TRIP                    25
PUMP                 EE   POWER CUT                    49
Grouping:
SQL> with
  2    data as (
  3      select equip, type, action_date, dc, code,
  4             lead (action_date)
  5                 over (partition by equip, type order by action_date)
  6             - action_date duration
  7      from equip
  8    ),
  9    downtimes as (
 10     select equip, type, code, 24*duration downtime
 11     from data
 12     where dc = 'D'
 13    )
 14  select equip, type, code, sum(downtime) downtime
 15  from downtimes
 16  group by equip, type, code
 17  order by equip, type desc, code
 18  /
EQUIP                TYPE CODE                   DOWNTIME
-------------------- ---- -------------------- ----------
PUMP                 TE   LINE TRIP                   2.5
PUMP                 TE   MANAGEMENT                  193
PUMP                 EE   LINE TRIP                    49
PUMP                 EE   POWER CUT                    49

Report message to a moderator

Re: Select Query suggestion [message #685740 is a reply to message #685738] Tue, 15 March 2022 14:15 Go to previous messageGo to next message
akssre
Messages: 26
Registered: March 2018
Junior Member
Dear Piripicchio,

Greetings,

Thank you for your quick response

Prima facie, this is what is the expected result. I will run it over in the right database with entire column and data, and will update you.

Question:- Can we use LAG in the same fashion and get the same result?

Best Regards,

Report message to a moderator

Re: Select Query suggestion [message #685741 is a reply to message #685739] Tue, 15 March 2022 14:17 Go to previous messageGo to next message
akssre
Messages: 26
Registered: March 2018
Junior Member
Dear Michel,

Greetings,

Thank you for your quick response.

This is the expected result. I will run it over in the right database with entire column and data, and will update you.

Best Regards,

Report message to a moderator

Re: Select Query suggestion [message #685742 is a reply to message #685741] Tue, 15 March 2022 14:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can indeed get the same result with LAG:
SQL> with
  2    data as (
  3      select equip, type, action_date, dc, code,
  4             action_date
  5             - lag(action_date)
  6                 over (partition by equip, type order by action_date)
  7             duration,
  8             lag(code) over (partition by equip, type order by action_date)
  9               prev_code
 10      from equip
 11    ),
 12    downtimes as (
 13     select equip, type, prev_code code, 24*duration downtime
 14     from data
 15     where dc != 'D'
 16    )
 17  select equip, type, code, sum(downtime) downtime
 18  from downtimes
 19  group by equip, type, code
 20  order by equip, type desc, code
 21  /
EQUIP                TYPE CODE                   DOWNTIME
-------------------- ---- -------------------- ----------
PUMP                 TE   LINE TRIP                   2.5
PUMP                 TE   MANAGEMENT                  193
PUMP                 EE   LINE TRIP                    49
PUMP                 EE   POWER CUT                    49

Report message to a moderator

Re: Select Query suggestion [message #685745 is a reply to message #685742] Wed, 16 March 2022 08:35 Go to previous messageGo to next message
akssre
Messages: 26
Registered: March 2018
Junior Member
Dear Michel and Piripicchio,

I have executed the query on the actual database, result is fine where the data is correct in the base tables.

Thank you for the support.

Best Regards,

Report message to a moderator

Re: Select Query suggestion [message #685753 is a reply to message #685745] Thu, 17 March 2022 05:22 Go to previous message
Solomon Yakobson
Messages: 3212
Registered: January 2010
Location: Connecticut, USA
Senior Member
Match recognize solution:

SELECT  EQUIP,
        TYPE,
        CODE,
        SUM(DOWNTIME) DOWNTIME
  FROM  EQUIP
  MATCH_RECOGNIZE(
                  PARTITION BY EQUIP,
                               TYPE
                  ORDER BY ACTION_DATE
                  MEASURES
                    IS_DOWN.CODE CODE,
                    (IS_ANY.ACTION_DATE - IS_DOWN.ACTION_DATE) * 24 DOWNTIME
                  AFTER MATCH SKIP TO LAST IS_ANY
                  PATTERN(
                          IS_DOWN IS_ANY
                         )
                  DEFINE IS_DOWN AS DC = 'D'
                 )
  GROUP BY EQUIP,
           TYPE,
           CODE
  ORDER BY EQUIP,
           TYPE,
           CODE
/

EQUIP                TYPE                 CODE                   DOWNTIME
-------------------- -------------------- -------------------- ----------
PUMP                 EE                   LINE TRIP                    49
PUMP                 EE                   POWER CUT                    49
PUMP                 TE                   LINE TRIP                   2.5
PUMP                 TE                   MANAGEMENT                  193

SQL>
SY.

Report message to a moderator

Previous Topic: Pass a "Cursor XXXX is" statement to another procedure
Next Topic: Code not waiting for REFCURSOR
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Mar 25 15:30:05 CDT 2023
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.