75 lines
2.5 KiB
SQL
75 lines
2.5 KiB
SQL
drop view sust.members_by_section;
|
|
drop view sust.members_active;
|
|
|
|
CREATE VIEW sust.members_active
|
|
AS SELECT id,
|
|
member_surname,
|
|
member_firstname,
|
|
member_firstname_spouse,
|
|
member_count,
|
|
member_active,
|
|
current_state_code,
|
|
member_email
|
|
FROM members m
|
|
WHERE TRIM(BOTH FROM current_state_code) = 'A'::text AND member_count > 0 AND member_primary = true
|
|
ORDER BY member_surname;
|
|
|
|
CREATE OR REPLACE VIEW sust.members_by_section
|
|
AS SELECT m.id,
|
|
m.member_surname,
|
|
m.member_firstname,
|
|
m.member_firstname_spouse,
|
|
m.member_active,
|
|
m.member_email,
|
|
s.section_name
|
|
FROM members_active m
|
|
JOIN member_sections ms ON ms.member_id = m.id
|
|
JOIN sections s ON s.id = ms.section_id;
|
|
|
|
SELECT sum(member_count) FROM members_active
|
|
|
|
drop view sust.members_paid_years;
|
|
CREATE OR REPLACE VIEW sust.members_paid_years
|
|
AS SELECT id,
|
|
member_surname,
|
|
member_firstname,
|
|
member_suffix,
|
|
member_firstname_spouse,
|
|
CONCAT_WS(' ', member_street, member_streetnumber, member_city) as member_address,
|
|
member_active,
|
|
current_state_code,
|
|
member_email,
|
|
member_membership_start,
|
|
CASE
|
|
WHEN member_membership_start > '2023-12-31'::date THEN 'N'::text
|
|
WHEN (EXISTS ( SELECT 1
|
|
FROM payments p
|
|
WHERE p.member_id = m.id AND EXTRACT(year FROM p.payment_year) = 2023::numeric)) THEN 'X'::text
|
|
ELSE NULL::text
|
|
END AS paid_2023,
|
|
CASE
|
|
WHEN member_membership_start > '2024-12-31'::date THEN 'N'::text
|
|
WHEN (EXISTS ( SELECT 1
|
|
FROM payments p
|
|
WHERE p.member_id = m.id AND EXTRACT(year FROM p.payment_year) = 2024::numeric)) THEN 'X'::text
|
|
ELSE NULL::text
|
|
END AS paid_2024,
|
|
CASE
|
|
WHEN member_membership_start > '2025-12-31'::date THEN 'N'::text
|
|
WHEN (EXISTS ( SELECT 1
|
|
FROM payments p
|
|
WHERE p.member_id = m.id AND EXTRACT(year FROM p.payment_year) = 2025::numeric)) THEN 'X'::text
|
|
ELSE NULL::text
|
|
END AS paid_2025,
|
|
CASE
|
|
WHEN member_membership_start > '2026-12-31'::date THEN 'N'::text
|
|
WHEN (EXISTS ( SELECT 1
|
|
FROM payments p
|
|
WHERE p.member_id = m.id AND EXTRACT(year FROM p.payment_year) = 2026::numeric)) THEN 'X'::text
|
|
ELSE NULL::text
|
|
END AS paid_2026
|
|
FROM members m
|
|
WHERE TRIM(BOTH FROM current_state_code) = 'A'::text AND member_count > 0 AND member_primary = true
|
|
ORDER BY member_surname;
|
|
|