Zeitspanne nach Kalenderwoche splitten

D

dash_

Benutzer
Beiträge
15
Hallo liebe Forumsmitglieder,

ich habe folgendes Problem. Meine Rohdaten sehen wie folgt aus:
1281612.jpg

Jedoch soll ich jetzt die Menge anhand der Zeitspanne auch noch in die richtige Kalenderwoche gruppieren/splitten:
1281614.jpg

Zu beachten, er soll nur von Mo-Fr die Tage nehmen und anhand der Daten auch die Menge richtig berechnen, zudem die Kalenderwoche angeben. Ist mein Vorhaben über MS SQL möglich oder muss ich mir etwas über den Berichts-Generator 3.0 basteln? Sollte dies möglich sein, wäre ich über jegliche Denkanstöße sehr dankbar. Denn ich verzweifle schon am splitten der einzelnen Datensätze in die richtige Kalenderwoche.

Vielen Dank im Voraus für jegliche Denkanstöße.

Mit freundlichen Grüßen

dash_
 
Werbung:
Eine wunderschöne Übung:
Code: 
WITH tabelle(pauftrag,start,ende,menge) AS (
   SELECT 200,cast('2018-06-12' AS DATE),cast('2018-06-15' AS DATE),400 UNION ALL
   SELECT 201,'2018-06-13','2018-06-20',600 UNION ALL
   SELECT 202,'2018-06-14','2018-06-27',1500 UNION ALL
   SELECT 203,'2018-07-05','2018-07-06',100
   ), t1(pauftrag,start,ende,menge) AS (
   SELECT   tabelle.pauftrag,
           tabelle.start,
           tabelle.ende,
           tabelle.menge
   FROM   tabelle
   UNION ALL
   SELECT   t1.pauftrag,
           dateadd(day,1,t1.start),
           t1.ende,
           t1.menge
   FROM   t1
   WHERE   dateadd(day,1,t1.start) <= t1.ende
   )
SELECT   t1.pauftrag,
       datepart(week,t1.start) AS kw,
       t1.menge / sum(count(*)) OVER (PARTITION BY t1.pauftrag) * count(*) AS menge
FROM   t1
WHERE   datepart(dw,t1.start) BETWEEN 1 AND 5
GROUP BY t1.pauftrag,datepart(week,t1.start),t1.menge
ohne die Tabelle mit den Demo Daten:
Code: 
WITH
t1(pauftrag,start,ende,menge) AS (
   SELECT   tabelle.pauftrag,
           tabelle.start,
           tabelle.ende,
           tabelle.menge
   FROM   tabelle
   UNION ALL
   SELECT   t1.pauftrag,
           dateadd(day,1,t1.start),
           t1.ende,
           t1.menge
   FROM   t1
   WHERE   dateadd(day,1,t1.start) <= t1.ende
   )
SELECT   t1.pauftrag,
       datepart(week,t1.start) AS kw,
       t1.menge / sum(count(*)) OVER (PARTITION BY t1.pauftrag) * count(*) AS menge
FROM   t1
WHERE   datepart(dw,t1.start) BETWEEN 1 AND 5
GROUP BY t1.pauftrag,datepart(week,t1.start),t1.menge
Jetzt fehlen da die Angaben Start- und Enddatum, interessieren die überhaupt? Für die Mengenberechnung ist ja nur die Anzahl der Tage relevant.

PS: Rundungen können natürlich irgendwann zu Differenzen führen.
 
Hallo Ukulele,

danke erstmal für eine sehr schnelle Rückmeldung. Start- und Enddatum sind auch interessant, ja. Ich werde mir mal deine Beispiele anschauen und auf meine Auswertung anpassen.
Ich hoffe ich komme damit klar. Vielen Dank.

Gruß
dash_
 
Hallo nochmal,

ich habe soweit meine Wünsche mithilfe deines Codebeispiels umsetzen können.
Jedoch würde ich gerne das noch mit dem Datum hinbekommen, dass es laufend angezeigt wird:
Das heißt, wenn Startdatum 13.06.2018 beginnt und am 28.06.2018 endet, dass er mir die Daten wie folgt anzeigt und die Arbeitstage mit ausgibt:

Rohdaten:

pauftrag kw von bis menge AT
200 - 13.06.2018 28.06.2018 6000 -

Ergebnis:
pauftrag kw von bis menge AT
200 24 13.06.2018 15.06.2018 1500 3
200 25 18.06.2018 22.06.2018 2500 5
200 26 25.06.2018 28.06.2018 2000 4


Ist das auch möglich? Über jegliche Denkanstöße bin ich sehr dankbar.
 
Also den ursprünglichen Auftragszeitraum kannst du natürlich noch dazu joinen. Komplizierter wirds, wenn du den Zeitraum anpassen willst so das er nur den Anteil in der jeweiligen KW abbilden soll aber auch das geht ansich. Was solls denn werden dann passe ich das mal an.
 
Es geht darum um die Menge der verbrauchten Materialien auszurechnen um einen wöchtenlichen Forecast des Verbrauchs zu haben der einzelnen Produktionsaufträge.
 
Ne das hab ich wohl verstanden, du teilst die Menge auf KWs auf das passt ja auch schon. Ich wollte aber wissen ob du auch den Zeitraum an die jeweilige KW anpassen willst, das ist etwas knifflig.

Ich habe das mal etwas erweitert.
Zum einen weil mich das mit den Rundungsdifferenzen gestört hat, dafür habe ich in t2 eine CASE-Anweisung eingebaut die den Differenzbetrag der durch Rundungen entsteht auf den letzten Teilbetrag aufschlägt.
Zum Anderen habe ich den ursprünglichen Zeitraum gejoint und mich an der Berechnung des Teilzeitraumes versucht. Letzteres ist ein bischen wirr geraten aber sollte den zu KW und Menge passenden Zeitraum zeigen, also maximal Mo bis Fr der jeweiligen KW.
Code: 
WITH tabelle(pauftrag,start,ende,menge) AS (
   SELECT 200,cast('2018-06-12' AS DATE),cast('2018-06-15' AS DATE),400 UNION ALL
   SELECT 201,'2018-06-13','2018-06-20',333 UNION ALL
   SELECT 202,'2018-06-14','2018-06-27',1500 UNION ALL
   SELECT 203,'2018-07-05','2018-07-06',100
   ), t1(pauftrag,start,ende,menge) AS (
   SELECT   tabelle.pauftrag,
           tabelle.start,
           tabelle.ende,
           tabelle.menge
   FROM   tabelle
   UNION ALL
   SELECT   t1.pauftrag,
           dateadd(day,1,t1.start),
           t1.ende,
           t1.menge
   FROM   t1
   WHERE   dateadd(day,1,t1.start) <= t1.ende
   ), t2(pauftrag,start,kw,menge) AS (
   SELECT   t1.pauftrag,
           convert(DATE,dateadd(week,0,dateadd(day,1-datepart(dw,t1.start),datediff(day,0,t1.start)))),
           datepart(week,t1.start),
           (   CASE
               WHEN   t1.menge / sum(count(*)) OVER (PARTITION BY t1.pauftrag) * sum(count(*)) OVER (PARTITION BY t1.pauftrag) = t1.menge
               OR       datepart(week,t1.start) < max(datepart(week,t1.start)) OVER (PARTITION BY t1.pauftrag)
               THEN   /*Das hier ist die eigentliche Berechnung der anteiligen Menge*/ t1.menge / sum(count(*)) OVER (PARTITION BY t1.pauftrag) * count(*)
               ELSE   /*Hier wird auf die letzte KW mit  anteiliger Menge der Restbetrag aufgeschlagen*/ t1.menge / sum(count(*)) OVER (PARTITION BY t1.pauftrag) * count(*) +
                       t1.menge - t1.menge / sum(count(*)) OVER (PARTITION BY t1.pauftrag) * sum(count(*)) OVER (PARTITION BY t1.pauftrag)
               END )
   FROM   t1
   WHERE   datepart(dw,t1.start) BETWEEN 1 AND 5
   GROUP BY t1.pauftrag,dateadd(week,0,dateadd(day,1-datepart(dw,t1.start),datediff(day,0,t1.start))),datepart(week,t1.start),t1.menge
   )
SELECT   t2.pauftrag,
        --Das ist der gesammte Zeitraum, wird einfach durch einen Join der originären Tabelle wieder als Spalten dazu geholt
       t3.start,
       t3.ende,
       --Das ist nur der Zeitraum auf den der berechnete Anteil entfällt
       (   CASE
           WHEN   datepart(week,t3.start) = t2.kw
           THEN   t3.start
           ELSE   t2.start
           END ) AS start_teilzeitraum,
       (   CASE
           WHEN   datepart(week,t3.ende) = t2.kw
           THEN   (CASE WHEN datepart(dw,t3.ende) > 5 THEN dateadd(day,(datepart(dw,t3.ende)*-1+5),t3.ende) ELSE t3.ende END)
           ELSE   dateadd(day,5,t2.start)
           END ) AS ende_teilzeitraum,
       t2.kw,
       t2.menge
FROM   t2
INNER JOIN tabelle t3
ON       t2.pauftrag = t3.pauftrag
 
Hallo,

erstmal danke für deine Mühen. Ich werde mir den Code gleich mal angucken und überprüfen, ob ich damit mein Ziel erreiche. :)
 
Hallo Ukulele,

wollte mich nochmal bei dir bedanken. Habe es wie gewünscht hinbekommen.
Ich musste zwar Zeile für Zeile durchgehen, damit ich es verstehe etc., aber es hat funktioniert und ich konnte damit viel lernen.
Die Berechnung der Menge habe ich anders gemacht, da die Wochenenden nicht mitgerechnet werden sollten.
Hier mein Code, falls jemand in Zukunft das auch so braucht wie ich:

Code: 
--Rohdaten
;WITH data(Artikel,Bezeichnung,Fertigungsartikel,Produktionsauftrag,Auftragsstatus,Gesamtmenge,Starttermin,Liefertermin) AS(
   SELECT '600001','Bezeichnung1','110049','P100011','Aktiv',400,cast('2018-06-28' AS DATE),cast('2018-07-01' AS DATE) UNION ALL
   SELECT '600068','Bezeichnung2','110050','P100012','Aktiv',600,'2018-06-27','2018-07-10' UNION ALL
   SELECT '600045','Bezeichnung3','110055','P100013','Aktiv',1500,'2018-07-14','2018-08-02' UNION ALL
   SELECT '600065','Bezeichnung4','110060','P100014','Aktiv',100,'2018-07-05','2018-07-06'
),

--Kalendertabelle
kalender (Artikel,Bezeichnung,Fertigungsartikel,Produktionsauftrag,Auftragsstatus,Gesamtmenge,Starttermin,Liefertermin) AS (
SELECT
    data.Artikel,
    data.Bezeichnung,
    data.Fertigungsartikel,
    data.Produktionsauftrag,
    data.Auftragsstatus,
    data.Gesamtmenge,
    data.Starttermin,
    data.Liefertermin
FROM
    data
UNION  ALL
    SELECT
        kalender.Artikel,
        kalender.Bezeichnung,
        kalender.Fertigungsartikel,
        kalender.Produktionsauftrag,
        kalender.Auftragsstatus,
        kalender.Gesamtmenge,
        DATEADD(day,1,kalender.Starttermin),
        kalender.Liefertermin
    FROM
        kalender
    WHERE
        kalender.Starttermin < Liefertermin
),

--Kalenderwochen ermitteln vom Zeitraum zwischen Starttermin und Liefertermin
Zwischenzeitraum(Artikel, Produktionsauftrag,Starttermin, KW) AS (
SELECT
    kalender.Artikel,
    kalender.Produktionsauftrag,
    CONVERT(date,DATEADD(WEEK,0,DATEADD(DAY,1-DATEPART(DW,kalender.Starttermin),DATEDIFF(DAY,0,kalender.Starttermin)))),
    DATEPART(WEEK,kalender.Starttermin)
FROM
    kalender
WHERE
    DATEPART(WEEKDAY,kalender.Starttermin) NOT IN (6,7)
GROUP BY
    kalender.Artikel,kalender.Produktionsauftrag,DATEADD(WEEK,0,DATEADD(DAY,1-DATEPART(WEEKDAY,kalender.Starttermin),DATEDIFF(DAY,0,kalender.Starttermin))),DATEPART(WEEK,kalender.Starttermin)
)

--QUERY
SELECT
    Zwischenzeitraum.Artikel,
    data.Bezeichnung,
    data.Fertigungsartikel,
    Zwischenzeitraum.Produktionsauftrag,
    data.Auftragsstatus,
    data.Gesamtmenge,
    data.Starttermin,
    data.Liefertermin,
--Arbeitstage vom Gesamtzeitraum ermitteln
    SUM(DATEDIFF(DAY,CASE WHEN DATEPART(WEEK,data.Starttermin) = Zwischenzeitraum.KW THEN data.Starttermin ELSE Zwischenzeitraum.Starttermin END,CASE WHEN DATEPART(WEEK,data.Liefertermin) = Zwischenzeitraum.KW THEN (CASE    WHEN DATEPART(WEEKDAY,data.Liefertermin) > 5 THEN DATEADD(DAY,(DATEPART(WEEKDAY,data.Liefertermin)*-1+5),data.Liefertermin) ELSE data.Liefertermin END) ELSE DATEADD(DAY,4,Zwischenzeitraum.Starttermin) END) + 1) OVER (PARTITION BY Zwischenzeitraum.Produktionsauftrag,Zwischenzeitraum.Artikel) AS AT_Gesamt,
    Zwischenzeitraum.KW,
--START_Teilzeitraum ermitteln
    CASE
        WHEN DATEPART(WEEK,data.Starttermin) = Zwischenzeitraum.KW
        THEN data.Starttermin
        ELSE Zwischenzeitraum.Starttermin
    END AS START_Teilzeitraum,
--ENDE_Teilzeitraum ermitteln
    CASE
        WHEN DATEPART(WEEK,data.Liefertermin) = Zwischenzeitraum.KW
        THEN
            (CASE
                WHEN DATEPART(WEEKDAY,data.Liefertermin) > 5
                THEN DATEADD(DAY,(DATEPART(WEEKDAY,data.Liefertermin)*-1+5),data.Liefertermin)
                ELSE data.Liefertermin
            END)
        ELSE DATEADD(DAY,4,Zwischenzeitraum.Starttermin)
    END AS ENDE_Teilzeitraum,
--Arbeitstage vom Teilzeitraum ermitteln
    DATEDIFF(DAY,CASE WHEN DATEPART(WEEK,data.Starttermin) = Zwischenzeitraum.KW THEN data.Starttermin ELSE Zwischenzeitraum.Starttermin END,CASE WHEN DATEPART(WEEK,data.Liefertermin) = Zwischenzeitraum.KW THEN (CASE    WHEN DATEPART(WEEKDAY,data.Liefertermin) > 5 THEN DATEADD(DAY,(DATEPART(WEEKDAY,data.Liefertermin)*-1+5),data.Liefertermin) ELSE data.Liefertermin END) ELSE DATEADD(DAY,4,Zwischenzeitraum.Starttermin) END) + 1 AS AT_Teilzeitraum,
--Menge berechnen (Gesamtmenge / AT_Gesamt * AT_Teilzeitraum)
    (data.Gesamtmenge / (SUM(DATEDIFF(DAY,CASE WHEN DATEPART(WEEK,data.Starttermin) = Zwischenzeitraum.KW THEN data.Starttermin ELSE Zwischenzeitraum.Starttermin END,CASE WHEN DATEPART(WEEK,data.Liefertermin) = Zwischenzeitraum.KW THEN (CASE    WHEN DATEPART(WEEKDAY,data.Liefertermin) > 5 THEN DATEADD(DAY,(DATEPART(WEEKDAY,data.Liefertermin)*-1+5),data.Liefertermin) ELSE data.Liefertermin END) ELSE DATEADD(DAY,4,Zwischenzeitraum.Starttermin) END) + 1) OVER (PARTITION BY Zwischenzeitraum.Produktionsauftrag,Zwischenzeitraum.Artikel))) * (DATEDIFF(DAY,CASE WHEN DATEPART(WEEK,data.Starttermin) = Zwischenzeitraum.KW THEN data.Starttermin ELSE Zwischenzeitraum.Starttermin END,CASE WHEN DATEPART(WEEK,data.Liefertermin) = Zwischenzeitraum.KW THEN (CASE    WHEN DATEPART(WEEKDAY,data.Liefertermin) > 5 THEN DATEADD(DAY,(DATEPART(WEEKDAY,data.Liefertermin)*-1+5),data.Liefertermin) ELSE data.Liefertermin END) ELSE DATEADD(DAY,4,Zwischenzeitraum.Starttermin) END) + 1) AS Berechnete_Menge
FROM
    Zwischenzeitraum
INNER JOIN data ON (Zwischenzeitraum.Produktionsauftrag = data.Produktionsauftrag AND Zwischenzeitraum.Artikel = data.Artikel)
 
Werbung:
Ja da musste ich auch grübeln aber am Ende mach ich sowas gerne im Gegensatz zu sagen wir Pivot oder sowas. Die Wochenenden habe ich allerdings auch nicht mit eingerechnet, sondern die Mengen immer nach Werktagen aufgeteilt. Das sollte deckungsgleich sein.

Es müsste aber weitere Wege geben um zum Ziel zu kommen, möglicherweise auch performantere. Das Verstehen ist am wichtigsten und die Daten müssen am Ende passen.
 
Zurück
Oben