Group by aufteilen, wenn in einem Datumsbereich ein Datum fehlt

[monsee]

Hallo zusammen,

ich habe mich hier neu angemeldet, weil ich mit einem SQL nicht weiterkomme und mir hier Hilfe oder Tipps erhoffe.
Folgendes:
Ich habe eine Tabelle in der Ferientage von Personen stehen (ziel_datum).
Jeder Datensatz entspricht einem Tag, der der user_id zugeordnet ist.
Diese einzelnen Tage können natürlich genehmigt werden.

Nun möchte ich diese Daten in einer View gruppieren und in Von-Bis Variante anzeigen lassen.
Dazu nutze ich einfach die MIN und MAX Funktion auf das ziel_datum für Von und Bis und gruppiere nach user_id, dem Monat des ziel_datums und der Kalenderwoche.

Nun stellt Euch vor, der Benutzer hat 2 Wochen Urlaub im August
Vom 10.08.2015 - 14.08.2015 und vom 17.08.2015 - 21.08.2015.

Mein SQL:

SELECT
    vup.user_id,
    MONTH(vup.ziel_datum) Monat,
    MIN(vup.ziel_datum) Von,
    MAX(vup.ziel_datum) Bis,
    DATEPART(wk, vup.ziel_datum) KW
FROM
    v_urlaub_personen vup
WHERE
    (vup.genehmigt_vorg >= 1
    AND vup.genehmigt_personal >= 1)
    AND vup.user_id = 2
    AND vup.pflichturlaub = 0
GROUP BY
    vup.user_id,
    MONTH(vup.ziel_datum),
    DATEPART(wk, vup.ziel_datum)

gibt nun folgendes zurück:
user_id: 2 | Monat: 8 | Von: 10.08.2015 | Bis: 14.08.2015 | KW: 33
user_id: 2 | Monat 8 | Von: 17.08.2015 | Bis: 21.08.2015 |KW: 34

Ok. Das sieht super aus, aber, was ist denn jetzt, wenn in der KW 34 bspw. der 19.08.2015 nicht genehmigt wurde. Macht zwar nicht unbedingt Sinn, ich würde es aber gerne berücksichtigen.
(Eine der genehmigt-Spalten braucht also nur 0 sein, dann fällt der Tag raus.)
Mein SQL gibt in diesem Fall aber gleiches Ergebnis wie oben aus.
Theroretisch müsste ich ja jetzt 3 Datensätze erhalten:

user_id: 2 | Monat: 8 | Von: 10.08.2015 | Bis: 14.08.2015 | KW: 33
user_id: 2 | Monat 8 | Von: 17.08.2015 | Bis: 18.08.2015 |KW: 34
user_id: 2 | Monat 8 | Von: 20.08.2015 | Bis: 21.08.2015 |KW: 34

Wie kann ich das lösen?

Besten Dank für Eure Tipps.

monsee

 

[Distrilec]

Hier mal mein "Ansatz" (in Oracle)... Vllt. regt es ja ein paar Ideen an
Bisschen mit Rekursion rumgefiddelt... Und bestimmt nicht die beste Lösung

Die erste CTE ist nur für die Daten (hast uns ja keine gegeben)...

Edit:
Noch ein bisschen hinzugefügt...

Spoiler: Neuer Code

With dat as (Select  '1' as user_id , to_date('12.03.2015', 'DD.MM.YYYY') as zieldatum ,'X' as genehmigt_vorg,'X' as genehmigt_personal, 'JA' as pflichturlaub from dual union all
Select '1',to_date('13.03.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '1',to_date('14.03.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '1',to_date('15.03.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '1',to_date('16.03.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '1',to_date('17.03.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '1',to_date('18.03.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '1',to_date('19.03.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '1',to_date('21.03.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '1',to_date('22.03.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '1',to_date('23.03.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '1',to_date('24.03.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '1',to_date('25.03.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '1',to_date('26.03.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '1',to_date('27.03.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '1',to_date('28.03.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '1',to_date('29.03.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '1',to_date('30.03.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '1',to_date('31.03.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '1',to_date('13.05.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '1',to_date('14.05.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '1',to_date('15.05.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '1',to_date('16.05.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '1',to_date('17.05.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '1',to_date('18.05.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '1',to_date('19.05.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '1',to_date('20.05.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '1',to_date('21.05.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '1',to_date('22.05.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
-- User 2
Select '2',to_date('13.03.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '2',to_date('14.03.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '2',to_date('15.03.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '2',to_date('16.03.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '2',to_date('17.03.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '2',to_date('18.03.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '2',to_date('19.03.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '2',to_date('21.03.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '2',to_date('22.03.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '2',to_date('23.03.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '2',to_date('24.03.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '2',to_date('25.03.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '2',to_date('26.03.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '2',to_date('27.03.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '2',to_date('28.03.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '2',to_date('29.03.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '2',to_date('30.03.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '2',to_date('31.03.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '2',to_date('13.05.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '2',to_date('14.05.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '2',to_date('15.05.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '2',to_date('16.05.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '2',to_date('17.05.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '2',to_date('18.05.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '2',to_date('19.05.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '2',to_date('20.05.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '2',to_date('21.05.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual union all
Select '2',to_date('22.05.2015', 'DD.MM.YYYY'),'X','X', 'JA' from dual),
recursive as (
Select connect_by_root(zieldatum) as temp,
       user_id,
       zieldatum,
       genehmigt_vorg,
       genehmigt_personal,
       pflichturlaub
From   dat
Connect By zieldatum = prior zieldatum + 1)

Select t.user_id,
       t.user_urlaub_nummer,
       case
          when to_char(t.zieldatum_min, 'MM') <> to_char(t.zieldatum_max, 'MM') then
             to_char(t.zieldatum_min, 'MM') || ' - ' || to_char(t.zieldatum_max, 'MM')
          else to_char(t.zieldatum_min, 'MM')
       end as monat,
       case
          when to_char(t.zieldatum_min, 'IW') <> to_char(t.zieldatum_max, 'IW') then
             to_char(t.zieldatum_min, 'IW') || ' - ' || to_char(t.zieldatum_max, 'IW')
          else to_char(t.zieldatum_min, 'IW')
       end as kalenderwoche,
       t.zieldatum_min as von,
       t.zieldatum_max as bis
From (
Select rank() over (partition by rec.user_id order by rec.temp asc) as user_urlaub_nummer,
       min(rec.user_id) as user_id,
       min(rec.zieldatum) as zieldatum_min,
       max(rec.zieldatum) as zieldatum_max,
       min(rec.genehmigt_vorg) as genehmigt_vorg,
       min(rec.genehmigt_personal) as genehmigt_personal,
       min(rec.pflichturlaub) as pflichturlaub
From   recursive rec

Where not exists (Select 1 from recursive where zieldatum = rec.temp and temp <> rec.temp)

Group By rec.temp,
         rec.user_id) t

 

[ukulele]

Ich dachte auch erst an den Einsatz von CTE / Rekursion aber eigentlich ist das nicht nötig.

/*
CREATE TABLE test(datum SMALLDATETIME);

INSERT INTO test(datum) VALUES('2015-10-08 00:00:00.000');
INSERT INTO test(datum) VALUES('2015-11-08 00:00:00.000');
INSERT INTO test(datum) VALUES('2015-12-08 00:00:00.000');
INSERT INTO test(datum) VALUES('2015-13-08 00:00:00.000');
INSERT INTO test(datum) VALUES('2015-14-08 00:00:00.000');
INSERT INTO test(datum) VALUES('2015-17-08 00:00:00.000');
INSERT INTO test(datum) VALUES('2015-18-08 00:00:00.000');
INSERT INTO test(datum) VALUES('2015-20-08 00:00:00.000');
INSERT INTO test(datum) VALUES('2015-21-08 00:00:00.000');
*/

WITH t AS (
SELECT    test.datum,
        vorgaenger.datum AS vorher,
        nachfolger.datum AS nachher
FROM    test
LEFT JOIN test vorgaenger
ON        test.datum = vorgaenger.datum - 1
LEFT JOIN test nachfolger
ON        test.datum = nachfolger.datum + 1
        )

SELECT    datum AS beginn,
        NULL AS ende
FROM    t
WHERE    vorher IS NULL
AND        nachher IS NULL
UNION ALL
SELECT    t0.datum AS beginn,
        (    SELECT    min(t1.datum)
            FROM    t t1
            WHERE    t1.nachher IS NULL
            AND        t1.datum > t0.datum ) AS ende
FROM    t t0
WHERE    t0.vorher IS NULL

WITH erzeugt nur eine Temporäre Tabelle, das sollte man am besten in eine Sicht auslagern, ansonsten muss man nur noch nach user_id unterscheiden.

 

[Distrilec]

Die Idee ist Grundstäzlich gut...
Aber zumindest bei mir bekomme ich ein falsches ergebnis??
Sollte Pos 2 und 3 nicht zusammen hängen?

BEGINN ENDE
1 14.08.2015 17.08.2015
2 18.08.2015 20.08.2015
3 21.08.2015

 

[ukulele]

Ja Vorzeichen und so

/*
CREATE TABLE test(datum SMALLDATETIME);

INSERT INTO test(datum) VALUES('2015-10-08 00:00:00.000');
INSERT INTO test(datum) VALUES('2015-11-08 00:00:00.000');
INSERT INTO test(datum) VALUES('2015-12-08 00:00:00.000');
INSERT INTO test(datum) VALUES('2015-13-08 00:00:00.000');
INSERT INTO test(datum) VALUES('2015-14-08 00:00:00.000');
INSERT INTO test(datum) VALUES('2015-17-08 00:00:00.000');
INSERT INTO test(datum) VALUES('2015-18-08 00:00:00.000');
INSERT INTO test(datum) VALUES('2015-20-08 00:00:00.000');
INSERT INTO test(datum) VALUES('2015-21-08 00:00:00.000');
*/

WITH t AS (
SELECT    test.datum,
        vorgaenger.datum AS vorher,
        nachfolger.datum AS nachher
FROM    test
LEFT JOIN test vorgaenger
ON        test.datum - 1 = vorgaenger.datum
LEFT JOIN test nachfolger
ON        test.datum + 1 = nachfolger.datum
        )

SELECT    t0.datum AS beginn,
        t1.datum AS ende
FROM    t t0
LEFT JOIN t t1
ON        t1.datum > t0.datum
AND        t1.nachher IS NULL
AND NOT EXISTS (    SELECT    1
                    FROM    t t2
                    WHERE    t2.datum > t0.datum
                    AND        t2.nachher IS NULL
                    AND        t2.datum < t1.datum )
WHERE    t0.vorher IS NULL