Information ausblenden
Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm

SQL Query mit Datumsbehandllung

Dieses Thema im Forum "Microsoft SQL Server" wurde erstellt von poli_hf, 24 Oktober 2016.

  1. poli_hf

    poli_hf Benutzer

    Hallo zusammen,

    hoffe Ihr könnt mir helfen. Hab folgende Daten in einer Tabelle:

    Datestart Dateend Type
    01.01.2016 15.01.2016 BNDL
    02.01.2016 16.01.2016 BNDL
    03.01.2016 17.01.2016 BNDL
    04.01.2016 18.01.2016 BNDL
    05.01.2016 19.01.2016 BNDL
    05.01.2016 20.01.2016 BNDL
    07.01.2016 21.01.2016 BNDL
    08.01.2016 22.01.2016 BNDL
    09.01.2016 23.01.2016 BNDL
    10.01.2016 24.01.2016 BNDL
    Ich brauche für jeden Tag ein Saldo

    Datum Saldo
    01.01.2016 1
    02.01.2016 2
    03.01.2016 3
    04.01.2016 4
    05.01.2016 6
    06.01.2016 6
    07.01.2016 8
    08.01.2016 9
    09.01.2016 10
    10.01.2016 11
    11.01.2016 11
    12.01.2016 11
    13.01.2016 11
    14.01.2016 11
    15.01.2016 10
    16.01.2016 9
    17.01.2016 8
    18.01.2016 7
    19.01.2016 6
    20.01.2016 5
    21.01.2016 4
    22.01.2016 3
    23.01.2016 2
    24.01.2016 1


    Einer ne Idee wie ich das anstelle?
     
  2. akretschmer

    akretschmer Datenbank-Guru

    der 10.1. (als Beispiel) ist aber nur 10 mal in den Daten enthalten. Fehler von Dir? Die anderen, die mit 11 dastehen, auch.

    Lösung, allerdings mit PostgreSQL:

    Code:
    test=*# select * from poli_hf ;
      datum   
    -------------------------
     [2016-01-01,2016-01-16)
     [2016-01-02,2016-01-17)
     [2016-01-03,2016-01-18)
     [2016-01-04,2016-01-19)
     [2016-01-05,2016-01-20)
     [2016-01-05,2016-01-21)
     [2016-01-07,2016-01-22)
     [2016-01-08,2016-01-23)
     [2016-01-09,2016-01-24)
     [2016-01-10,2016-01-25)
    (10 Zeilen)
    
    test=*# select d::date, count(p.*) from generate_series('2016-01-01'::date, '2016-01-24'::date,'1day'::interval)d left join poli_hf p on p.datum @> d.d::date group by 1 order by 1;
      d  | count
    ------------+-------
     2016-01-01 |  1
     2016-01-02 |  2
     2016-01-03 |  3
     2016-01-04 |  4
     2016-01-05 |  6
     2016-01-06 |  6
     2016-01-07 |  7
     2016-01-08 |  8
     2016-01-09 |  9
     2016-01-10 |  10
     2016-01-11 |  10
     2016-01-12 |  10
     2016-01-13 |  10
     2016-01-14 |  10
     2016-01-15 |  10
     2016-01-16 |  9
     2016-01-17 |  8
     2016-01-18 |  7
     2016-01-19 |  6
     2016-01-20 |  5
     2016-01-21 |  4
     2016-01-22 |  3
     2016-01-23 |  2
     2016-01-24 |  1
    (24 Zeilen)
    
    test=*#
    
    Rollback & Kaffee ;-)
     
  3. poli_hf

    poli_hf Benutzer

    Die Tabelle spiegelt Zu- und Abgänge (Pro Datensatz) dar.
    Am 01.01. hat der Kunde ein "Gut" bekommen. Bis zum 10.01.2016 hat dieser Kunde das "Gut" 10 x mal bekommen. Somit hat er 10 Stück im Bestand.
    Ab dem 15.01 gibt er pro Tag einen zurück. Das Tagessaldo reduziert somit immer um einen.

    Ob das wohl auch MS SQL geht?
     
  4. akretschmer

    akretschmer Datenbank-Guru

    sollte im Prinzip ähnlich gehen, Du joinst auf die Bedingung, daß das Datum BETWEEN von Start und Ende ist. Ob M$SQL sowas wie enerate_series() kann, um on-the-fly Datumswerte zu berechnen (wie ich es tue) weiß ich nicht. Notfalls eine Hilfstabelle erstellen.
     
  5. ukulele

    ukulele Datenbank-Guru

    Ich würde jetzt spontan die Zeiträume in einzelne Einträge umwandeln (mit WITH):
    Code:
    WITH tabelle(Datestart,Dateend,[Type]) AS (
       SELECT cast('01.01.2016' AS DATE),cast('15.01.2016' AS DATE),'BNDL' UNION ALL
       SELECT '02.01.2016','16.01.2016','BNDL'
       ), liste(Datestart,Dateend) AS (
       SELECT   t.Datestart,
           t.Dateend
       FROM   tabelle t
       UNION ALL
       SELECT   dateadd(day,1,liste.Datestart),
           liste.Dateend
       FROM   liste
       WHERE   dateadd(day,1,liste.Datestart) <= liste.Dateend
       )
    SELECT   Datestart AS Datum,
         count(*) AS Saldo
    FROM   liste
    GROUP BY Datestart
    OPTION   (MAXRECURSION 1000);
    Da sind jetzt Testdaten von mir mit drin, daher müsstest du die erste Tabelle weg lassen:
    Code:
    WITH liste(Datestart,Dateend) AS (
       SELECT   t.Datestart,
           t.Dateend
       FROM   tabelle t
       UNION ALL
       SELECT   dateadd(day,1,liste.Datestart),
           liste.Dateend
       FROM   liste
       WHERE   dateadd(day,1,liste.Datestart) <= liste.Dateend
       )
    SELECT   Datestart AS Datum,
         count(*) AS Saldo
    FROM   liste
    GROUP BY Datestart
    OPTION   (MAXRECURSION 1000);
    
     
    poli_hf gefällt das.
  6. poli_hf

    poli_hf Benutzer

    Also muss schon sagen : :D:D

    Genial. Danke dir.

    Hat funktioniert
     
  7. poli_hf

    poli_hf Benutzer

    Hab aber noch ein Problem:

    Ich brauche den Saldo an jedem TAG.
    Vom 01.01. - 31.01.
     
  8. ukulele

    ukulele Datenbank-Guru

    Ich kann ja noch eine Datumsliste erzeugen:
    Code:
    WITH tabelle(Datestart,Dateend,[Type]) AS (
       SELECT cast('01.01.2016' AS DATE),cast('15.01.2016' AS DATE),'BNDL' UNION ALL
       SELECT '02.01.2016','16.01.2016','BNDL'
       ), liste(Datestart,Dateend) AS (
       SELECT   t.Datestart,
           t.Dateend
       FROM   tabelle t
       UNION ALL
       SELECT   dateadd(day,1,liste.Datestart),
           liste.Dateend
       FROM   liste
       WHERE   dateadd(day,1,liste.Datestart) <= liste.Dateend
       ), basisliste(Datum) AS (
       SELECT   dateadd(yyyy, datediff(yyyy,0,getdate()),0)
       UNION ALL
       SELECT   dateadd(day,1,basisliste.Datum)
       FROM   basisliste
       WHERE   datepart(yyyy,dateadd(day,1,basisliste.Datum)) = datepart(yyyy,getdate())
       )
    SELECT   b.Datum,
         sum(CASE WHEN l.Datestart IS NULL THEN 0 ELSE 1 END) AS Saldo
    FROM   basisliste b
    LEFT JOIN liste l
    ON     b.Datum = l.Datestart
    GROUP BY b.Datum
    OPTION   (MAXRECURSION 1000);
    
     
  9. poli_hf

    poli_hf Benutzer

    HI,

    das Ergebnis weicht aber von meiner manuellen Berechnung ab :eek:
    Rohdaten:
    Zugang Abgang Type
    2016-01-01 2016-01-07 BNDL
    2016-01-01 2016-12-31 BNDL
    2016-01-04 2016-01-21 BNDL
    2016-01-05 2016-01-10 BNDL
    2016-01-05 2016-01-13 BNDL
    2016-01-10 2016-12-31 BNDL
    2016-01-10 2016-01-15 BNDL
    2016-01-28 2016-12-31 BNDL

    Das ist Ergebnis von der Abfrage:
    2016-01-01 2
    2016-01-02 2
    2016-01-03 2
    2016-01-04 3
    2016-01-05 5
    2016-01-06 5
    2016-01-07 5
    2016-01-08 4
    2016-01-09 4
    2016-01-10 6
    2016-01-11 5
    2016-01-12 5
    2016-01-13 5
    2016-01-14 4
    2016-01-15 4
    2016-01-16 3
    2016-01-17 3
    2016-01-18 3
    2016-01-19 3
    2016-01-20 3
    2016-01-21 3
    2016-01-22 2
    2016-01-23 2
    2016-01-24 2
    2016-01-25 2
    2016-01-26 2
    2016-01-27 2
    2016-01-28 3
    2016-01-29 3
    2016-01-30 3
    2016-01-31 3

    Mein Ergebnis :
    Datum Saldo
    01.01.2016 2
    02.01.2016 2
    03.01.2016 2
    04.01.2016 3
    05.01.2016 5
    06.01.2016 5
    07.01.2016 4
    08.01.2016 4
    09.01.2016 4
    10.01.2016 5
    11.01.2016 5
    12.01.2016 5
    13.01.2016 4
    14.01.2016 4
    15.01.2016 3
    16.01.2016 3
    17.01.2016 3
    18.01.2016 3
    19.01.2016 3
    20.01.2016 3
    21.01.2016 2
    22.01.2016 2
    23.01.2016 2
    24.01.2016 2
    25.01.2016 2
    26.01.2016 2
    27.01.2016 2
    28.01.2016 3
    29.01.2016 3
    30.01.2016 3
    31.01.2016 0
     
  10. ukulele

    ukulele Datenbank-Guru

    Dann wirst du die Ursache dafür wohl suchen müssen :)

    Guck doch einfach mal warum in deiner Berechnung der 31.01. 0 Tage hat und dann schaust du in deiner Tabelle
    Code:
    SELECT * FROM tabelle WHERE '2016-01-31' BETWEEN Zugang AND Abgang
     
  11. poli_hf

    poli_hf Benutzer

    :)
    2016-01-01 2016-01-07 BNDL
    2016-01-01 2016-12-31 BNDL
    2016-01-04 2016-01-21 BNDL
    2016-01-05 2016-01-10 BNDL
    2016-01-05 2016-01-13 BNDL
    2016-01-10 2016-12-31 BNDL
    2016-01-10 2016-01-15 BNDL
    2016-01-28 2016-12-31 BNDL

    Ausgehend von der obigen Tabelle ist der Saldo 31.01. bei 0 weil alle Zugänge auch Abgegangen sind. ??
     
  12. ukulele

    ukulele Datenbank-Guru

    Verstehe ich nicht richtig. Dein Zeitraum in der Ausgangstabelle repräsentiert doch für jeden Tag in dem Zeitraum eine 1, oder zählt der letzte Tag (also Abgang) nicht mit? Dann musst du nur <= mit < tauschen:
    Code:
    WHERE dateadd(day,1,liste.Datestart) <= liste.Dateend
    Code:
    WHERE dateadd(day,1,liste.Datestart) < liste.Dateend
     
  13. poli_hf

    poli_hf Benutzer

    Ausgehend von diesem Datenbestand:

    2016-01-01 2016-01-07 BNDL
    2016-01-01 2016-12-31 BNDL
    2016-01-04 2016-01-21 BNDL
    2016-01-05 2016-01-10 BNDL
    2016-01-05 2016-01-13 BNDL
    2016-01-10 2016-12-31 BNDL
    2016-01-10 2016-01-15 BNDL
    2016-01-28 2016-12-31 BNDL


    Datum Mein Ergebnis Anzahl
    01.01.2016 2 2
    02.01.2016 2 2
    03.01.2016 2 2
    04.01.2016 3 3
    05.01.2016 5 5
    06.01.2016 5 5
    07.01.2016 4 5
    08.01.2016 4 4
    09.01.2016 4 4
    10.01.2016 5 6
    11.01.2016 5 5
    12.01.2016 5 5
    13.01.2016 4 5
    14.01.2016 4 4
    15.01.2016 3 4
    16.01.2016 3 3
    17.01.2016 3 3
    18.01.2016 3 3
    19.01.2016 3 3
    20.01.2016 3 3
    21.01.2016 2 3
    22.01.2016 2 2
    23.01.2016 2 2
    24.01.2016 2 2
    25.01.2016 2 2
    26.01.2016 2 2
    27.01.2016 2 2
    28.01.2016 3 3
    29.01.2016 3 3
    30.01.2016 3 3
    31.01.2016 0 3
    Am 07.01., 10.01., 15.01., 21.01. und am 31.01. --> Also da wo es endet reduziert er den Saldo nicht.
     
  14. poli_hf

    poli_hf Benutzer

    Hat leider nichts gebracht
     
  15. ukulele

    ukulele Datenbank-Guru

    Er reduziert eigentlich nie irgendwo etwas. Folgendes passiert:

    1) Da ich die Ausgangstabelle nicht habe, erzeuge ich mit WITH tabelle AS ( ... alle Datensätze.
    2) Das Start-Datum aller Datensätze der Ausgangstabelle wird aufgelistet. Dazu wird jedes Datum um einen Tag solange hochgezählt, bis das hochgezählte Datum dem End-Datum entspricht. Aus einem Datensatz wie
    2016-01-01 2016-01-07 BNDL
    wird also
    2016-01-01
    2016-01-01 + 1 = 2016-01-02
    2016-01-02 + 1 = 2016-01-03
    2016-01-03 +1 = 2016-01-04
    2016-01-04 + 1 = 2016-01-05
    2016-01-05 + 1 = 2016-01-06
    2016-01-06 + 1 = 2016-01-07
    eine Liste mit 7 Datumswerten. Das macht er für jeden Zeitraum, sehr viele Tage sind also mehrfach vorhanden, für jeden Tag in jedem Zeitraum ein Datensatz.
    3) Die Tabelle "basisliste" wird ähnlich hochgezählt. Ich bestimme den ersten Tag des Jahres und zähle ihn solange hoch bis ich beim letzten Tag des Jahres bin.
    4) Ich joine auf jeden Tag des Jahres die Tage, die ich in "liste" erzeugt habe.
    5) Ich ziehe eine gruppiere nach Tag und ziehe eine Summe über alle Datensätze, zu denen per LEFT JOIN ein Datum aus "liste" kommt. Das ergibt die Schnittmenge jedes Tages zu den Zeiträumen, in denen er vorkommt.

    Wenn ich das jetzt um deine Beispieldaten aus Post #13 erweitere:
    Code:
    WITH tabelle(Datestart,Dateend,[Type]) AS (
       SELECT cast('2016-01-01' AS DATE),cast('2016-01-07' AS DATE),'BNDL' UNION ALL
       SELECT '2016-01-01','2016-12-31','BNDL' UNION ALL
       SELECT '2016-01-04','2016-01-21','BNDL' UNION ALL
       SELECT '2016-01-05','2016-01-10','BNDL' UNION ALL
       SELECT '2016-01-05','2016-01-13','BNDL' UNION ALL
       SELECT '2016-01-10','2016-12-31','BNDL' UNION ALL
       SELECT '2016-01-10','2016-01-15','BNDL' UNION ALL
       SELECT '2016-01-28','2016-12-31','BNDL'
       ), liste(Datestart,Dateend) AS (
       SELECT   t.Datestart,
           t.Dateend
       FROM   tabelle t
       UNION ALL
       SELECT   dateadd(day,1,liste.Datestart),
           liste.Dateend
       FROM   liste
       WHERE   dateadd(day,1,liste.Datestart) <= liste.Dateend
       ), basisliste(Datum) AS (
       SELECT   dateadd(yyyy,datediff(yyyy,0,getdate()),0)
       UNION ALL
       SELECT   dateadd(day,1,basisliste.Datum)
       FROM   basisliste
       WHERE   datepart(yyyy,dateadd(day,1,basisliste.Datum)) = datepart(yyyy,getdate())
       )
    SELECT   b.Datum,
         sum(CASE WHEN l.Datestart IS NULL THEN 0 ELSE 1 END) AS Saldo
    FROM   basisliste b
    LEFT JOIN liste l
    ON     b.Datum = l.Datestart
    WHERE   datepart(mm,b.Datum) = 1
    GROUP BY b.Datum
    OPTION   (MAXRECURSION 1000);
    Kommt auch genau das raus was du im Post #13 als "Mein Ergebnis" da stehen hast.
    Jetzt wüsste ich nicht wie das an einigen Tagen falsch und an anderen richtig sein könnte.
     
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden