10
Access SQL - Anzahl der Personen je Monat aus Zeitraum berechnen
Schönen guten Morgen in die Runde.
ich hatte es vor längerem schon in die Abteilung Office eingestellt, aber bis Dato keine einzige Rückmeldung erhalten.
Ich stehe gerade vor einem für mich nicht lösbaren Problem und bin schon Schneeblind.
Die Überschrift gibt wahrscheinlich nicht ganz her, was ich möchte..
Ich habe circa 400 Datensätze die ich aus einer Abfrage bekomme. Diese sehen wie folgt aus:
Es gibt einen Beginn, ein Ende und ein vorzeitiges Austrittsdatum.
Nun möchte ich eine Abfrage erstellen, die es mir erlaubt jeden dieser Datensätze in die belegten Monate aufzusplitten,
um anschließend zu zählen, wie viele Personen im den Monat da waren.
Also je Person/Rechnung soll für jeden Monat der Anwesenheit ein Datensatz erstellt werden.
Wenn jemand früher ausgeschieden ist, soll zur Berechnung das Austrittdatum genommen werden.
Mein aktueller Ansatz geht so:
Ich habe mir 2 Hilfstabellen für das Jahr und den Monat erstellt. Gefüllt von 1 bis 12 und 2010 bis 2030.. als Beispiel.
Nun erstelle ich folgende Abfrage:
In einer zweiten Abfrage zähle ich nun die Anzahl über eine Gruppierung auf Jahre und Monate.
Das funktioniert..
Allerdings ist das Ergebnis der "ersten Abfrage" nicht wie erhofft.
Ich bekomme die Aufteilung in die monatlichen Datensätze, jedoch werden Rechnungen auch noch nach dem Enddatum in den Folgejahren angezeigt.
Ich vermute ich mache einen Denkfehler bei den Monaten..
Ich weiß gerade nicht weiter. WO IST MEIN DENKFEHLER???
Wäre super, wenn mir jemand weiterhelfen könnte..
Gruß
ich hatte es vor längerem schon in die Abteilung Office eingestellt, aber bis Dato keine einzige Rückmeldung erhalten.
Ich stehe gerade vor einem für mich nicht lösbaren Problem und bin schon Schneeblind.
Die Überschrift gibt wahrscheinlich nicht ganz her, was ich möchte..
Ich habe circa 400 Datensätze die ich aus einer Abfrage bekomme. Diese sehen wie folgt aus:
kd_nr rg_nr beginn ende austritt massnahmenummer kursname zusatz
40621 10146 05.02.2018 09.03.2018 3542017 ITZT M1
40618 10150 04.12.2017 26.01.2018 3542017 ITZT M1
40618 10151 29.01.2018 02.03.2018 3542017 ITZT M1
40619 10162 06.08.2018 21.09.2018 3542017 ITZT M1
40708 10312 02.09.2019 13.08.2021 3452019 FISI
40682 10314 02.09.2019 13.08.2021 3452019 FISI M3
40770 10317 02.09.2019 13.08.2021 24.04.2020 3452019 Irgendwas M4Es gibt einen Beginn, ein Ende und ein vorzeitiges Austrittsdatum.
Nun möchte ich eine Abfrage erstellen, die es mir erlaubt jeden dieser Datensätze in die belegten Monate aufzusplitten,
um anschließend zu zählen, wie viele Personen im den Monat da waren.
Also je Person/Rechnung soll für jeden Monat der Anwesenheit ein Datensatz erstellt werden.
Wenn jemand früher ausgeschieden ist, soll zur Berechnung das Austrittdatum genommen werden.
Mein aktueller Ansatz geht so:
Ich habe mir 2 Hilfstabellen für das Jahr und den Monat erstellt. Gefüllt von 1 bis 12 und 2010 bis 2030.. als Beispiel.
Nun erstelle ich folgende Abfrage:
SELECT sys_hobj_jahre.jahr,
sys_hobj_monate.monat,
qry_rechnungen_zu_kursen.beginn,
qry_rechnungen_zu_kursen.ende,
qry_rechnungen_zu_kursen.austritt,
sys_kunden.nachname,
qry_rechnungen_zu_kursen.kd_nr,
qry_rechnungen_zu_kursen.massnahmenummer,
qry_rechnungen_zu_kursen.kursname,
qry_rechnungen_zu_kursen.zusatz,
Datediff("m", [qry_rechnungen_zu_kursen].[beginn],
Iif(Isnull([qry_rechnungen_zu_kursen].[austritt]),
[qry_rechnungen_zu_kursen].[ende], [qry_rechnungen_zu_kursen].[austritt])) AS
monate
FROM sys_hobj_jahre,
sys_hobj_monate,
qry_rechnungen_zu_kursen
INNER JOIN sys_kunden
ON qry_rechnungen_zu_kursen.kd_nr = sys_kunden.si_kundennummer
WHERE (( ( sys_hobj_jahre.jahr ) > Year([qry_rechnungen_zu_kursen].[beginn])
AND ( sys_hobj_jahre.jahr ) < Year(
Iif(Isnull(
[qry_rechnungen_zu_kursen].[austritt]),
[qry_rechnungen_zu_kursen].[austritt], (
[qry_rechnungen_zu_kursen].[ende] ))) ))
OR ( ( ( sys_hobj_jahre.jahr ) = Year(
[qry_rechnungen_zu_kursen].[beginn]) )
AND
( ( sys_hobj_monate.monat ) >= Month([qry_rechnungen_zu_kursen].[beginn]) ) )
OR ( ( ( sys_hobj_jahre.jahr ) = Year(
Iif(Isnull(
[qry_rechnungen_zu_kursen].[austritt]),
[qry_rechnungen_zu_kursen].[austritt], (
[qry_rechnungen_zu_kursen].[ende] ))) )
AND ( ( sys_hobj_monate.monat ) <= Month(
Iif(Isnull(
[qry_rechnungen_zu_kursen].[austritt]),
[qry_rechnungen_zu_kursen].[austritt], (
[qry_rechnungen_zu_kursen].[ende] ))) ) )
ORDER BY sys_hobj_jahre.jahr,
sys_hobj_monate.monat,
qry_rechnungen_zu_kursen.kd_nr; In einer zweiten Abfrage zähle ich nun die Anzahl über eine Gruppierung auf Jahre und Monate.
Das funktioniert..
Allerdings ist das Ergebnis der "ersten Abfrage" nicht wie erhofft.
Ich bekomme die Aufteilung in die monatlichen Datensätze, jedoch werden Rechnungen auch noch nach dem Enddatum in den Folgejahren angezeigt.
Ich vermute ich mache einen Denkfehler bei den Monaten..
Ich weiß gerade nicht weiter. WO IST MEIN DENKFEHLER???
Wäre super, wenn mir jemand weiterhelfen könnte..
Gruß
Auf Facebook teilen
Auf Twitter teilen
Auf Reddit teilen
Auf Linkedin teilenBitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben
Content-ID: 1918169101
Url: https://administrator.de/contentid/1918169101
Ausgedruckt am: 26.11.2024 um 14:11 Uhr
10 Kommentare
Neuester Kommentar
Was Du grundlegend suchst ist ein LATERAL JOIN dieser Tabelle mit einer 'on the fly' für jeden einzelnen Record erstellten Tabelle.
Hier eine Demo mit PostgreSQL:
Die anderen Konditionen drum herum bekommt man dann leicht dazu, das Problem ist, daß nicht alle Datenbanken so coole Features wie lateral join, generate_series() etc. haben. Möglicherweise wirst Du das also so auf diesem eleganten Weg nicht hinbekommen ...
Nachtrag: man sollte evtl. noch ein date_trunc() einbauen:
Hier eine Demo mit PostgreSQL:
app=# select * from zappo74;
id | begin | ende
----+------------+------------
1 | 2018-02-05 | 2018-03-09
2 | 2017-12-04 | 2018-01-26
3 | 2022-01-01 | 2022-11-03
(3 rows)
app=# select * from zappo74 left join lateral (select * from generate_series(zappo74.begin, zappo74.ende, '1month'::interval)) x on true;
id | begin | ende | generate_series
----+------------+------------+------------------------
1 | 2018-02-05 | 2018-03-09 | 2018-02-05 00:00:00+00
1 | 2018-02-05 | 2018-03-09 | 2018-03-05 00:00:00+00
2 | 2017-12-04 | 2018-01-26 | 2017-12-04 00:00:00+00
2 | 2017-12-04 | 2018-01-26 | 2018-01-04 00:00:00+00
3 | 2022-01-01 | 2022-11-03 | 2022-01-01 00:00:00+00
3 | 2022-01-01 | 2022-11-03 | 2022-02-01 00:00:00+00
3 | 2022-01-01 | 2022-11-03 | 2022-03-01 00:00:00+00
3 | 2022-01-01 | 2022-11-03 | 2022-04-01 00:00:00+00
3 | 2022-01-01 | 2022-11-03 | 2022-05-01 00:00:00+00
3 | 2022-01-01 | 2022-11-03 | 2022-06-01 00:00:00+00
3 | 2022-01-01 | 2022-11-03 | 2022-07-01 00:00:00+00
3 | 2022-01-01 | 2022-11-03 | 2022-08-01 00:00:00+00
3 | 2022-01-01 | 2022-11-03 | 2022-09-01 00:00:00+00
3 | 2022-01-01 | 2022-11-03 | 2022-10-01 00:00:00+00
3 | 2022-01-01 | 2022-11-03 | 2022-11-01 00:00:00+00
(15 rows)Die anderen Konditionen drum herum bekommt man dann leicht dazu, das Problem ist, daß nicht alle Datenbanken so coole Features wie lateral join, generate_series() etc. haben. Möglicherweise wirst Du das also so auf diesem eleganten Weg nicht hinbekommen ...
Nachtrag: man sollte evtl. noch ein date_trunc() einbauen:
select * from zappo74 left join lateral (select * from generate_series(date_trunc('month',zappo74.begin), zappo74.ende, '1month'::interval)) x on true; 
Ja, leider muss ich Access benutzen 
Also Access schreckt mich hier auch ein wenig ab, vermutlich kann das viele Sachen nicht. Du hast ja aber schon Hilfstabellen angelegt, so das das eigentlich mit Boardmitteln möglich sein sollte.
Der Code ist im WHERE-Teil sehr unleserlich. Du solltest unbedingt qry_rechnungen_zu_kursen explizit joinen, nicht einfach in den FROM-Teil packen.
Mit einer Hilfstabelle ließe sich auch deutlich einfacher rechnen statt mit einer für Jahre und einer für Monate. Ich glaube beim Join der Monate klappt das nicht richtig.
Ich hab das mal umgestellt (MSSQL, könnte auch so in Access gehen) um eine Hilfstabelle als Ausgang zu haben und darauf explizit zu joinen:
Der Code ist im WHERE-Teil sehr unleserlich. Du solltest unbedingt qry_rechnungen_zu_kursen explizit joinen, nicht einfach in den FROM-Teil packen.
Mit einer Hilfstabelle ließe sich auch deutlich einfacher rechnen statt mit einer für Jahre und einer für Monate. Ich glaube beim Join der Monate klappt das nicht richtig.
Ich hab das mal umgestellt (MSSQL, könnte auch so in Access gehen) um eine Hilfstabelle als Ausgang zu haben und darauf explizit zu joinen:
SELECT *
FROM ( SELECT sys_hobj_jahre.jahr,
sys_hobj_monate.monat,
convert(DATE,convert(VARCHAR(8),sys_hobj_jahre.jahr) + convert(VARCHAR(8),sys_hobj_monate.monat) + '01',112) AS von,
dateadd(day,-1,dateadd(month,1,
convert(DATE,convert(VARCHAR(8),sys_hobj_jahre.jahr) + convert(VARCHAR(8),sys_hobj_monate.monat) + '01',112)
)) AS bis
FROM sys_hobj_jahre,
sys_hobj_monate ) sys_hobj
INNER JOIN qry_rechnungen_zu_kursen
ON [qry_rechnungen_zu_kursen].[beginn] BETWEEN sys_hobj.von AND sys_hobj.bis
AND isnull([qry_rechnungen_zu_kursen].[austritt],[qry_rechnungen_zu_kursen].[ende]) BETWEEN sys_hobj.von AND sys_hobj.bis
INNER JOIN sys_kunden
ON qry_rechnungen_zu_kursen.kd_nr = sys_kunden.si_kundennummer
ORDER BY sys_hobj.von
qry_rechnungen_zu_kursen.kd_nr;
Hallo Zappo,
zwar bin ich auch nicht der Access-König, aber wenn ich das richtig sehe, dann verwendest Du das Austrittsdatum genau dann, wenn es leer ist:
Das ist falsch, Du mußt es verwenden, wenn es nicht leer ist:
Der Rest Deiner Bedingungen sollte so passen.
Gruß, Mad Max
zwar bin ich auch nicht der Access-König, aber wenn ich das richtig sehe, dann verwendest Du das Austrittsdatum genau dann, wenn es leer ist:
Iif ( Isnull([qry_rechnungen_zu_kursen].[austritt]),
[qry_rechnungen_zu_kursen].[austritt],
[qry_rechnungen_zu_kursen].[ende]
)Das ist falsch, Du mußt es verwenden, wenn es nicht leer ist:
Iif ( Isnull([qry_rechnungen_zu_kursen].[austritt]),
[qry_rechnungen_zu_kursen].[ende],
[qry_rechnungen_zu_kursen].[austritt]
)Gruß, Mad Max
@MadMax
Ja, da ist mit auch schon aufgefallen, aber selbst nach der Korrektur funktioniert es nicht.
Komisch ist auch, das Datensätze für Monate erzeugt werden, obwohl ja eigentlich schon ende oder ausgeschieden.
Ich check es nicht
@ukulele-7
Bei deiner Variante bekomme ich immer eine Fehlermeldung.
Between Operator ohne And in Abfrageausdruck [qry_rechnungen_zu_kursen].[ende]) BETWEEN sys_hobj.vo
Ich vermute, die Hilfstabelle wird nicht angelegt..
Glaube in Access war das irgendwas mit SELECT INTO oder so.. Ich hasse Access
Ja, da ist mit auch schon aufgefallen, aber selbst nach der Korrektur funktioniert es nicht.
Komisch ist auch, das Datensätze für Monate erzeugt werden, obwohl ja eigentlich schon ende oder ausgeschieden.
Ich check es nicht
@ukulele-7
Bei deiner Variante bekomme ich immer eine Fehlermeldung.
Between Operator ohne And in Abfrageausdruck [qry_rechnungen_zu_kursen].[ende]) BETWEEN sys_hobj.vo
Ich vermute, die Hilfstabelle wird nicht angelegt..
Glaube in Access war das irgendwas mit SELECT INTO oder so.. Ich hasse Access
Die Zeile scheint dann nicht vollständig:
Aber falls Access kein BETWEEN kann lässt sich das ja leicht umstellen
[qry_rechnungen_zu_kursen].[beginn] BETWEEN sys_hobj.von AND sys_hobj.bis[qry_rechnungen_zu_kursen].[beginn] >= sys_hobj.von AND [qry_rechnungen_zu_kursen].[beginn] <= sys_hobj.bis
Ich hab das jetzt mal so umgebaut, dass Access es meiner Ansicht nach können sollte;
convert() zu strvcon()
VARCAHR() zu CStr()
Allerdings erhalte ich jetzt einen JOIN Ausdruck wird nicht unterstützt Fehler.
Ich verzweifle hier bal..
convert() zu strvcon()
VARCAHR() zu CStr()
Allerdings erhalte ich jetzt einen JOIN Ausdruck wird nicht unterstützt Fehler.
Ich verzweifle hier bal..
SELECT *
FROM
(
SELECT sys_hobj_jahre.jahr,
sys_hobj_monate.monat,
strconv(DATE,strconv(CStr(8),sys_hobj_jahre.jahr) + strconv(CStr(8),sys_hobj_monate.monat) + '01',112) AS von,
dateadd(day,-1,dateadd(month,1,
strconv(DATE,strconv(CStr(8),sys_hobj_jahre.jahr) + strconv(CStr(8),sys_hobj_monate.monat) + '01',112) )) AS bis
FROM sys_hobj_jahre,
sys_hobj_monate
)
sys_hobj
INNER JOIN qry_rechnungen_zu_kursen
ON [qry_rechnungen_zu_kursen].[beginn] >= sys_hobj.von AND [qry_rechnungen_zu_kursen].[beginn] <= sys_hobj.bis
AND Isnull([qry_rechnungen_zu_kursen].[austritt]), [qry_rechnungen_zu_kursen].[ende], [qry_rechnungen_zu_kursen].[austritt] >= sys_hobj.von AND <= sys_hobj.bis
INNER JOIN sys_kunden
ON [qry_rechnungen_zu_kursen].[kd_nr = sys_kunden].[ti_kundennummer]
ORDER BY sys_hobj.von
[qry_rechnungen_zu_kursen].[kd_nr];
Ich hab leider kein Access zum testen zur Hand. Es kann sein das Access mit dem verschachtelten Select ein Problem hat. Oder es fehlt einfach nur ein banales AS.
Teste mal bitte:
Teste mal bitte:
SELECT * FROM ( SELECT * FROM sys_hobj_jahre ) sys_hobjSELECT * FROM ( SELECT * FROM sys_hobj_jahre ) AS sys_hobj
Hallo Zappo,
da habe ich mich in die Irre leiten lassen, das ist nicht so sonderlich übersichtlich. Die benötigten Bedingungen sind zwar alle da, aber falsch angeordnet.
Ich beziehe mich jetzt auf Deinen Befehl von ganz oben, da müßte die Bedingung so aufgebaut sein (unnötige Klammern hab ich mal weggelassen, die stören nur):
Übrigens müßtest Du das
Iif(Isnull([qry_rechnungen_zu_kursen].[austritt]),[qry_rechnungen_zu_kursen].[ende], [qry_rechnungen_zu_kursen].[austritt])
auch kürzer schreiben können als
Nz([qry_rechnungen_zu_kursen].[austritt],[qry_rechnungen_zu_kursen].[ende])
Und falls Dich noch der Fehler von Deinem letzten Beitrag interessiert:
Die dritte Zeile von Deinem ersten INNER JOIN ist nicht korrekt, da fehlt die Hälfte. Vor dem IsNull sollte wohl wieder das Iif stehen, die Klammer sollte dann auch wieder geschlossen werden nach den drei Parametern und zwischen AND und <= fehlt noch irgendein Wert, mit dem verglichen werden soll.
Gruß, Mad Max
da habe ich mich in die Irre leiten lassen, das ist nicht so sonderlich übersichtlich. Die benötigten Bedingungen sind zwar alle da, aber falsch angeordnet.
Ich beziehe mich jetzt auf Deinen Befehl von ganz oben, da müßte die Bedingung so aufgebaut sein (unnötige Klammern hab ich mal weggelassen, die stören nur):
where ((sys_hobj_jahre.jahr = Year([qry_rechnungen_zu_kursen].[beginn])
and sys_hobj_monate.monat >= Month([qry_rechnungen_zu_kursen].[beginn]))
or sys_hobj_jahre.jahr > Year ([qry_rechnungen_zu_kursen].[beginn])) and
((sys_hobj_jahre.jahr = Year(Iif(Isnull([qry_rechnungen_zu_kursen].[austritt]),[qry_rechnungen_zu_kursen].[ende], [qry_rechnungen_zu_kursen].[austritt]))
and sys_hobj_monate.monat <= Month(Iif(Isnull([qry_rechnungen_zu_kursen].[austritt]),[qry_rechnungen_zu_kursen].[austritt], [qry_rechnungen_zu_kursen].[ende])))
or sys_hobj_jahre.jahr < Year(Iif(Isnull([qry_rechnungen_zu_kursen].[austritt]),[qry_rechnungen_zu_kursen].[ende], [qry_rechnungen_zu_kursen].[austritt])))Übrigens müßtest Du das
Iif(Isnull([qry_rechnungen_zu_kursen].[austritt]),[qry_rechnungen_zu_kursen].[ende], [qry_rechnungen_zu_kursen].[austritt])
auch kürzer schreiben können als
Nz([qry_rechnungen_zu_kursen].[austritt],[qry_rechnungen_zu_kursen].[ende])
Und falls Dich noch der Fehler von Deinem letzten Beitrag interessiert:
Die dritte Zeile von Deinem ersten INNER JOIN ist nicht korrekt, da fehlt die Hälfte. Vor dem IsNull sollte wohl wieder das Iif stehen, die Klammer sollte dann auch wieder geschlossen werden nach den drei Parametern und zwischen AND und <= fehlt noch irgendein Wert, mit dem verglichen werden soll.
Gruß, Mad Max
@MadMax
Du bist mein Held!!
Genau das war es. Jetzt funktioniert es. Hatte sich zwar nochmal der gleiche Fehler eingeschlichen
Iif ( Isnull([qry_rechnungen_zu_kursen].[austritt]),
[qry_rechnungen_zu_kursen].[ende],
[qry_rechnungen_zu_kursen].[austritt]
)<
/code>
hab es diesmal aber selbst direkt gemerkt ;)
Das mit der Reihenfolge hab ich wohl irgendwie selbst zu verantworten. In der Entwurfsansicht in Access kann man ja wie in den Tabellen die Reihenfolge der Felder ändern. Bei Tabellen hat das ja keine Auswirkung. In der Entwurfsansicht wird wohl aber auch der SQL Code beeinflusst und angeordnet. Ist mir erst richtig bei der Sortierung aufgefallen. Wollte der Lesbarkeit halber schieben und der händische SQL Code wurde dann angepasst.
Auf jeden Fall ein DICKES DANKE!!
Du bist mein Held!!
Genau das war es. Jetzt funktioniert es. Hatte sich zwar nochmal der gleiche Fehler eingeschlichen
Iif ( Isnull([qry_rechnungen_zu_kursen].[austritt]),
[qry_rechnungen_zu_kursen].[ende],
[qry_rechnungen_zu_kursen].[austritt]
)<
/code>
hab es diesmal aber selbst direkt gemerkt ;)
Das mit der Reihenfolge hab ich wohl irgendwie selbst zu verantworten. In der Entwurfsansicht in Access kann man ja wie in den Tabellen die Reihenfolge der Felder ändern. Bei Tabellen hat das ja keine Auswirkung. In der Entwurfsansicht wird wohl aber auch der SQL Code beeinflusst und angeordnet. Ist mir erst richtig bei der Sortierung aufgefallen. Wollte der Lesbarkeit halber schieben und der händische SQL Code wurde dann angepasst.
Auf jeden Fall ein DICKES DANKE!!
