Seite 1 von 1
SQL-Statement mit allen Tagen eines Monats
Verfasst: Mi, 30. Sep 2020 17:42
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?
Re: SQL-Statement mit allen Tagen eines Monats
Verfasst: Mi, 30. Sep 2020 23:06
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
![Very Happy :D](./images/smilies/biggrin.gif)
.
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
![Smile :)](./images/smilies/smile.gif)
.
Re: SQL-Statement mit allen Tagen eines Monats
Verfasst: Do, 01. Okt 2020 14:05
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
Re: SQL-Statement mit allen Tagen eines Monats
Verfasst: Do, 01. Okt 2020 14:11
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
Re: SQL-Statement mit allen Tagen eines Monats
Verfasst: Do, 01. Okt 2020 14:21
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
Re: SQL-Statement mit allen Tagen eines Monats
Verfasst: Do, 01. Okt 2020 14:23
von Marcus Herz
ne stimmt auch nicht. Brauch Daten zum Testen. Aber es geht in die Richtung
Re: SQL-Statement mit allen Tagen eines Monats
Verfasst: Do, 01. Okt 2020 14:25
von UliTs
Marcus Herz hat geschrieben: ↑Do, 01. Okt 2020 14:23
...Brauch Daten zum Testen...
Nimm doch die aus meinem ersten Beitrag
![Very Happy :D](./images/smilies/biggrin.gif)
.
Uli
Re: SQL-Statement mit allen Tagen eines Monats
Verfasst: Do, 01. Okt 2020 15:21
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
Re: SQL-Statement mit allen Tagen eines Monats
Verfasst: Do, 01. Okt 2020 15:27
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
Re: SQL-Statement mit allen Tagen eines Monats
Verfasst: Do, 01. Okt 2020 15:56
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
Re: SQL-Statement mit allen Tagen eines Monats
Verfasst: Do, 01. Okt 2020 17:16
von UliTs
Ich vermute, dass das nicht funktioniert.
Edit: ich glaube doch
![Very Happy :D](./images/smilies/biggrin.gif)
Re: SQL-Statement mit allen Tagen eines Monats
Verfasst: Fr, 02. Okt 2020 12:05
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
Re: SQL-Statement mit allen Tagen eines Monats
Verfasst: Fr, 02. Okt 2020 13:42
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:
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
Re: SQL-Statement mit allen Tagen eines Monats
Verfasst: Mo, 05. Okt 2020 15:09
von UliTs
Danke. Coole Lösung.
![Cool 8)](./images/smilies/cool.gif)