GROUP BY : chaque ancien adhérent apparait une fois par année antérieure cotiséeGROUP BY : chaque ancien adhérent n'apparait qu'une foisSELECT users.nom, users.email, users.id, s.label AS 'Activité' FROM users INNER JOIN services_users su ON su.id_user = users.id INNER JOIN services s ON s.id = su.id_service WHERE ( s.label = "Cotisation " || strftime('%Y', DATE('now', '-1 year')) OR s.label = "Cotisation " || strftime('%Y', DATE('now', '-2 year')) OR s.label = "Cotisation " || strftime('%Y', DATE('now', '-3 year')) ) AND users.id NOT IN ( SELECT users.id FROM users INNER JOIN services_users su ON su.id_user = users.id INNER JOIN services s ON s.id = su.id_service WHERE s.label = "Cotisation " || strftime('%Y', DATE()) ) GROUP BY users.id ORDER BY users.nom LIMIT 1000 ;
SELECT users.nom, users.email, users.id AS _user_id, s.label AS 'Activité', f.label AS 'Tarif', su.paid AS 'Payé', su.date AS 'Date' FROM users INNER JOIN services_users su ON su.id_user = users.id INNER JOIN services s ON s.id = su.id_service INNER JOIN services_fees f ON f.id = su.id_fee WHERE s.id != 1 AND su.date >= s.start_date AND su.id_user NOT IN ( SELECT id_user FROM services_users sus WHERE sus.id_service = 1 AND sus.date >= s.start_date ) ORDER BY users.nom ASC;
SELECT su.date AS 'Date d''inscription', su.expiry_date AS 'Date d''expiration', SUM(tl.debit)/100 AS 'Montant réglé', expected_amount - SUM(tl.debit)/100 AS 'Reste à régler', users.*, users.id AS _user_id FROM users INNER JOIN ( SELECT MAX(expiry_date) AS expiry_date, DATE, id_user, id, expected_amount FROM services_users -- Modifier ici l'ID de l'activité WHERE id_service = 1 AND paid = 1 AND (expiry_date >= DATE() OR expiry_date IS NULL) GROUP BY id_user) AS su ON su.id_user = users.id LEFT JOIN acc_transactions_users tu ON tu.id_service_user = su.id LEFT JOIN acc_transactions_lines tl ON tl.id_transaction = tu.id_transaction GROUP BY users.id