Service Level berechnen (gruppieren)

[marja]

Hallo zusammen,

für alle aktuell offenen Daten, möchte ich den Service Level berechnet haben. Das heißt, wie viel Vorgänge sind kleiner 24h, zwischen 24h und 36h und größer 36h.

Mit der Abfrage (Beispiel)

SELECT VORGANG_ID, DATUM, SYSTIMESTAMP-DATE AS SLA
FROM TABELLE_VORGANG
WHERE AKT_STATUS_ID = '1' AND AKT_USER_ID is NULL

lasse ich mir alle aktuell offenen Datensätze anzeigen.
Die Spalte SLA zeigt mir dazu dann die Zeitdifferenz zwischen dem aktuellen Datum/Zeit und der Erstellung des jeweiligen Datensatzes (DATUM) an.

Nun möchte ich dieses Ergebnis gerne gruppieren nach den oben genannten SLA's. Das heißt bsp. 35 Vorgänge sind kleiner 24h, 20 Vorgänge zwischen 24h und 36h und so weiter.

Ich hoffe einer kann mir weiterhelfen?!

Gruß
Marja

[defo]

Auf die Schnelle: schau mal nach RANK oder DENSE RANK, window bzw. analytic functions.

In die Abfrage müssen dann noch entsprechend Deiner Vorstellung die genannten Grenzwerte rein 24, 36, 48 ..

[FlexGer]

Wie wäre es mit dieser Lösung?

[highlight=sql]
select count(case
when sla < 24 then
1
else
null
end) as n_24,
count(case
when sla between 24 and 36 then
1
else
null
end) as n_24_36,
count(case
when sla > 36 then
1
else
null
end) as n_36

from (SELECT VORGANG_ID, DATUM, (sysdate - datum) * 24 AS SLA
FROM (select '1234' as vorgang_id,
to_date('05.09.2016 15:00', 'dd.mm.yyyy hh24:mi') as datum,
'1' as akt_status_id,
null as akt_user_id
from dual
union all
select '12345' as vorgang_id,
to_date('06.09.2016 7:00', 'dd.mm.yyyy hh24:mi') as datum,
'1' as akt_status_id,
null as akt_user_id
from dual
union all
select '123456' as vorgang_id,
to_date('04.09.2016 11:00', 'dd.mm.yyyy hh24:mi') as datum,
'1' as akt_status_id,
null as akt_user_id
from dual
union all
select '1234567' as vorgang_id,
to_date('02.09.2016 12:00', 'dd.mm.yyyy hh24:mi') as datum,
'1' as akt_status_id,
null as akt_user_id
from dual) TABELLE_VORGANG
WHERE AKT_STATUS_ID = '1'
AND AKT_USER_ID is NULL);

[/highlight]

[jum]

Hier vielleicht etwas dynamischer mit einer Zeittabelle:

WITH data_tab(vorgang_id, datum) AS
 (SELECT '10', to_date('06.09.2016 15:00','dd.mm.yyyy hh24:mi') FROM dual UNION ALL
  SELECT '11', to_date('05.09.2016 15:00','dd.mm.yyyy hh24:mi') FROM dual UNION ALL 
  SELECT '12', to_date('06.09.2016 07:00','dd.mm.yyyy hh24:mi') FROM dual UNION ALL 
  SELECT '13', to_date('04.09.2016 11:00','dd.mm.yyyy hh24:mi') FROM dual UNION ALL 
  SELECT '17', to_date('02.09.2016 12:00','dd.mm.yyyy hh24:mi') FROM dual),
  time_tab(descr, rng_start, rng_stop) AS 
 (SELECT  '0-24',  0,  24  FROM dual UNION ALL
  SELECT '24-36', 24,  36  FROM dual UNION ALL 
  SELECT '36-48', 36,  48  FROM dual UNION ALL 
  SELECT   '48+', 36, 999  FROM dual)
SELECT DISTINCT descr
  , COUNT(*) OVER (PARTITION BY descr) n
  FROM data_tab 
  JOIN time_tab
    ON (sysdate - datum) * 24  BETWEEN rng_start AND rng_stop
 ORDER BY descr; 

DESCR N
----------
0-24	 1
24-36	 1
36-48	 1
48+	 3

[FlexGer]

Sehr schöne Lösung jum.
Warum hast du COUNT(*) OVER(PARTITION BY descr) verwendet?
Macht eine normale Gruppierung einen Unterschied?

[highlight=sql]
WITH data_tab(vorgang_id,
datum) AS
(SELECT '10', to_date('06.09.2016 15:00', 'dd.mm.yyyy hh24:mi')
FROM dual
UNION ALL
SELECT '11', to_date('05.09.2016 15:00', 'dd.mm.yyyy hh24:mi')
FROM dual
UNION ALL
SELECT '12', to_date('06.09.2016 07:00', 'dd.mm.yyyy hh24:mi')
FROM dual
UNION ALL
SELECT '13', to_date('04.09.2016 11:00', 'dd.mm.yyyy hh24:mi')
FROM dual
UNION ALL
SELECT '17', to_date('02.09.2016 12:00', 'dd.mm.yyyy hh24:mi')
FROM dual),
time_tab(descr,
rng_start,
rng_stop) AS
(SELECT '0-24', 0, 24
FROM dual
UNION ALL
SELECT '24-36', 24, 36
FROM dual
UNION ALL
SELECT '36-48', 36, 48
FROM dual
UNION ALL
SELECT '48+', 36, 999
FROM dual)
SELECT descr, COUNT(*) as n --OVER(PARTITION BY descr) n
FROM data_tab
JOIN time_tab
ON (sysdate - datum) * 24 BETWEEN rng_start AND rng_stop
group by descr
ORDER BY descr;
[/highlight]

[jum]

Hallo @FlexGer,
Du hast völlig recht, eine "normale" Gruppierung (Aggregierung) ist ausreichend und sogar besser, sie vermeidet das nachträgliche DISTINCT bei meiner Query (Analytisch) - Danke für den Hinweis.