Dauer in Vereinsmitgliedschaft berechnen

voumi · 25 Dezember 2024

Mein aktuelles Anliegen ist (für mich) etwas zu komplex. Die Suchfunktion habe ich zwar benutzt. Da ich aber nicht genau weiss, wonach ich suchen soll, habe ich möglicherweise ähnliche Problemstellungen übersehen und bitte schon jetzt um Entschuldigung...

Unser Verein bewirtschaftet die Mitglieder mit Admidio. (Die Datenbank ist also gegeben und kann vom mir nicht beeinflusst werden.) In der Datenbank gibt es verschiedene Tabellen. In der Tabelle adm_members werden in den Spalten mem_begin und mem_end die Start und Enddaten der Rollen (mem_rol_id) der Mitglieder (mem_usr_id) gespeichert. Damit ich nun die Jubiläen richtig berechnen kann, muss ich alle Einträge (eine bis mehrere Zeilen) von zwei Rollen (2 und 73) filtern. Die Knacknuss dabei ist, dass ein (1) Enddatum der Rolle 2 '9999-12-31' enthalten muss. (Dies bedeutet, dass die Person aktuell noch Mitglied des Vereins ist.) Es kann aber auch sein, dass die Person früher bereits Mitglied war und nach einem Unterbruch wieder im Verein mitmacht. Diese früheren Jahre sollen für das Jubiläum auch beigezogen werden. In einer weiteren Tabelle (adm_ user_data) sind dann die Namen und Vornamen in der Spalte usd_value abgelegt. In der Spalte usd_usf_if steht der "Code" für den Wert (1 für Nachname und 2 für Vorname).
Ich habe bereits ein paar Codes ausprobiert. Es werden mir aber stets alle Personen angezeigt, welche jemals im Verein waren....
Mit diesem Code werden die nächsten Jubiläen angezeigt. Leider auch von Ehemaligen.

SQL:

SELECT
usr_id, mem_usr_id, CONCAT(id1.usd_value, ' ',id2.usd_value) AS sort, SUM(If (mem_end = "9999-12-31",YEAR(NOW())-YEAR(mem_begin)+1,YEAR(mem_end) - YEAR(mem_begin)+1)) AS Mitgliedsjahre,

CONCAT
(
IF(30 - SUM(If (mem_end = "9999-12-31",YEAR(NOW())-YEAR(mem_begin)+1,YEAR(mem_end) - YEAR(mem_begin)+1)) + YEAR(NOW())>= YEAR(NOW()),CONCAT(30 - SUM(If (mem_end = "9999-12-31",YEAR(NOW())-YEAR(mem_begin)+1,YEAR(mem_end) - YEAR(mem_begin)+1)) + YEAR(NOW()) , " Kantonaler Veteran, "),""),

IF(35 - SUM(If (mem_end = "9999-12-31",YEAR(NOW())-YEAR(mem_begin)+1,YEAR(mem_end) - YEAR(mem_begin)+1)) + YEAR(NOW())>= YEAR(NOW()),CONCAT(35 - SUM(If (mem_end = "9999-12-31",YEAR(NOW())-YEAR(mem_begin)+1,YEAR(mem_end) - YEAR(mem_begin)+1)) + YEAR(NOW()) , " Eidgenössischer Veteran, "),""),

IF(50 - SUM(If (mem_end = "9999-12-31",YEAR(NOW())-YEAR(mem_begin)+1,YEAR(mem_end) - YEAR(mem_begin)+1)) + YEAR(NOW())>= YEAR(NOW()),CONCAT(50 - SUM(If (mem_end = "9999-12-31",YEAR(NOW())-YEAR(mem_begin)+1,YEAR(mem_end) - YEAR(mem_begin)+1)) + YEAR(NOW()) , " Kantonaler Ehrenveteran, "),""),

IF(60 - SUM(If (mem_end = "9999-12-31",YEAR(NOW())-YEAR(mem_begin)+1,YEAR(mem_end) - YEAR(mem_begin)+1)) + YEAR(NOW())>= YEAR(NOW()),CONCAT(60 - SUM(If (mem_end = "9999-12-31",YEAR(NOW())-YEAR(mem_begin)+1,YEAR(mem_end) - YEAR(mem_begin)+1)) + YEAR(NOW()) , " CISM, "),""),

IF(70 - SUM(If (mem_end = "9999-12-31",YEAR(NOW())-YEAR(mem_begin)+1,YEAR(mem_end) - YEAR(mem_begin)+1)) + YEAR(NOW())>= YEAR(NOW()),CONCAT(70 - SUM(If (mem_end = "9999-12-31",YEAR(NOW())-YEAR(mem_begin)+1,YEAR(mem_end) - YEAR(mem_begin)+1)) + YEAR(NOW()) , " Eidgenössischer Ehrenveteran"),"")
) AS JUBI
FROM
adm_members, adm_users

LEFT JOIN adm_user_data id1 ON id1.usd_usr_id = usr_id
AND id1.usd_usf_id = 1

LEFT JOIN adm_user_data id2 ON id2.usd_usr_id = usr_id
AND id2.usd_usf_id = 2

WHERE
(mem_rol_id = 2 OR mem_rol_id = 73) and mem_usr_id = usr_id

GROUP BY usr_id

EXCEPT
(SELECT usr_id, 

CONCAT(id1.usd_value, ' ', id2.usd_value) AS name, CONCAT(id4.usd_value, ' ', id5.usd_value) AS ort, YEAR(mem_begin), YEAR(mem_end)

FROM adm_roles, adm_categories, adm_members, adm_users

LEFT JOIN adm_user_data id1 ON id1.usd_usr_id = usr_id
AND id1.usd_usf_id = 1

LEFT JOIN adm_user_data id2 ON id2.usd_usr_id = usr_id
AND id2.usd_usf_id = 2

LEFT JOIN adm_user_data id4 ON id4.usd_usr_id = usr_id
AND id4.usd_usf_id = 4

LEFT JOIN adm_user_data id5 ON id5.usd_usr_id = usr_id
AND id5.usd_usf_id = 5

WHERE rol_id = 2 AND mem_begin <= NOW() AND mem_end > NOW()

AND rol_valid = 1
AND rol_cat_id = cat_id
AND cat_org_id = 1
AND mem_rol_id = rol_id

AND mem_usr_id = usr_id
AND usr_valid = 1)

ORDER BY JUBI ASC;

Wenn ich den unteren Teil (nach EXCEPT) alleine "starte", werden nur die Aktivmitglieder angezeigt.

Hier noch die Tabellen:
adm_members

mem_id	mem_usr_id	mem_rol_id	mem_begin	mem_end
1	940	2	1970	1975
2	940	2	1980	1991
3	940	2	2001	9999
4	940	73	1960	1965
5	920	73	1975	1990
6	920	2	1991	9999
7	910	2	1990	2000
8	900	2	1990	2000
9	900	2	2020	9999
10	800	2	1990	1991

adm_usr_data

usd_id	usd_usr_id	usd_usr_id	usd_value
1	940	1	Nachname 1
2	940	2	Vorname 1
3	920	1	Nachname 2
4	920	2	Vorname 2
5	910	1	Nachname 3
6	910	2	Vorname 3
7	900	1	Nachname 4
8	900	2	Vorname 4
9	800	1	Nachname 5
10	800	2	Vorname 5

Gewünschtes Resultat:

mem_usr_id	Vorname	Nachname	Jahre	JUBI
940	Vorname 1	Nachname 1	48	2026 Vet...
920	Vorname 2	Nachname 2	50	2024 Vet...
900	Vorname 4	Nachname 4	16	2038 Vet...

Kann mir jemand von Euch einen Tipp geben, was ich übersehen habe? Besten Dank und liebe Grüsse aus der verschneiten Schweiz.
voumi

dabadepdu · 26 Dezember 2024

Ich hab den Code nur überflogen. Hier ist mir nicht klar, warum Du mehrfach joinst.
Die Where Clause dürfte vermutlich durch die Filterung auch den Left Join zerstören.

Aber für den Anfang 2 Fragen / Überlegungen.
Willst Du Jubiläen errechnen, die mangels Mitgliedschaft sozusagen ungültig sind?
Wenn nicht, sind nur die Datensätze relevant, die das Ende in der Zukunft haben.
Zur Berechnung der Differenz, könntest Du (nach dem Filtern, siehe vorige Frage) das Zukunftsdatum durch das aktuelle ersetzen. Zusätzlich könntest Du weitere Spalten produzieren, die nicht mit dem aktuellen Jahr arbeiten, sondern mit einer Projektion (nächste 3 Monate, Jahresende, o.ä., damit nicht erst zum Jahreswechsel klar ist, wer Jubiläum hat / haben wird...

castorp · 26 Dezember 2024

Ich würde das mit einer EXISTS Bedingung lösen. Um es übersichtlicher zu machen, würde ich die Berechnung der Mitgliedschaft, den Join für die Namen und die Berechnung des Jubiläum in mehreren Schritten machen. Etwas in der Art:

Code:

with dauer as (
  select m1.mem_usr_id, 
         sum((case when year(m1.mem_end) = 9999 then year(now()) else year(m1.mem_end) end - m1.mem_begin) + 1) as jahre
  from adm_members m1
  where mem_rol_id in (2, 73)
    and exists (select *
                from adm_members m2
                where m2.mem_usr_id = m1.mem_usr_id
                  and year(m2.mem_end) = 9999)
  group by mem_usr_id
  order by mem_usr_id
), user_info as (
  select v.usd_usr_id,
         v.usd_value as vorname, 
         n.usd_value as nachname
  from adm_usr_data v
    join adm_usr_data n on v.usd_usr_id = n.usd_usr_id and n.usd_usf_id = 2
  where v.usd_usf_id = 1
)    
select d.mem_usr_id, u.vorname, u.nachname, d.jahre
from dauer d
  join user_info u on u.usd_usr_id = d.mem_usr_id;

Im abschliessenden SELECT, kannst Du dann die Berechnung des nächsten Jubiläums einbauen.

voumi · 26 Dezember 2024

Vielen Dank für Eure Antworten.

@dabadepdu:
Wenn das mit der Mitgliedschaft nur so einfach wäre ;-)
Die Jubiläen werden vom Verband gemacht. Dabei ist es egal, in welcher Untersektion man Mitglied ist oder war. Deshalb habe ich zwei Rollen gemacht. Eine für uns und eine für andere (Mir ist es ja egal, in welcher anderen Untersektion die Person Mitglied war. Mich interessieren nur die Jahre, welche dort geleistet wurden.) Es ist auch egal, wie oft die Mitgliedschaft unterbrochen worden ist - meist der Elternzeit geschuldet. Dabei zählen angebrochene Jahre. Eine Mitgliedschaft vom 1.1.2000 bis am 31.12.2001 ist gleichbedeutend wie eine Mitgliedschaft vom 31.12.2000 bis am 1.1.2001. Für die Berechnung der Jubiläen zählt beides als 2 Jahre.
Hoffentlich verstehe ich Deine Frage mit zum zweimaligen JOIN richtig: Ich hatte die Hoffnung, dass ich mit der ersten Select-Anfrage sämtliche Jahre aller Mitglieder pro Mitglied auflisten kann. In der zweiten Abfrage sind nur noch die aktuellen Mitglieder aufgelistet. Zudem hoffte ich, dass ich damit das Resultat ausdünnen kann. Eigentlich sollten im Resultat nur noch die aktuellen Aktivmitglieder mit den Jahren aufgelistet sein. (Alle IDs, welche in beiden SELECT-Abfragen vorkommen). Es werden aber immer alle 291 angezeigt. Es wird also gar nichts ausgefiltert. (Falls ich EXCEPT richtig verstehe, hätten im Resultat wenigstens alle aktuell nicht aktiven Mitglieder angezeigt werden sollen...)

@castorp:
Dein Lösungsvorschlag ergibt ebenfalls 291 Einträge. 44 wären das angestrebte Ziel ;-) (Mit den Originaldaten...) Leider verstehe ich Deinen Code nicht zu 100%. Aber ab Zeile 6 stehen eigentlich genau die Bedingungen, welche ich im Endresultat haben möchte. Warum dann die anderen, welche im mem_end ein Datum kleiner Now() bzw. ungleich 9999 haben, trotzdem noch angezeigt werden, kann ich nicht nachvollziehen.

ukulele · 27 Dezember 2024

@castorp erzeugt eine Tabelle "dauer" in der für jede Mitgliedschaft eines aktuell aktiven Mitglieds die Dauer ermittelt wird, aggregiert auf die mem_usr_id. Mitglieder, die aktuell nicht aktiv sind, werden über EXISTS ausgeschlossen. Dann joint er noch die Userdaten wie Vor- und Nachname dazu.

Wegen der zu vielen Zeilen: Hast du eventuell in dem ersten Select mehr Spalten als "mem_usr_id" und "jahre" angegeben? MySQL gruppiert dann ggf. nicht richtig. Ansonsten sieht mir der Code eigentlich richtig aus.

castorp · 27 Dezember 2024

voumi schrieb:
Dein Lösungsvorschlag ergibt ebenfalls 291 Einträge. 44 wären das angestrebte Ziel ;-) (Mit den Originaldaten...)

Bei solchen Problemen hilft es, die Abfrage Schritt für Schritt aufzubauen und bei jedem Schritt das Ergebnis zu prüfen. Je "einfacher" die Abfrage ist, desto einfacher wird es die Fehler zu finden. Fang also erstmal mit der Bedingung an "alle Mitglieder die aktiv sind" (das ist im Grunde meine EXISTS Bedingung) und schau Dir die Datensätze an, die nicht im Ergebnis sein dürfen und überlege, warum sie trotzdem mit drin sind. Aber ohne die Originaldaten kann man das aus der Ferne aber nicht analysieren.

voumi · 28 Dezember 2024

Vielen Dank für für Eure Hilfe! Mein Code sieht nun so aus....

SQL:

with dauer as (
  select m1.mem_usr_id, 
        CONCAT( sum((case when year(m1.mem_end) >= year(Now()) then year(now()) else year(m1.mem_end) end - year(m1.mem_begin))) + count((case when year(m1.mem_end) >= year(Now()) then year(now()) else year(m1.mem_end) end - year(m1.mem_begin)))-1) AS jahre
  from adm_members m1
  where mem_rol_id in (2, 648)
    and exists (select *
                from adm_members m2
                where m2.mem_usr_id = m1.mem_usr_id and mem_rol_id = 2
                  and year(m2.mem_end) > Year(Now())) 
  group by mem_usr_id
  order by mem_usr_id
), user_info as (
  select v.usd_usr_id,
         v.usd_value as vorname, 
         n.usd_value as nachname
  from adm_user_data v
    join adm_user_data n on v.usd_usr_id = n.usd_usr_id and n.usd_usf_id = 1
  where v.usd_usf_id = 2
)    
select d.mem_usr_id, u.vorname, u.nachname, d.jahre, CONCAT(u.nachname, ' ' , u.vorname, ', ') AS sort,
CONCAT(
If(30 - d.jahre + Year(Now()) >= Year(Now()), CONCAT(30 - d.jahre + Year(Now()), ' Kantonaler Veteran, '),''),
If(35 - d.jahre + Year(Now()) >= Year(Now()), CONCAT(35 - d.jahre + Year(Now()), ' Eidgenössischer Veteran, '),''),
If(50 - d.jahre + Year(Now()) >= Year(Now()), CONCAT(50 - d.jahre + Year(Now()), ' Kantonaler Ehrenveteran, '),''),
If(60 - d.jahre + Year(Now()) >= Year(Now()), CONCAT(60 - d.jahre + Year(Now()), ' CISM Veteran, '),''),
If(70 - d.jahre + Year(Now()) >= Year(Now()), CONCAT(70 - d.jahre + Year(Now()), ' Eidgenössischer Ehrenveteran, '),'')
)AS jubi

from dauer d
  join user_info u on u.usd_usr_id = d.mem_usr_id
ORDER BY jubi ASC, sort ASC;

....und macht genau das, was er soll

Nachdem ich auch noch die " durch ' ersetzt habe, funktioniert der Code jetzt auch mit der php-bridge auf unserer Joomla-Site.
Ohne Euch wäre ich aufgeschmissen gewesen. Vielen herzlichen Dank und liebe Grüsse aus der Schweiz.

voumi · 30 Dezember 2024

Ich habe da noch eine Folgefrage und weiss nicht, ob ich dafür ein neues Thema eröffnen soll...

Mit dem Essen kommt ja bekanntlich der Appetit.

Ist es möglich den folgenden Code von castorp so anzupassen, dass doppelt belegte Jahre nicht berücksichtigt werden. (Falls es dafür keine Lösung gibt, ist es kein grosses Unglück, da einfach bei der Eingabe jeweils daran gedacht werden muss, dass sich die Rollen nicht überschneiden.)
Der Hintergrund: Man kann ja im Laufe der Jahre in verschiedenen Vereinen tätig sein. Entweder hintereinander oder auch gleichzeitig bzw. überschneidend. Wenn man zum Beispiel während 10Jahren gleichzeitig in drei Vereinen Mitglied ist, ergibt dies rechnerisch 30Jahre. Für das 30jährige Dienstgeschenk ist es aber noch zu früh. Wären genau dieselben Mitgliedschaften aber nacheinander, bestünde Anrecht auf das Geschenk.
Die Abfrage funktioniert also solange, wie sich die End-Daten nicht mit den (zeitlich nächsten) Begin-Daten überschneiden (Oder auch umgekehrt, wenn die Person weg war und wieder zurück kommt.).

SQL:

select m1.mem_usr_id,
        CONCAT( sum((case when year(m1.mem_end) >= year(Now()) then year(now()) else year(m1.mem_end) end - year(m1.mem_begin))) + count((case when year(m1.mem_end) >= year(Now()) then year(now()) else year(m1.mem_end) end - year(m1.mem_begin)))-1) AS jahre
  from adm_members m1
  where mem_rol_id in (74, 75)

  group by mem_usr_id
  order by mem_usr_id;

Und die Tabelle dazu:

mem_id	mem_usr_id	mem_rol_id	mem_begin	mem_end
2	100	74	1995	9999
4	110	75	1995	2000
7	110	74	2000	9999
10	120	75	1995	2005
8	120	74	2000	9999
12	130	75	1980	1982
3	130	74	1983	2000
5	130	75	1985	2009
11	130	74	2008	2010
9	140	74	1975	1984
13	140	74	1990	1994
6	140	74	2000	2004
1	140	74	2015	9999

Und noch zur Veranschaulichung am Beispiel der Person mit der ID 130:

Falls ich mich nicht vertan habe, sollte das Resultat bei allen 30 sein.

Vielen Dank für die Tipps. :cool:

ukulele · 2 Januar 2025

Grundsätzlich ist das möglich. Allerdings macht das aus meiner Sicht ein komplett anderes Vorgehen erforderlich. Man könnte z.B. mit Hilfe von Window Functions die Zeiträume zunächst zerlegen (in ein Datensatz pro Jahr) und dann beim Gruppieren nur DISTINCT Jahr summieren. Wenn dein MySQL aktuell ist, eigentlich kein Problem.

Aber es drängt sich die Frage auf ob das wirklich der sinnvolle Weg ist. Jetzt ist das scheinbar kein Fehler in der Datenbasis sondern dem Umstand geschuldet, das hier verschiedene mem_rol_id kumuliert werden, um die Mitgliedschaftsdauer zu ermitteln. Gibt es nicht vielleicht eine mem_rol_id, die immer gilt, sobald die Person irgendeine Rolle hält? Dann könnte man einfach per WHERE filtern.

voumi · 2 Januar 2025

Das mit einer mem_rol_id, welche immer gilt, ist aus meiner Sicht (der versucht, eine mysql-Abfrage zu machen...) grundsätzlich eine gute Idee. Admidio stellt Rollen zur Verfügung, welche gelten, wenn eine andere Rolle aktiviert wird. Leider muss diese "gemeinsame" Rolle dann separat beendet werden. Diesbezüglich müsste ich wohl beim Entwickler-Team von Admidio vorstellig werden, dass die gemeinsame Rolle nur so lange aktiv ist, wie mindestens eine der ihr zugeordneten Rollen ebenfalls aktiv ist....
Vielleicht wäre es auch ein Plan, eine Abfrage zu machen, welche mir alle Mitglieder der gemeinsamen Rolle anzeigt, welche aber den anderen nicht - bzw. nicht mehr - angehören. Diese Abfrage müsste dann einmal im Jahr gemacht werden und dürfte nicht vergessen gehen...
Oder habe ich Deinen zweiten Absatz nicht richtig verstanden? Die Rolle 74 gilt grundsätzlich immer. Diese bildet die Mitgliedschaft im eigenen Verein ab. Für das Verbandsjubiläum zählen aber zusätzlich auch jene Jahre, welche (ausschliesslich) in einem anderen Verein geleistet wurden. Doppelte Mitgliedschaften zählen aber nicht doppelt. Zudem sind wir für das Verbandsjubiläum nur verantwortlich, falls die Person zum Jubiläumszeitpunkt bei uns Mitglied ist.

So oder so; Es bleibt eine Lösung, bei welcher irgend jemand an etwas denken muss und es nicht vergessen werden darf. Dies ist ja auch bei "meiner" aktuellen Lösung so: Solange daran gedacht wird, dass sich die Jahre der externen Vereinsmitgliedschaft nicht mit jenen der internen Vereinsmitgliedschaft überschneiden, stimmt die Berechnung.
Es ist mir klar, dass ich jetzt noch am i-Tüpfelchen herum mache. ;-) Aber danke für den Tipp mit der Windows-Funktion. Ich werde mich diesbezüglich einmal einlesen und herumpröbeln. Und falls ich es nicht schaffe, kann ich immer noch die Anwender fragen, ob sie lieber einmal pro Jahr eine Abfrage machen wollen oder bei der Dateneingabe ein Auge auf die korrekten Daten haben möchten.
Besten Dank für Deine Unterstützung.

voumi · 2 Januar 2025

@ukulele Dein Ansatz im ersten Abschnitt tönt für mich sehr plausibel. Offenbar habe ich Google aber bislang mit falschen Suchwörtern gefüttert. Bis jetzt habe ich leider noch nirgends einen Ansatz gefunden, mit welchem ich pröbeln könnte. Gerne würde ich hier meine nichtfunktionierenden Versuche zeigen. Vielleicht hast Du mir noch einen Tipp, welcher mich auf die richtige Fährte führen könnte. Diverse Dokumentationen zur Windows-Funktion habe ich bereits gelesen und grob (sehr grob) auch verstanden.
Allerdings würde es vielleicht ja auch reichen, wenn ich pro Datensatz beginnend mit dem Startjahr mit einer Schleife mem_rol_id, mem_usr_id und das extrahierte Jahr in einen neuen Datensatz schreiben würde, solange das extrahierte Jahr kleiner oder gleich Endjahr ist. Aber auch dafür fehlt mir der Syntax....
Aber morgen ist auch noch ein Tag. Ich bleibe dran.

ukulele · 3 Januar 2025

Also zunächst bezog sich meine Überlegung auf die vorhandenen Daten. Klar kann man der Anwendung eventuell beibringen oder beibringen lassen, spezielle Systemrollen zu führen. Ich wollte aber erstmal nur wissen, ob es sowas vielleicht schon gibt.

Ich nahm an, das es sich um verschiedene Rollen im selben Verein handelt (Schatzmeister, Vorsitzender, etc.) und habe auf eine Rolle "Mitglied" spekuliert, die nicht in sich überschneidenden Zeiträumen auftreten kann. Durch deine Erläuterung verstehe ich deine Daten jetzt ja überhaupt erst besser. Es gibt ggf. mehrere Vereinsmitgliedschaften parallel in verschiedenen Vereinen, die nicht gezählt werden sollen.

Schleifen oder Cursor sind grundsätzlich zu vermeiden. Window-Functions ermöglichen aber Rekursion und zwar sehr performant. Ich hab das mit MSSQL gemacht, das läuft aber auch auf aktuellen MySQL Versionen. Einzig die Funktionen datepart() und getdate() musst du ersetzen:

Code:

WITH t AS (
    SELECT    m1.mem_usr_id,
            m1.mem_begin,
            (CASE WHEN m1.mem_end > datepart(year,getdate()) THEN datepart(year,getdate()) ELSE m1.mem_end END) AS mem_end,
            m1.mem_begin AS mem_year
    FROM    adm_members m1
    WHERE    mem_rol_id IN (74,75)
    UNION ALL
    SELECT    t.mem_usr_id,
            t.mem_begin,
            t.mem_end,
            t.mem_year + 1
    FROM    t
    WHERE    t.mem_year + 1 <= t.mem_end
    )
SELECT    t.mem_usr_id,
        count(DISTINCT t.mem_year) AS jahre
FROM    t
GROUP BY t.mem_usr_id
ORDER BY t.mem_usr_id

Bei Rekursion gibt es vermutlich eine maximale Rekursionstiefe. Dann läuft es in einen Fehler, daher habe ich auch die Fälle mit 9999 als Endjahr schon vorher durch das aktuelle Jahr ersetzt.

ukulele · 3 Januar 2025

Ich habe noch ein wenig nachgedacht und bin zu dem Schluss gekommen das Rekursion vielleicht nicht die beste Wahl ist. Vor allem wenn man das eventuell Tagesgenau rechnen will und das mit Zeiträumen über Jahrzehnte hinweg. Nicht, das das nicht ginge, aber es entstehen unnötig lange Rekrusionen.

Ich habe mehrere Window-Functions hinter einander durchgeführt und komme damit auch auf ein Ergebnis:

Code:

WITH adm_members(mem_id,mem_usr_id,mem_rol_id,mem_begin,mem_end) AS (
    SELECT 2,100,74,1995,9999 UNION ALL
    SELECT 4,110,75,1995,2000 UNION ALL
    SELECT 7,110,74,2000,9999 UNION ALL
    SELECT 10,120,75,1995,2005 UNION ALL
    SELECT 8,120,74,2000,9999 UNION ALL
    SELECT 12,130,75,1980,1982 UNION ALL
    SELECT 3,130,74,1983,2000 UNION ALL
    SELECT 5,130,75,1985,2009 UNION ALL
    SELECT 11,130,74,2008,2010 UNION ALL
    SELECT 9,140,74,1975,1984 UNION ALL
    SELECT 13,140,74,1990,1994 UNION ALL
    SELECT 6,140,74,2000,2004 UNION ALL
    SELECT 1,140,74,2015,9999
    ), t1 AS (
    SELECT    mem_usr_id,
            mem_begin AS mem_year,
            1 AS mem_active
    FROM    adm_members
    UNION ALL
    SELECT    mem_usr_id,
            (CASE WHEN mem_end = 9999 THEN datepart(year,getdate()) ELSE mem_end END) AS mem_year,
            -1 AS mem_active
    FROM    adm_members
    ), t2 AS (
    SELECT    mem_usr_id,
            mem_year,
            sum(mem_active) OVER (PARTITION BY mem_usr_id ORDER BY mem_year) AS mem_active
    FROM    t1
    ), t3 AS (
    SELECT    *,
            lag(mem_active) OVER (PARTITION BY mem_usr_id ORDER BY mem_year) AS lag_mem_active
    FROM    t2
    ), t4 AS (
    SELECT    *,
            (CASE WHEN mem_active = 0 THEN lag(mem_year) OVER (PARTITION BY mem_usr_id ORDER BY mem_year) ELSE NULL END) AS lag_mem_year
    FROM    t3
    WHERE    mem_active = 1
    AND    (    lag_mem_active IS NULL
    OR        lag_mem_active = 0 )
    OR    (    mem_active IS NULL
    OR        mem_active = 0 )
    AND        lag_mem_active = 1
    )
SELECT    mem_usr_id,
        sum(mem_year - lag_mem_year + 1) AS mem_sum
FROM    t4
WHERE    mem_active = 0
GROUP BY mem_usr_id
ORDER BY mem_usr_id

Viel Spaß beim grübeln ;-)

voumi · 3 Januar 2025

@ukulele WOW!!!! Vielen herzlichen Dank für Deine aufwändige Arbeit.

Dein erster Vorschlag gibt bei mir den Fehler aus, dass die Tabelle t nicht existiere. Wenn ich die Zeilen von "UNION ALL" bis und mit "WHERE t.mem_year +1 <= t.mem_end" lösche, ist der Fehler weg und es werden die Anzahl der Datensätze pro mem_usr_id angezeigt.

Bei Deinem zweiten Vorschlag wird vor am Kopf der Zeile mit "sum(mem_active) OVER PARTITION BY mem_usr_id...." der folgende Hinweis angezeigt: "Ein Alias wurde zuvor gefunden. (bei mem_active)".
Am Kopf der Zeile "), t3 AS (" erscheint der Hinweis: "Unerwartetes Ende des WITH CTE (bei ))" Trotzdem funktioniert der Code. Ich habe nun versucht, den Code so anzupassen, dass er auf die Daten der Datenbank zugreift. Er sieht nun so aus:

SQL:

WITH  t1 AS (
    SELECT    mem_usr_id, mem_rol_id,
            YEAR(mem_begin) AS mem_year,
            1 AS mem_active
    FROM    adm_members
    UNION ALL
    SELECT    mem_usr_id, mem_rol_id,
            (CASE WHEN YEAR(mem_end) = '9999' THEN year(NOW()) ELSE YEAR(mem_end) END) AS mem_year,
            -1 AS mem_active
    FROM    adm_members
    ), t2 AS (
    SELECT    mem_usr_id, mem_rol_id,
            mem_year,
            sum(mem_active) OVER (PARTITION BY mem_usr_id ORDER BY mem_year) AS mem_active
    FROM    t1
    ), t3 AS (
    SELECT    *,
            lag(mem_active) OVER (PARTITION BY mem_usr_id ORDER BY mem_year) AS lag_mem_active
    FROM    t2
    ), t4 AS (
    SELECT    *,
            (CASE WHEN mem_active = 0 THEN lag(mem_year) OVER (PARTITION BY mem_usr_id ORDER BY mem_year) ELSE NULL END) AS lag_mem_year
    FROM    t3
    WHERE    mem_active = 1
    AND    (    lag_mem_active IS NULL
    OR        lag_mem_active = 0 )
    OR    (    mem_active IS NULL
    OR        mem_active = 0 )
    AND        lag_mem_active = 1
    )
SELECT    mem_usr_id, mem_rol_id, mem_active,
        sum(mem_year - lag_mem_year + 1) AS mem_sum
FROM    t4
WHERE    mem_active = 0 and mem_rol_id IN (74, 75)
GROUP BY mem_usr_id
ORDER BY mem_usr_id;

Seltsamerweise, zumindest für mein Verständnis, bekomme ich als Resultat lediglich zwei Zeilen, wovon eine noch falsch ist, bzw. eine falsche Dauer anzeigt. Vielleicht mache ich einen Überlegungsfehler. Aber ich habe angenommen, dass ich die erste Tabelle nicht erstellen muss, da ich ja die Daten von meiner Datenbank abfragen will. Dafür habe ich eine WHERE-Klausel mit dem Einschränkungskriterium der Rollen (74 & 75) ergänzt.

Auch habe ich versucht, mir die Zwischenresultate anzeigen zu lassen. Aussagekräftig ist dies - zumindest für mich - leider nicht...

Ich glaube herausgefunden zu habe, was beim falschen Resultat der mem_usr_id 140 bzw. 1455 noch fehlt; vermutlich die aktuellste Mitgliedschaft ab 2015/9999. Jedenfalls würde das die 11 fehlenden Jahre erklären.

Eben ist mir aufgefallen, dass im Resultat all jene mem_usr_ids fehlen, welche beim Enddatum 9999 eingetragen haben... Ob das mit den beiden Hinweisen auf Fehler einen Zusammenhang hat? Wobei - Dein ganzer Code zeigt ja das korrekte Resultat an, obwohl die Hinweise dort schon bestehen... Spielt die Reihenfolge der Datensätze in der Datenbank eine Rolle? Der User 140 wird ja angezeigt, obwohl er noch eine aktive Rolle (9999) hat. 110 wird jedoch nicht angezeigt. Dieser hätte auch eine Rolle <> 9999 und sollte eigentlich - wie der User 140 - mit einem falschen Resultat (6) auf der Liste sein.

Soweit mein Zwischenstand. Morgen geht's mit dem Grübeln weiter ;-)

voumi · 4 Januar 2025

Manchmal hilft 'darüber schlafen' wirklich ;-)
Ich hatte ja den Verdacht, dass es ein Problem mit dem Enddatum '9999' gibt. Nachdem ich bei einer mem_usr_id dafür sorgte, dass ebenfalls keine aktiven Rollen mehr vorhanden sind, war ich enttäuscht, dass diese ID dann nicht im Resultat der Abfrage auftauchte. Also lag ich mit meiner Vermutung falsch.
Auf die richtige Fährte wurde ich aber gebracht, als ich nachgesehen habe, was LAG genau macht. Da ging mir ein Licht auf. In meiner Abfrage gibt es ja sehr viel mehr Zeilen als im Übungsbeispiel. Nach dem Filtern der ersten beiden Tabellen stimmt nun auch das Resultat. Die beiden roten Punkte mit den Fehlerhinweisen sind zwar immer noch da, aber der Code funktioniert nun.
Aktuell sieht er so aus:

SQL:

WITH  t1 AS (
    SELECT    mem_usr_id, mem_rol_id,
            YEAR(mem_begin) AS mem_year,
            1 AS mem_active
    FROM    adm_members
    WHERE mem_rol_id IN (74,75)
    UNION ALL
    SELECT    mem_usr_id, mem_rol_id,
            (CASE WHEN YEAR(mem_end) = '9999' THEN year(NOW()) ELSE YEAR(mem_end) END) AS mem_year,
            -1 AS mem_active
    FROM    adm_members
    WHERE mem_rol_id IN (74,75)
    ), t2 AS (
    SELECT    mem_usr_id, mem_rol_id,
            mem_year,
            sum(mem_active) OVER (PARTITION BY mem_usr_id ORDER BY mem_year) AS mem_active
    FROM    t1
    ), t3 AS (
    SELECT    *,
            lag(mem_active) OVER (PARTITION BY mem_usr_id ORDER BY mem_year) AS lag_mem_active
    FROM    t2
    ), t4 AS (
    SELECT    *,
            (CASE WHEN mem_active = 0 THEN lag(mem_year) OVER (PARTITION BY mem_usr_id ORDER BY mem_year) ELSE NULL END) AS lag_mem_year
    FROM    t3
    WHERE    mem_active = 1
    AND    (    lag_mem_active IS NULL
    OR        lag_mem_active = 0 )
    OR    (    mem_active IS NULL
    OR        mem_active = 0 )
    AND        lag_mem_active = 1
    )
SELECT    mem_usr_id,
        sum(mem_year - lag_mem_year + 1) AS mem_sum
FROM    t4
GROUP BY mem_usr_id
ORDER BY mem_usr_id;

@ukulele Besten Dank für Deine Unterstützung.

Dauer in Vereinsmitgliedschaft berechnen

voumi

Benutzer

dabadepdu

Datenbank-Guru

castorp

Datenbank-Guru

voumi

Benutzer

ukulele

Datenbank-Guru

castorp

Datenbank-Guru

voumi

Benutzer

voumi

Benutzer

ukulele

Datenbank-Guru

voumi

Benutzer

voumi

Benutzer

ukulele

Datenbank-Guru

ukulele

Datenbank-Guru

voumi

Benutzer

voumi

Benutzer

Ähnliche Themen

Dauer in Vereinsmitgliedschaft berechnen

Benutzer

Datenbank-Guru

Datenbank-Guru

Benutzer

Datenbank-Guru

Datenbank-Guru

Benutzer

​

Benutzer

Datenbank-Guru

Benutzer

Benutzer

Datenbank-Guru

Datenbank-Guru

Benutzer

Benutzer

Ähnliche Themen