I'm trying to find out a way with Oracle(11g) analytical function to get a time period in status data. My data are like this:
<style>
table {
font-family: arial, sans-serif;
border-collapse: collapse;
width: 100%;
}
td, th {
border: 1px solid #dddddd;
text-align: left;
padding: 8px;
}
tr:nth-child(even) {
background-color: #dddddd;
}
</style>
<table>
<tr>
<th>Service</th>
<th>Date</th>
<th>Status</th>
</tr>
<tr>
<td>Service A</td>
<td>01.01.2018 08:00</td>
<td>OK</td>
</tr>
<tr>
<td>Service A</td>
<td>01.01.2018 08:05</td>
<td>OK</td>
</tr>
<tr>
<td>Service A</td>
<td>01.01.2018 08:10</td>
<td>WARNING</td>
</tr>
<tr>
<td>Service A</td>
<td>01.01.2018 08:15</td>
<td>WARNING</td>
</tr>
<tr>
<td>Service A</td>
<td>01.01.2018 08:20:00</td>
<td>WARNING</td>
</tr>
<tr>
<td>Service A</td>
<td>01.01.2018 08:25:00</td>
<td>WARNING</td>
</tr>
<tr>
<td>Service A</td>
<td>01.01.2018 08:30:00</td>
<td>OK</td>
</tr>
<tr>
<td>Service A</td>
<td>01.01.2018 08:35:00</td>
<td>OK</td>
</tr>
</table>
I habe a lot of services, more different status and finaly a lot of data. My question is how I get in a smart anlalytical way a result like this:
Service, Start_date, End_date, Status
Service A, 01.01.2018 08:00, 01.01.2018 08:05,OK
Service A, 01.01.2018 08:10, 01.01.2018 08:25,WARNING
Service A, 01.01.2018 08:30, 01.01.2018 08:35,OK
and so on
Oracle 11g R2 Schema Setup:
CREATE TABLE yourtable
(Service varchar2(9), Date_t DATE, Status varchar2(7))
;
INSERT ALL
INTO yourtable (Service, Date_t, Status)
VALUES ('Service A', TIMESTAMP '2018-01-01 08:00:00', 'OK')
INTO yourtable (Service, Date_t, Status)
VALUES ('Service A', TIMESTAMP '2018-01-01 08:05:00', 'OK')
INTO yourtable (Service, Date_t, Status)
VALUES ('Service A', TIMESTAMP '2018-01-01 08:10:00', 'WARNING')
INTO yourtable (Service, Date_t, Status)
VALUES ('Service A', TIMESTAMP '2018-01-01 08:15:00', 'WARNING')
INTO yourtable (Service, Date_t, Status)
VALUES ('Service A', TIMESTAMP '2018-01-01 08:20:00', 'WARNING')
INTO yourtable (Service, Date_t, Status)
VALUES ('Service A', TIMESTAMP '2018-01-01 08:25:00', 'WARNING')
INTO yourtable (Service, Date_t, Status)
VALUES ('Service A', TIMESTAMP '2018-01-01 08:30:00', 'OK')
INTO yourtable (Service, Date_t, Status)
VALUES ('Service A', TIMESTAMP '2018-01-01 08:35:00', 'OK')
SELECT * FROM dual
;
Query 1:
SELECT service,
MIN (date_t) start_date,
MAX(date_t) end_date,
MAX(status) Status
FROM (SELECT service,
date_t,
status,
row_number()
over (
ORDER BY date_t ) - row_number()
over (
PARTITION BY service, status
ORDER BY date_t ) stat_change
FROM yourtable)
GROUP BY service,
stat_change
ORDER BY start_date,
end_date
| SERVICE | START_DATE | END_DATE | STATUS |
|-----------|----------------------|----------------------|---------|
| Service A | 2018-01-01T08:00:00Z | 2018-01-01T08:05:00Z | OK |
| Service A | 2018-01-01T08:10:00Z | 2018-01-01T08:25:00Z | WARNING |
| Service A | 2018-01-01T08:30:00Z | 2018-01-01T08:35:00Z | OK |