Home » SQL & PL/SQL » SQL & PL/SQL » min / max date of grouped status of a values ? (merged) (oracle enterprise rdbms 18.3, linux64)
min / max date of grouped status of a values ? (merged) [message #681662] Thu, 13 August 2020 05:18 Go to next message
flat
Messages: 8
Registered: September 2006
Junior Member
Hi,

i would like to query the following table to get the proper result.
i cannot find any simple solution on this by myself.
maybe someone of you have a little trick on this.

here is my table:
date; service_name; status
13.08.2020 11:08; service_1; ok 
13.08.2020 11:09; service_1; ok
13.08.2020 11:10; service_1; ok
13.08.2020 11:11; service_1; ok
13.08.2020 11:12; service_1; fail
13.08.2020 11:13; service_1; fail
13.08.2020 11:14; service_1; fail
13.08.2020 11:15; service_1; ok
13.08.2020 11:16; service_1; fail
13.08.2020 11:17; service_1; fail
13.08.2020 11:18; service_1; fail
The result should be grouped by (service_name;status) and look like this (availability-reporting):

service_name; status; min_date_status; max_date_status; count_status
service1; ok ; 13.08.2020 11:08; 13.08.2020 11:11; 4
service1; fail; 13.08.2020 11:12; 13.08.2020 11:14; 3
service1; ok; 13.08.2020 11:15; 13.08.2020 11:15; 1
service1; fail; 13.08.2020 11:16; 13.08.2020 11:18; 3
does anybody have some clue, how to solve this ?

many thanks in advance,
flat

Report message to a moderator

Re: min / max date of grouped status of a values ? (merged) [message #681666 is a reply to message #681662] Thu, 13 August 2020 05:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

min, max, count group by service_name, status?
What is the problem?

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: min / max date of grouped status of a values ? (merged) [message #681667 is a reply to message #681666] Thu, 13 August 2020 05:38 Go to previous messageGo to next message
flat
Messages: 8
Registered: September 2006
Junior Member
Hi !
Problem is, that i do not want to group ALL status in one line in the result.
i need a min/max date of the status every time the status changed its value.
regards, flat

Report message to a moderator

Re: min / max date of grouped status of a values ? (merged) [message #681668 is a reply to message #681662] Thu, 13 August 2020 05:49 Go to previous messageGo to next message
flat
Messages: 8
Registered: September 2006
Junior Member
here are the inserts for a testcase:

CREATE TABLE availability_check (
    check_time             DATE,
    service            VARCHAR2(20),
    status                 VARCHAR2(20)
);

insert into availability_check values (to_date('13.08.2020 11:08:00','DD.MM.YYYY HH24:MI:SS'), 'service_1', 'ok');
insert into availability_check values (to_date('13.08.2020 11:09:00','DD.MM.YYYY HH24:MI:SS'), 'service_1', 'ok');
insert into availability_check values (to_date('13.08.2020 11:10:00','DD.MM.YYYY HH24:MI:SS'), 'service_1', 'ok');
insert into availability_check values (to_date('13.08.2020 11:11:00','DD.MM.YYYY HH24:MI:SS'), 'service_1', 'ok');
insert into availability_check values (to_date('13.08.2020 11:12:00','DD.MM.YYYY HH24:MI:SS'), 'service_1', 'fail');
insert into availability_check values (to_date('13.08.2020 11:13:00','DD.MM.YYYY HH24:MI:SS'), 'service_1', 'fail');
insert into availability_check values (to_date('13.08.2020 11:14:00','DD.MM.YYYY HH24:MI:SS'), 'service_1', 'fail');
insert into availability_check values (to_date('13.08.2020 11:15:00','DD.MM.YYYY HH24:MI:SS'), 'service_1', 'ok');
insert into availability_check values (to_date('13.08.2020 11:16:00','DD.MM.YYYY HH24:MI:SS'), 'service_1', 'fail');
insert into availability_check values (to_date('13.08.2020 11:17:00','DD.MM.YYYY HH24:MI:SS'), 'service_1', 'fail');
insert into availability_check values (to_date('13.08.2020 11:18:00','DD.MM.YYYY HH24:MI:SS'), 'service_1', 'fail');
regards, flat

Report message to a moderator

Re: min / max date of grouped status of a values ? (merged) [message #681669 is a reply to message #681668] Thu, 13 August 2020 06:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
 
SQL> select service, check_time, status from availability_check order by 1, 2;
SERVICE              CHECK_TIME          STATUS
-------------------- ------------------- --------------------
service_1            13/08/2020 11:08:00 ok
service_1            13/08/2020 11:09:00 ok
service_1            13/08/2020 11:10:00 ok
service_1            13/08/2020 11:11:00 ok
service_1            13/08/2020 11:12:00 fail
service_1            13/08/2020 11:13:00 fail
service_1            13/08/2020 11:14:00 fail
service_1            13/08/2020 11:15:00 ok
service_1            13/08/2020 11:16:00 fail
service_1            13/08/2020 11:17:00 fail
service_1            13/08/2020 11:18:00 fail

11 rows selected.

SQL> with
  2    data as (
  3      select service, check_time, status,
  4             case
  5               when lag(status,1,'X') over (partition by service order by check_time) != status
  6                 then row_number() over (partition by service order by check_time)
  7             end grp
  8      from availability_check
  9   ),
 10   grouping as (
 11      select service, check_time, status,
 12             last_value(grp ignore nulls) over (partition by service order by check_time) grp
 13      from data
 14   )
 15  select service, status, min(check_time) min_date_status, max(check_time) max_date_status, count(*) count_status
 16  from grouping
 17  group by service, status, grp
 18  order by service, grp
 19  /
SERVICE              STATUS               MIN_DATE_STATUS     MAX_DATE_STATUS     COUNT_STATUS
-------------------- -------------------- ------------------- ------------------- ------------
service_1            ok                   13/08/2020 11:08:00 13/08/2020 11:11:00            4
service_1            fail                 13/08/2020 11:12:00 13/08/2020 11:14:00            3
service_1            ok                   13/08/2020 11:15:00 13/08/2020 11:15:00            1
service_1            fail                 13/08/2020 11:16:00 13/08/2020 11:18:00            3

4 rows selected.

Report message to a moderator

Re: min / max date of grouped status of a values ? (merged) [message #681670 is a reply to message #681668] Thu, 13 August 2020 07:31 Go to previous messageGo to next message
EdStevens
Messages: 1375
Registered: September 2013
Senior Member
Just as an aside, thank you for
1) proper use of to_date
2) using 4-digit years.

Report message to a moderator

icon14.gif  Re: min / max date of grouped status of a values ? (merged) [message #681671 is a reply to message #681662] Thu, 13 August 2020 09:33 Go to previous message
flat
Messages: 8
Registered: September 2006
Junior Member
@Michel Cadot

Awsome, thats excaclty what i searched for.
thank you very much for such quick help!

in my point of view, this is advanced pivoting/grouping, i would never got to this.
Smile

regards,
flat

[Updated on: Thu, 13 August 2020 09:35]

Report message to a moderator

Previous Topic: update foreign key of all the referenced table of table
Next Topic: dbms_output.put_line synchronize
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Mar 30 23:00:33 CDT 2023
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.