Dauer in Vereinsmitgliedschaft berechnen

[voumi]

Und hier noch der finale Code für den Fall, dass jemand ein ähnliches Problem hat. Die beiden Fehlerhinweise sind zwar immer noch da. Aber er funktioniert: BestenDank für die freundliche Unterstützung von castorp und ukulele.

WITH  t1 AS (
    SELECT    m1.mem_usr_id, m1.mem_rol_id,
            YEAR(m1.mem_begin) AS mem_year,
            1 AS mem_active
    FROM    adm_members m1
    WHERE m1.mem_rol_id IN (74,75) AND EXISTS (SELECT m2.mem_usr_id from adm_members m2 where m2.mem_rol_id = 74 AND m1.mem_usr_id = m2.mem_usr_id AND YEAR(mem_end) >= YEAR(NOW()))
    UNION ALL
    SELECT    m1.mem_usr_id, m1.mem_rol_id,
            (CASE WHEN YEAR(m1.mem_end) = '9999' THEN year(NOW()) ELSE YEAR(m1.mem_end) END) AS mem_year,
            -1 AS mem_active
    FROM    adm_members m1
    WHERE m1.mem_rol_id IN (74,75) AND EXISTS (SELECT m2.mem_usr_id from adm_members m2 where m2.mem_rol_id = 74 AND m1.mem_usr_id = m2.mem_usr_id AND YEAR(m2.mem_end) >= YEAR(NOW()))
    ), 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
    ), 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
), t5 AS (
    SELECT mem_usr_id, sum(mem_year - lag_mem_year + 1) - 1 as mem_sum
    from t4
    GROUP BY mem_usr_id
    )
   
SELECT mem_usr_id, u.vorname, u.nachname, mem_sum, CONCAT(u.nachname, ' ' , u.vorname, ', ') AS sort,
CASE
WHEN 30 - mem_sum + Year(Now()) >= Year(Now()) THEN CONCAT(30 - mem_sum + Year(Now()), ' Kantonaler Veteran')
WHEN 35 - mem_sum + Year(Now()) >= Year(Now()) THEN CONCAT(35 - mem_sum + Year(Now()), ' Eidgenössischer Veteran')
WHEN 50 - mem_sum + Year(Now()) >= Year(Now()) THEN CONCAT(50 - mem_sum + Year(Now()), ' Kantonaler Ehren-Veteran')
WHEN 60 - mem_sum + Year(Now()) >= Year(Now()) THEN CONCAT(60 - mem_sum + Year(Now()), ' CISM Veteran')
WHEN 70 - mem_sum + Year(Now()) >= Year(Now()) THEN CONCAT(70 - mem_sum + Year(Now()), ' Eidgenössischer Ehren-Veteran')
END AS jubi
   
from t5
  join user_info u on usd_usr_id = mem_usr_id
 
HAVING (LEFT(jubi,4) <= Year(Now())+5)
ORDER BY jubi ASC, sort ASC;

 

[ukulele]

Also ja, die Tabelle adm_members erstelle ich mit WITH als erstes, die brauchst du natürlich nicht. Jeder andere der damit testen möchte aber natürlich schon, du kannst auch damit testen um sowas nachzuvollziehen.

Warum er bei dir scheinbar keine Rekursion machen möchte, weiß ich nicht genau. In dem Bespiel bezieht sich der Select innerhalb von t auf t selbst. Kann sein, das MySQL das anders macht oder so nicht kann, welche Version von MySQL ist das denn? Eventuell weiß auch jemand zu dieser zweiten Warnung was zu sagen, ich arbeite halt nicht mit MySQL

Die zweite Variante ohne Rekursion gefällt mir allerdings auch besser, auch wenn es ein paar Aliase sind bis man am Ziel ist. Das lässt sich sehr gut debuggen wenn man die Ausgabe nach jedem Schritt sortiert betrachtet. lag() oder leas() nutzen ja eine Sortierung, die ganz wesentlich ist.

Hier noch zum Verständnis:

t1 erzeugt (1 = Eintritt, -1 = Austritt):

100 1995 1
100 2025 -1
110 1995 1
110 2000 1
110 2000 -1
110 2025 -1
120 1995 1
120 2000 1
120 2005 -1
120 2025 -1
130 1980 1
130 1982 -1
130 1983 1
130 1985 1
130 2000 -1
130 2008 1
130 2009 -1
130 2010 -1
140 1975 1
140 1984 -1
140 1990 1
140 1994 -1
140 2000 1
140 2004 -1
140 2015 1
140 2025 -1

Für das Verständnis muss an dieser Stelle klar sein das es mehrere Eintritte hintereinander geben kann.

t2 erzeugt:

100 1995 1
100 2025 0
110 1995 1
110 2000 1
110 2000 1
110 2025 0
120 1995 1
120 2000 2
120 2005 1
120 2025 0
130 1980 1
130 1982 0
130 1983 1
130 1985 2
130 2000 1
130 2008 2
130 2009 1
130 2010 0
140 1975 1
140 1984 0
140 1990 1
140 1994 0
140 2000 1
140 2004 0
140 2015 1
140 2025 0

t3 erzeugt (usr_id,year,active,lag_active):

100 1995 1 NULL
100 2025 0 1
110 1995 1 NULL
110 2000 1 1
110 2000 1 1
110 2025 0 1
120 1995 1 NULL
120 2000 2 1
120 2005 1 2
120 2025 0 1
130 1980 1 NULL
130 1982 0 1
130 1983 1 0
130 1985 2 1
130 2000 1 2
130 2008 2 1
130 2009 1 2
130 2010 0 1
140 1975 1 NULL
140 1984 0 1
140 1990 1 0
140 1994 0 1
140 2000 1 0
140 2004 0 1
140 2015 1 0
140 2025 0 1

t4 erzeugt(usr_id,year,active,lag_active,lag_year):

100 1995 1 NULL NULL
100 2025 0 1 1995
110 1995 1 NULL NULL
110 2025 0 1 1995
120 1995 1 NULL NULL
120 2025 0 1 1995
130 1980 1 NULL NULL
130 1982 0 1 1980
130 1983 1 0 NULL
130 2010 0 1 1983
140 1975 1 NULL NULL
140 1984 0 1 1975
140 1990 1 0 NULL
140 1994 0 1 1990
140 2000 1 0 NULL
140 2004 0 1 2000
140 2015 1 0 NULL
140 2025 0 1 2015

letzter Schritt(usr_id,sum):

100 31
110 31
120 31
130 31
140 31

 

[ukulele]

Eventuell mag MySQL nicht, das ich den selben Alias öfter verwende - das wird wohl den Hinweis auslösen. Ich setze ja mem_active und mache dann sum(mem_active) wieder zu mem_active usw.