SQL Query mit Datumsbehandllung

P

poli_hf

Benutzer
Beiträge
11
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?
 
Werbung:
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 ;-)
 
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?
 
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.
 
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);
 
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);
 
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
 
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
 
:)
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. ??
 
poli_hf schrieb:
Ausgehend von der obigen Tabelle ist der Saldo 31.01. bei 0 weil alle Zugänge auch Abgegangen sind. ??
Zum Vergrößern anklicken....
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
 
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.
 
ukulele schrieb:
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
Zum Vergrößern anklicken....

Hat leider nichts gebracht
 
Werbung:
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.
Datum Saldo
----------------------- -----------
2016-01-01 00:00:00.000 2
2016-01-02 00:00:00.000 2
2016-01-03 00:00:00.000 2
2016-01-04 00:00:00.000 3
2016-01-05 00:00:00.000 5
2016-01-06 00:00:00.000 5
2016-01-07 00:00:00.000 5
2016-01-08 00:00:00.000 4
2016-01-09 00:00:00.000 4
2016-01-10 00:00:00.000 6
2016-01-11 00:00:00.000 5
2016-01-12 00:00:00.000 5
2016-01-13 00:00:00.000 5
2016-01-14 00:00:00.000 4
2016-01-15 00:00:00.000 4
2016-01-16 00:00:00.000 3
2016-01-17 00:00:00.000 3
2016-01-18 00:00:00.000 3
2016-01-19 00:00:00.000 3
2016-01-20 00:00:00.000 3
2016-01-21 00:00:00.000 3
2016-01-22 00:00:00.000 2
2016-01-23 00:00:00.000 2
2016-01-24 00:00:00.000 2
2016-01-25 00:00:00.000 2
2016-01-26 00:00:00.000 2
2016-01-27 00:00:00.000 2
2016-01-28 00:00:00.000 3
2016-01-29 00:00:00.000 3
2016-01-30 00:00:00.000 3
2016-01-31 00:00:00.000 3

(31 Zeile(n) betroffen)
Zum Vergrößern anklicken....
Jetzt wüsste ich nicht wie das an einigen Tagen falsch und an anderen richtig sein könnte.
 
Zurück
Oben