SQL-Statement mit allen Tagen eines Monats

Advantage Database Server

Moderator: Moderatoren

Antworten
UliTs
Der Entwickler von "Deep Thought"
Der Entwickler von "Deep Thought"
Beiträge: 2828
Registriert: Fr, 10. Feb 2006 9:51
Wohnort: Aachen
Hat sich bedankt: 259 Mal
Danksagung erhalten: 12 Mal
Kontaktdaten:

SQL-Statement mit allen Tagen eines Monats

Beitrag von UliTs »

Hallo allerseits,

ich kann die Stunden eines Mitarbeiters über einen Monat wie folgt darstellen:

Code: Alles auswählen

select Nw.PnId,Nw.Tag,Sum(Nw.Stunden) TagesStunden
from Stundennachweis Nw
where Nw.PnId=1234 and year( Nw.Tag )=2016 and month( Nw.Tag )=1
group by Nw.PnId,Nw.Tag
order by Nw.Tag
Da kommt dann z.B. so etwas heraus:

Code: Alles auswählen

PnId      Tag        TagStunden
1234      04.01.2016  8,50
1234      05.01.2016  8,00
...
1234      28.01.2016  2,00
Ich möchte aber als Ergebnis eine Tabelle haben, bei der es für jeden Tag eine Zeile gibt. Also in meinem Beispiel:

Code: Alles auswählen

PnId      Tag        TagStunden
1234      01.01.2016  0,00
1234      02.01.2016  0,00
1234      03.01.2016  0,00
1234      04.01.2016  8,50
1234      05.01.2016  8,00
...
1234      28.01.2016  2,00
1234      29.01.2016  0,00
1234      30.01.2016  0,00
1234      31.01.2016  0,00
Wie kann ich das möglichst einfach mit einem SQL Statement realisieren?
-------
Mitglied XuG Cologne
Mitglied XuG Osnabrück
UliTs
Der Entwickler von "Deep Thought"
Der Entwickler von "Deep Thought"
Beiträge: 2828
Registriert: Fr, 10. Feb 2006 9:51
Wohnort: Aachen
Hat sich bedankt: 259 Mal
Danksagung erhalten: 12 Mal
Kontaktdaten:

Re: SQL-Statement mit allen Tagen eines Monats

Beitrag von UliTs »

Ich habe jetzt eine Lösung mit Hilfe eines Scriptes und einer temporären Tabelle gefunden, die ganz gut funktioniert. Wenn es jemand noch einfacher hinbekommt, bitte informieren :D .

Code: Alles auswählen

declare Tag1      date;
declare Tag2      date;
declare Cursor1   cursor;
declare HPnId     integer;
declare HTag      date;

try drop table #TmpNw; catch all end;
select Nw.PnId,Nw.Tag,Sum(Nw.Stunden) TagesStunden
into #TmpNw
from Stundennachweis Nw
where year( Nw.Tag )=2016 and month( Nw.Tag )=1
group by Nw.PnId,Nw.Tag
order by Nw.PnId,Nw.Tag

set Tag1      = cast( '2016-01-01' as SQL_DATE );
set Tag2      = Tag1;
set HPnId     = 0;
set HTag      = Tag1-1;
open Cursor1 as select * from #TmpNw order by PnId,Tag;
while fetch Cursor1 do
  if Cursor1.PnId<>HPnId then 
    // Cursor steht auf ersten Datensatz der nächsten Person.
    if HPnId<>0 then
      // restliche Datensätze der vorherigen Person bis Monatsende anlegen
      while month( Tag2 )=month( Tag1 ) do
        insert into #TmpNw values( HPnId,Tag2,0 );
        set Tag2 = Tag2+1;
      end while;
    end if;
    // Zur nächsten Person wechseln
    set HPnId     = Cursor1.PnId;
    set HTag      = Cursor1.Tag;
    set Tag2      = Tag1; // erster Tag, der gegebenenfalls hinzugefügt werden muß
  end if;
  set HTag = Cursor1.Tag;
  // alle Datensätze bis zum vorhandenen Tag hinzufügen
  while Tag2 < HTag do
    insert into #TmpNw values( HPnId,Tag2,0 );
    set Tag2 = Tag2+1;
  end while;
  set Tag2 = HTag+1; 
end while;
close Cursor1;
// Bei der letzten Person noch die Tage bis zum Monatsende hinzufügen
while month( Tag2 )=month( Tag1 ) do
  insert into #TmpNw values( HPnId,Tag2,0 );
  set Tag2 = Tag2+1;
end while;

select *
from   #TmpNw
order by PnId,Tag
Dabei kommt das gewünschte Ergebnis raus:

Code: Alles auswählen

PnId      Tag        TagStunden
1234      01.01.2016  0,00
1234      02.01.2016  0,00
1234      03.01.2016  0,00
1234      04.01.2016  8,50
1234      05.01.2016  8,00
...
1234      28.01.2016  2,00
1234      29.01.2016  0,00
1234      30.01.2016  0,00
1234      31.01.2016  0,00
Vielleicht kann der eine oder andere ja damit etwas anfangen :) .
-------
Mitglied XuG Cologne
Mitglied XuG Osnabrück
Benutzeravatar
Marcus Herz
1000 working lines a day
1000 working lines a day
Beiträge: 851
Registriert: Mo, 16. Jan 2006 8:13
Wohnort: Allgäu
Hat sich bedankt: 39 Mal
Danksagung erhalten: 192 Mal
Kontaktdaten:

Re: SQL-Statement mit allen Tagen eines Monats

Beitrag von Marcus Herz »

Hallo Uli
Es ist immer schwierig, in SQL was abzufragen was es nicht gibt. In deinem Fall eine Tabelle mit allen Tagen.
Schneller könnte es sein, einmalig eine Tabelle Kalender mit allenn Tagen anzulegen und gegen diese die Abfrage laufen zu lassen:

Code: Alles auswählen

execute procedure spAddKalendertag(2016);
select Nw.PnId,kalenderTag,Sum(Nw.Stunden) TagesStunden
from kalender 
 	left join Stundennachweis Nw  on nw.tag = kalender.tag
where year( kalender.Tag )=2016 
group by 1,2
order by  1,2
Bei ADS ist GROUP BY auch ident mit ORDER BY und kann man weglassen
Wenn du noch eine Procedure anlegst, welche die Tabelle Kalender füllt, kannst du die vorher aufrufen:
in etwas so

Code: Alles auswählen

CREATE PROCEDURE spAddKalender
   ( 
      jahr integer
   ) 
BEGIN 
declare @Beginn date;
@Beginn = cast(trim(cast(_jahr as sql_char) +'-01-01' as sql_date);
while year(beginn) = _jahr do
	merge kalender on tag = @beginn when not matched then insert (tag) values(@beginn);
    @beginn = @beginn + 1;
end while;
end;
das ist jedenfalls rasend schnell
Gruß Marcus

Erkenne, was du findest, dann weißt du, wonach du gesucht hast
UliTs
Der Entwickler von "Deep Thought"
Der Entwickler von "Deep Thought"
Beiträge: 2828
Registriert: Fr, 10. Feb 2006 9:51
Wohnort: Aachen
Hat sich bedankt: 259 Mal
Danksagung erhalten: 12 Mal
Kontaktdaten:

Re: SQL-Statement mit allen Tagen eines Monats

Beitrag von UliTs »

Hallo Marcus,

vielen Dank für Deinen Beitrag. Allerdings funktioniert er meines Erachtens nicht. So fehlt bei den Tagen, die es in der Stundennachweis-Tabelle nicht gibt, die PnId und ist mit NULL belegt.

Uli
-------
Mitglied XuG Cologne
Mitglied XuG Osnabrück
Benutzeravatar
Marcus Herz
1000 working lines a day
1000 working lines a day
Beiträge: 851
Registriert: Mo, 16. Jan 2006 8:13
Wohnort: Allgäu
Hat sich bedankt: 39 Mal
Danksagung erhalten: 192 Mal
Kontaktdaten:

Re: SQL-Statement mit allen Tagen eines Monats

Beitrag von Marcus Herz »

Ja du hast recht, hab keine Daten zum Testen
Vielleicht so:

Code: Alles auswählen

select pn.PnId,kalenderTag,Sum(Nw.Stunden) TagesStunden
from kalender 
 	left join Stundennachweis Nw  on nw.tag = kalender.tag,
(select distinct pnid from stundennachweis) as PN
where year( kalender.Tag )=2016 
group by 1,2
order by  1,2
Gruß Marcus

Erkenne, was du findest, dann weißt du, wonach du gesucht hast
Benutzeravatar
Marcus Herz
1000 working lines a day
1000 working lines a day
Beiträge: 851
Registriert: Mo, 16. Jan 2006 8:13
Wohnort: Allgäu
Hat sich bedankt: 39 Mal
Danksagung erhalten: 192 Mal
Kontaktdaten:

Re: SQL-Statement mit allen Tagen eines Monats

Beitrag von Marcus Herz »

ne stimmt auch nicht. Brauch Daten zum Testen. Aber es geht in die Richtung
Zuletzt geändert von Marcus Herz am Do, 01. Okt 2020 14:25, insgesamt 1-mal geändert.
Gruß Marcus

Erkenne, was du findest, dann weißt du, wonach du gesucht hast
UliTs
Der Entwickler von "Deep Thought"
Der Entwickler von "Deep Thought"
Beiträge: 2828
Registriert: Fr, 10. Feb 2006 9:51
Wohnort: Aachen
Hat sich bedankt: 259 Mal
Danksagung erhalten: 12 Mal
Kontaktdaten:

Re: SQL-Statement mit allen Tagen eines Monats

Beitrag von UliTs »

Marcus Herz hat geschrieben: Do, 01. Okt 2020 14:23 ...Brauch Daten zum Testen...
Nimm doch die aus meinem ersten Beitrag :D .

Uli
-------
Mitglied XuG Cologne
Mitglied XuG Osnabrück
Benutzeravatar
Marcus Herz
1000 working lines a day
1000 working lines a day
Beiträge: 851
Registriert: Mo, 16. Jan 2006 8:13
Wohnort: Allgäu
Hat sich bedankt: 39 Mal
Danksagung erhalten: 192 Mal
Kontaktdaten:

Re: SQL-Statement mit allen Tagen eines Monats

Beitrag von Marcus Herz »

Jetzt hab ich so gelöst:

Code: Alles auswählen

select pn.PnId,kalenderTag,Sum(Nw.Stunden) TagesStunden
from kalender 
	left join (select distinct pnid from Stundennachweis) pn on true
 	left join Stundennachweis Nw  on nw.tag = kalender.tag and nw.pnid = pn.pnid
where year( kalender.Tag )=2016 
group by 1,2
order by  1,2

Gruß Marcus

Erkenne, was du findest, dann weißt du, wonach du gesucht hast
UliTs
Der Entwickler von "Deep Thought"
Der Entwickler von "Deep Thought"
Beiträge: 2828
Registriert: Fr, 10. Feb 2006 9:51
Wohnort: Aachen
Hat sich bedankt: 259 Mal
Danksagung erhalten: 12 Mal
Kontaktdaten:

Re: SQL-Statement mit allen Tagen eines Monats

Beitrag von UliTs »

Danke. Sieht auf den ersten Blick gut aus!
Uli

Edit: kleine Erweiterung, damit Personen, die überhaupt keine Stunden hatten, nicht aufgeführt werden:

Code: Alles auswählen

select pn.PnId,kalenderTag,Sum(Nw.Stunden) TagesStunden
from kalender 
	left join (select distinct pnid from Stundennachweis) pn on true
	left join Stundennachweis Nw  on nw.tag = kalender.tag and nw.pnid = pn.pnid
where year( kalender.Tag )=2016 and
	pn.pnid in ( select distinct pnid from Stundennachweis Nw where year(nw.Tag)=2016) )
group by 1,2
order by  1,2
-------
Mitglied XuG Cologne
Mitglied XuG Osnabrück
Benutzeravatar
Marcus Herz
1000 working lines a day
1000 working lines a day
Beiträge: 851
Registriert: Mo, 16. Jan 2006 8:13
Wohnort: Allgäu
Hat sich bedankt: 39 Mal
Danksagung erhalten: 192 Mal
Kontaktdaten:

Re: SQL-Statement mit allen Tagen eines Monats

Beitrag von Marcus Herz »

einfacher

Code: Alles auswählen

select pn.PnId,kalenderTag,Sum(Nw.Stunden) TagesStunden
from kalender 
	left join (select distinct pnid from Stundennachweis where year(nw.Tag)=2016) pn on true
	left join Stundennachweis Nw  on nw.tag = kalender.tag and nw.pnid = pn.pnid
where year( kalender.Tag )=2016
group by 1,2
order by  1,2
Gruß Marcus

Erkenne, was du findest, dann weißt du, wonach du gesucht hast
UliTs
Der Entwickler von "Deep Thought"
Der Entwickler von "Deep Thought"
Beiträge: 2828
Registriert: Fr, 10. Feb 2006 9:51
Wohnort: Aachen
Hat sich bedankt: 259 Mal
Danksagung erhalten: 12 Mal
Kontaktdaten:

Re: SQL-Statement mit allen Tagen eines Monats

Beitrag von UliTs »

Ich vermute, dass das nicht funktioniert.
Edit: ich glaube doch :D
-------
Mitglied XuG Cologne
Mitglied XuG Osnabrück
Benutzeravatar
Marcus Herz
1000 working lines a day
1000 working lines a day
Beiträge: 851
Registriert: Mo, 16. Jan 2006 8:13
Wohnort: Allgäu
Hat sich bedankt: 39 Mal
Danksagung erhalten: 192 Mal
Kontaktdaten:

Re: SQL-Statement mit allen Tagen eines Monats

Beitrag von Marcus Herz »

Tipfehler / Kopierfehler
Das nw. im DSISTINCT select muss weg

Code: Alles auswählen

select pn.PnId,kalenderTag,Sum(Nw.Stunden) TagesStunden
from kalender 
	left join (select distinct pnid from Stundennachweis where year(Tag)=2016) pn on true
	left join Stundennachweis Nw  on nw.tag = kalender.tag and nw.pnid = pn.pnid
where year( kalender.Tag )=2016
group by 1,2
order by  1,2
Gruß Marcus

Erkenne, was du findest, dann weißt du, wonach du gesucht hast
Benutzeravatar
nightcrawler
1000 working lines a day
1000 working lines a day
Beiträge: 650
Registriert: Di, 24. Apr 2012 16:33
Wohnort: 72184 Weitingen
Hat sich bedankt: 3 Mal
Danksagung erhalten: 96 Mal
Kontaktdaten:

Re: SQL-Statement mit allen Tagen eines Monats

Beitrag von nightcrawler »

Statt einer Temporären oder fixen Tabelle mit den Datumswerten kann man auch eine Stored Procedure verwenden:

Code: Alles auswählen

CREATE PROCEDURE AlleTage(monat integer, jahr integer, tag date output)
BEGIN
  //Timestamp ist in ADS einfacher als Date
  DECLARE @ts TIMESTAMP;
  @ts=CREATETIMESTAMP(_jahr, _monat, 1, 0, 0, 0, 0);
  WHILE MONTH(@ts)=_monat DO
    INSERT INTO __output(tag) VALUES(cast(@ts as SQL_DATE));
	@ts=TIMESTAMPADD( SQL_TSI_DAY, 1, @ts);
  END;
END;
Verwendung als Tabelle:

Code: Alles auswählen

EXECUTE PROCEDURE AlleTage(10,2020);
oder dann in einem Join:

Code: Alles auswählen

CREATE TABLE Stundennachweis(id autoinc, tag date, anzahl integer);
insert into stundennachweis(tag, anzahl) values
  ('2020-10-01', 3),
  ('2020-10-02', 2),
  ('2020-10-02', 5),
  ('2020-10-05', 8),
  ('2020-10-06', 5),
  ('2020-10-07', 2);

SELECT t.tag, sum(s.anzahl) FROM 
  (EXECUTE PROCEDURE AlleTage(10,2020)) t
  LEFT OUTER JOIN Stundennachweis s ON s.tag=t.tag
group by 1
Edit: Optimierung
--
Joachim
Joachim Dürr Softwareengineering
https://www.jd-engineering.de
UliTs
Der Entwickler von "Deep Thought"
Der Entwickler von "Deep Thought"
Beiträge: 2828
Registriert: Fr, 10. Feb 2006 9:51
Wohnort: Aachen
Hat sich bedankt: 259 Mal
Danksagung erhalten: 12 Mal
Kontaktdaten:

Re: SQL-Statement mit allen Tagen eines Monats

Beitrag von UliTs »

Danke. Coole Lösung. 8)
-------
Mitglied XuG Cologne
Mitglied XuG Osnabrück
Antworten