zappo74
Feb 15, 2022, updated at 08:17:04 (UTC)
view3016
view10
0
Goto Top

Access SQL - Anzahl der Personen je Monat aus Zeitraum berechnen

GermansolvedQuestionDatabasesDevelopment
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:

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       M4


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:


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ß
comment-contentCommentShareShare
Share on Facebook Share on Twitter Share on Reddit Share on Linkedin

Content-Key: 1918169101

Url: https://administrator.de/contentid/1918169101

Printed on: April 28, 2024 at 16:04 o'clock

10 Comments
Latest comment
Goto Top
Member: akretschmer
akretschmer Feb 15, 2022 at 08:49:51 (UTC)
Goto Top
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:

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;
Member: zappo74
zappo74 Feb 15, 2022 at 09:04:44 (UTC)
Goto Top
Ja, leider muss ich Access benutzen face-sad
Member: ukulele-7
ukulele-7 Feb 15, 2022 updated at 09:53:00 (UTC)
Goto Top
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:
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;
Member: MadMax
MadMax Feb 15, 2022 at 14:27:19 (UTC)
Goto Top
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:
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]
	)
Der Rest Deiner Bedingungen sollte so passen.

Gruß, Mad Max
Member: zappo74
zappo74 Feb 17, 2022 updated at 19:38:17 (UTC)
Goto Top
@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 face-sad


@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 face-sad
Member: ukulele-7
ukulele-7 Feb 17, 2022 at 21:37:00 (UTC)
Goto Top
Die Zeile scheint dann nicht vollständig:
[qry_rechnungen_zu_kursen].[beginn] BETWEEN sys_hobj.von AND sys_hobj.bis
Aber falls Access kein BETWEEN kann lässt sich das ja leicht umstellen
[qry_rechnungen_zu_kursen].[beginn] >= sys_hobj.von AND [qry_rechnungen_zu_kursen].[beginn] <= sys_hobj.bis
Member: zappo74
zappo74 Feb 18, 2022 at 09:54:13 (UTC)
Goto Top
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..

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];
Member: ukulele-7
ukulele-7 Feb 18, 2022 at 10:44:42 (UTC)
Goto Top
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:
SELECT * FROM ( SELECT * FROM sys_hobj_jahre ) sys_hobj
SELECT * FROM ( SELECT * FROM sys_hobj_jahre ) AS sys_hobj
Member: MadMax
Solution MadMax Feb 18, 2022 at 19:19:14 (UTC)
Goto Top
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):
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
Member: zappo74
zappo74 Feb 20, 2022 at 18:33:37 (UTC)
Goto Top
@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!!
GermansolvedQuestionDatabasesDevelopment
Hotly discussed
DaniEnd of availability for classic Teams clientDani - 1 CommentadmtechDeveloper diary: Release 6.1admtech