Verschiedene Datumswerte analysieren und korrekt ausgeben

danke @ukulele
ich werde nächste Woche mal dein Vorschlag probieren.

aber du hast recht, das war ein Schreibfehler von mir, die Zeile lautete bis 2021-03-31.

danke auf jedenfall für so viel Mühe an euch beiden.
 
Werbung:
Achso, datediff() könntest du mit dateadd() ersetzen und dann auf genau 3 Monate prüfen:
Code: 
WITH tabelle(Personalnr,von,bis) AS (
    SELECT 11917,convert(DATE,'2024-03-18',23),convert(DATE,'2025-06-17',23) UNION ALL
    SELECT 11917,'2023-09-13','2023-12-12' UNION ALL
    SELECT 11917,'2022-06-13','2022-09-12' UNION ALL
    SELECT 11917,'2021-12-07','2022-03-06' UNION ALL
    SELECT 11917,'2021-04-01','2021-12-06' UNION ALL
    SELECT 11917,'2020-09-07','2020-03-31' UNION ALL
    SELECT 11917,'2020-03-03','2020-06-01' UNION ALL
    SELECT 11917,'2020-01-31','2020-03-02' UNION ALL
    SELECT 11917,'2020-01-28','2020-01-30' UNION ALL
    SELECT 11917,'2019-03-04','2020-01-27' UNION ALL
    SELECT 11917,'2018-12-03','2019-03-03'
    )
SELECT    Personalnr,
        sum_unterbrechung,
        min(von) AS min_von,
        max(bis) AS min_bis
FROM    (
SELECT    *,
        sum(unterbrechung) OVER (PARTITION BY Personalnr ORDER BY von) AS sum_unterbrechung
FROM    (

SELECT    *,
--        lag(bis) OVER (PARTITION BY Personalnr ORDER BY von) AS lagbis,
        ( CASE WHEN dateadd(month,3,lag(bis) OVER (PARTITION BY Personalnr ORDER BY von)) <= von THEN 1 ELSE 0 END ) AS unterbrechung
FROM    tabelle

        ) t1

        ) t2
GROUP BY Personalnr,sum_unterbrechung
ORDER BY Personalnr,sum_unterbrechung DESC
 
Hallo @ukulele

ich bin leider erst heute richtig dazu gekommen und musste feststellen, das ich nicht ganz das gewünschte Ergebnis habe, muss aber gestehen, dass ich vergessen habe, dass die dreimonatige Unterbrechung auch auf Kunden zurückzuführen ist. Habe den Code wie folgt abgeändert:

SQL: 
SELECT  t3.personalnr,
                    t3.kundennr,
                    t3.sum_unterbrechung,
                    min(t3.datumvon) AS min_von,
                    max(t3.datumbis) AS min_bis
                FROM   
                    (
                        SELECT    t2.personalnr, t2.kundennr, t2.datumvon, t2.datumbis, sum(t2.unterbrechung) OVER (PARTITION BY t2.personalnr, t2.kundennr ORDER BY t2.datumvon, t2.kundennr) AS sum_unterbrechung
                        FROM    (
                            select ep.personalnr, k.kundennr, ep.datumvon, ep.datumbis, CASE WHEN cast(lag(ep.datumvon) OVER (PARTITION BY ep.personalnr, k.kundennr ORDER BY ep.datumvon, k.kundennr) as date) + interval '3 Month' <= cast(ep.datumvon as date) THEN 1 ELSE 0 END AS unterbrechung
                            from   einspers ep
                            inner join auftrag a on ep.auftragnr = a.auftragnr
                            inner join kunden k on k.kundennr = a.kundennr
                        ) t2

                    ) t3
                where t3.personalnr = 11917 and t3.kundennr = 143441
                GROUP BY t3.personalnr, t3.kundennr, t3.sum_unterbrechung
                ORDER BY t3.personalnr, t3.kundennr, t3.sum_unterbrechung desc

Ergebnis:

1712914556742.png

Es müsste doch aber Zeile 8 bis Zeile 6 zusammenfassen sowie 5 bis 3 und Zeile 2 bis 1 und Zeile 0 extra, im Prinzip aus den 9 Zeilen dürften doch nur 4 werden.
 
Du hast da stehen
Code: 
... CASE WHEN cast(lag(ep.datumvon) OVER (PARTITION BY ...
Ich habe in meinem Code an der selben Stelle
Code: 
... CASE WHEN dateadd(month,3,lag(bis) OVER (PARTITION BY ...
Probiers mal mit ep.datumbis
 
sooo, das ganze geht weiter, dachte ich bekomme das selber hin, mit dem bereits gelernten, aber so richtig doch nicht.

einsatzvoneinsatzbisstufe
2018-12-032020-01-272019-01-14
2018-12-032020-01-272019-03-03
2018-12-032020-01-272019-05-03
2018-12-032020-01-272019-07-03
2018-12-032020-01-272019-09-03
2018-12-032020-01-272020-03-03

ich habe die Stufen jetzt für die Einsätze errechnet, aber wie bekomme ich das jetzt in eine logische Darstellung:

einsatzvoneinsatzbis
2018-12-032019-01-13
2019-01-142019-03-02
2019-03-032019-05-02
2019-05-032019-07-02
2019-07-032019-09-02
2019-09-032020-01-27

Es kann durchaus vorkommen, dass der Wert in "stufe" null ist.

Löst man das wieder mit lag()? ich hatte an lead und greatest gedacht, aber damit kam ich nicht zum richtigen Ergebnis.

Besten Dank an @ukulele und @akretschmer
 
Ich verliere da etwas den Faden. Zeig doch mal Testdaten, wo stufe auch NULL ist und vor allem, was genau soll da raus kommen? Am besten in SQL Fiddle oder so wie ich mit einer Test-Tabelle im WITH. Ich muss den Code ja nicht nachbauen, wenn du ihn schon hast.
 
@ukulele
hier die Datenbank auf fiddle:


Widererwartend konnte ich NULL bei Stufen nun doch ausschließen.

Ergebnis von der oben genannten Abfrage sollte sein:

personalnrkundennreinsatzvoneinsatzbis
119171434412018-12-032019-01-14
119171434412019-01-152019-03-02
119171434412019-03-032019-05-02
119171434412019-05-032019-07-02
119171434412019-07-032019-09-02
119171434412019-09-032020-01-27
119171434412020-01-282020-03-30
119171434412020-01-312020-03-02
119171434412020-03-032020-06-01
119171434412020-09-072020-10-18
119171434412020-10-192020-12-06
119171434412020-12-072021-02-06
119171434412021-02-072021-03-31
119171434412021-04-012021-04-06
119171434412021-04-072021-06-06
119171434412021-06-072021-12-06
119171434412021-12-072022-03-06
119171434412022-06-132022-07-24
119171434412022-07-252022-09-12
119171434412022-09-132022-11-12
119171434412022-11-132023-03-12
119171434412023-03-132023-09-12
119171434412023-09-132023-12-12
119171434412024-03-182024-04-28
119171434412024-04-292024-06-17
119171434412024-06-182024-08-17
119171434412024-08-182024-10-17
119171434412024-10-182024-12-17
119171434412024-12-182025-06-17


Im Prinzip, wenn Stufe kleiner ist als einsatzbis und stufe größer ist als einsatzvon, dann soll der wert von einsatzbis auf 1 Tag vor Stufe enden und die nächste Zeile mit Stufe beginnen.

falls du schon arbeitest, warte kurz, habe estwas oprimieren können die vorherige abfrage!
 
Zuletzt bearbeitet:
Also hast du erreicht, was du machen wolltest? isnull() ist dein Freund, denke ich. Kann aber auch grade nicht viel dazu beitragen :)
 
ich verringere gerade noch die Datensätze, mal schauen ob ichs dann selber schaffe, sonst würde ich mich nochmal melden, hab gerade paar ideen 🤟
 
@ukulele
ich muss nochmal nerven, hier die komplette Tabelle:

FREE AI-Enhanced Online PostgreSQL Compiler - For learning & practice

Explore our free online PostgreSQL editor to practice and execute SQL. AI-enhanced to chat, explain, and generate code. Start coding smarter today!
sqlfiddle.com sqlfiddle.com

Ich möchte aus diesen Datensätzen immer eine Unterbrechung, also dann, wenn zwischen einsatzvon und einsatzbis ein Ergeignis passiert von den anderen Datumsspalten, wenn sich innerhalb von einsatzvon und einsatzbis etwas ändert.

Also:
Wenn sich zwischen einsatzvon und einsatzbis etwas in den nachfolgenden Spalten ändert, dann soll Spalte phasevon (neu zu erstellen) erstellt werden und dann erneut geprüft werden, ob sich innerhalb dieses Zeitraums dann von phasevon bis einsatzbis etwas ändert, auch wichtig, wenn in stufe wert steht, muss ein erster Datensatz angelegt werden, der bis zu dieser stufe geht, beispiel für zeile 1:

einsatzvon = 03.12.2018
einsatzbis = 03.03.2019
stufe=14.1.2019

es soll dann ein Datensatz erstellt werden:
phasevon = 03.12.2018
phasebis = 13.1.2019

nächste Zeile
phasevon = 14.1.2019
phasebis = 03.03.2019

dann haben wir den nächsten Fall:
einsatzvon = 04.03.2019
einsatzbis = 27.01.2020
stufe=14.1.2019, 03.03.2019, 03.05.2019, 03.07.2019, 03.09.2019
tvvon = 01.04.2018, 01.04.2019
tvbis=31.03.2019, 31.03.2020
daraus sollte folgendes werden:
phasevon = 04.03.2019
phasebis = 31.03.2019
phasevon = 01.04.2019
phasebis = 02.05.2019
phasevon = 03.05.2019
phasebis = 02.07.2019
phasevon = 03.07.2019
phasebis = 02.09.2019
phasevon = 03.09.2019
phasebis = 27.01.2020


Am Ende soll es (auf Basis dieser Daten) so aussehen:

PersonalnummerKundennummerPhase vonPhasebis
119171434412018-12-032019-01-13
119171434412019-01-142019-03-03
119171434412019-03-042019-03-31
119171434412019-04-012019-05-02
119171434412019-05-032019-07-02
119171434412019-07-032019-09-02
119171434412019-09-032020-01-27

und das immer so weiter. Ereignisse unterbrechen quasi einsatzvon und einsatzbis und machen daraus eine phase.

Wäre echt super wenn wir das irgendwie schaffen :)
@ukulele und @akretschmer bitte gebt mir ne Anregung 💟
 
Also erstmal halten wir fest: Deine Daten sind Grütze. Viel redundant, viel unklar. Wenn du das vorher sauber strukturieren könntest...

Wenn nicht dann müsstest du ein paar Dinge klar stellen.

Wenn ich die Datensätze zu den ersten beiden genannten Fällen betrachte dann interessieren mich erstmal nur die Spalten
personalnr,kundenkr,einsatzvon,einsatzbis
die bilden zusammen eine Gruppe und werden unterbrochen von
tvon/tbis
was wiederrum unterbrochen wird von
stufevon
und zwar in dieser Hirachie. Es ist gegeben, das stufevon immer zwischen tvon/tbis liegt und tvon/tbis immer zwischen einsatzvon/einsatzbis. Das würde ich zumindest erwarten, ist aber nicht so. Warum liegt tvon/tbis mal innerhalb, mal außerhalb des Bereichs von einsatzvon/einsatzbis? Wer ist hier von wem abhängig?

Die Unterbrechung stufevon ist relativ einfach, weil keine Spalte stufebis gegeben ist. stufebis kann quasi abgeleitet werden vom nächsten Datensatz über eine Window-Funktion. Ist denn bei tvon/tbis das tbis relevant, also kann tbis mehr als einen Tag von tvon des nächsten Datensatzes abweichen? Ich gehe mal von nein aus, da es in den beiden betrachteten Fällen nicht so ist.

Was eine mögliche Lösung der Aufgabe angeht würde ich erstmal fest halten das diese nichts mit der Lösung aus #17 zu tun hat. Ich weiß jetzt nicht genau ob du den Schritt in #17 vorher machst, ich gehe davon aus das die Tabelle test dein Datenbestand ist. Wenn das vorher schon berechnet wird, wäre eventuell ein ganz anderer Ansatz zielführender.
 
Hallo @ukulele,

danke für deine ausführliche Nachricht. Ich werde diese in einzelnen Passagen beantworten:

ukulele schrieb:
Wenn ich die Datensätze zu den ersten beiden genannten Fällen betrachte dann interessieren mich erstmal nur die Spalten
personalnr,kundenkr,einsatzvon,einsatzbis
die bilden zusammen eine Gruppe und werden unterbrochen von
tvon/tbis
was wiederrum unterbrochen wird von
stufevon
und zwar in dieser Hirachie. Es ist gegeben, das stufevon immer zwischen tvon/tbis liegt und tvon/tbis immer zwischen einsatzvon/einsatzbis. Das würde ich zumindest erwarten, ist aber nicht so. Warum liegt tvon/tbis mal innerhalb, mal außerhalb des Bereichs von einsatzvon/einsatzbis? Wer ist hier von wem abhängig?
Zum Vergrößern anklicken....
Ein Mitarbeiter ist in einem Kunden im Einsatz. Dieser Einsatz kann eine lange Zeit dauern. In dieser Zeit können sich viele Dinge ändern, zum Beispiel refvon und refbis, evon und ebis, ebvon und ebbis, mvon und mbis, ivon und ibis, uvon und ubis, tvon und tbis sowie eine neue stufe mit stufevon.

Es gibt leider keine Hierachie, jedes Datum kann vor oder nach einem anderen sein, leider, einzig was feststeht, ist einsatzvon und einsatzbis.

ukulele schrieb:
Die Unterbrechung stufevon ist relativ einfach, weil keine Spalte stufebis gegeben ist. stufebis kann quasi abgeleitet werden vom nächsten Datensatz über eine Window-Funktion. Ist denn bei tvon/tbis das tbis relevant, also kann tbis mehr als einen Tag von tvon des nächsten Datensatzes abweichen? Ich gehe mal von nein aus, da es in den beiden betrachteten Fällen nicht so ist.
Zum Vergrößern anklicken....
Bei folgenden Spalten ist es sicher, dass das Ende, auch gleichzeitig einen Tag später ein neuer Anfang ist:
tvon und tbis

Alle anderen können, müssen aber nicht einen Anfang und kein Ende haben, oder aber einen Anfang und ein Ende und damit kein erneuten Anfang.

ukulele schrieb:
Was eine mögliche Lösung der Aufgabe angeht würde ich erstmal fest halten das diese nichts mit der Lösung aus #17 zu tun hat. Ich weiß jetzt nicht genau ob du den Schritt in #17 vorher machst, ich gehe davon aus das die Tabelle test dein Datenbestand ist. Wenn das vorher schon berechnet wird, wäre eventuell ein ganz anderer Ansatz zielführender.
Zum Vergrößern anklicken....
Exakt, ich habe hier nicht den Lösungsansatz von #17 genommen. Ich hatte bei der ursprünglichen Frage leider nicht berücksichtigt, dass ich später weitere Datumsfelder benötige - die ursprüngliche Anforderung war anders.

Also ist die Tabelle auf fiddle meine Ausgangsbasis.

Aber, du hast Recht mit den Daten. Ich werde heute den Tag damit verbringen die Daten neu zu gliedern, ob mir das gelingt, weiß ich noch nicht, werde aber ein Update gaben.
 
Werbung:
Code: 
WITH t1 AS (
    SELECT    personalnr,
            kundennr,
            einsatzvon,
            einsatzbis,
            einsatzvon AS eventdate,
            'einsatzvon' AS eventtype
    FROM    test
    UNION ALL
    SELECT    personalnr,
            kundennr,
            einsatzvon,
            einsatzbis,
            einsatzbis AS eventdate,
            'einsatzbis' AS eventtype
    FROM    test
    UNION ALL
    SELECT    personalnr,
            kundennr,
            einsatzvon,
            einsatzbis,
            tvon AS eventdate,
            'tvon' AS eventtype
    FROM    test
    WHERE    tvon IS  NOT NULL
    UNION ALL
    SELECT    personalnr,
            kundennr,
            einsatzvon,
            einsatzbis,
            stufevon,
            'stufevon'
    FROM    test
    WHERE    stufevon IS NOT NULL
    ), t2 AS (
    SELECT    DISTINCT *
    FROM    t1
    WHERE    eventdate >= einsatzvon
    AND        eventdate < einsatzbis
    )
SELECT    personalnr,
        kundennr,
        einsatzvon,
        einsatzbis,
        eventtype AS phasegrund,
        eventdate AS phasevon,
        isnull(
            dateadd(day,-1,
                lead(eventdate) OVER (PARTITION BY personalnr,kundennr,einsatzvon,einsatzbis ORDER BY eventdate)
            )
        ,einsatzbis) AS phasebis
FROM    t2
ORDER BY personalnr,kundennr,einsatzvon,einsatzbis,eventdate
Weitere Unterbrechungs-"Events" wie refvon müsstest du mit weiteren UNION Abfragen anhängen. Das DISTINCT stört mich noch ein wenig aber ich habe irgendwie keine elegantere Lösung im Kopf.
 
Zurück
Oben