173 lines
5.9 KiB
SQL
173 lines
5.9 KiB
SQL
select * from members m where m.member_firstname like 'Josef'
|
|
select * from members m where m.member_active = true and m.member_count = 0;
|
|
|
|
update members
|
|
set member_email = null
|
|
where member_email = ''
|
|
|
|
select * from members order by members.updated_date desc
|
|
|
|
update members m set member_geo_latitude = null, member_geo_longitude = null
|
|
where id in (110,107,45,21,19,108,36,24,22,18,35,162,90,3,142,122,121,115,9,128,79,78,50,186,183,180,176,157,148,98);
|
|
|
|
select * from members m where m.member_geo_latitude is null;
|
|
|
|
select * from members m where id in (110,107,45,21,19,108,36,24,22,18,35,162,90,3,142,122,121,115,9,128,79,78,50,186,183,180,176,157,148,98);
|
|
|
|
update members m set member_geo_latitude = null, member_geo_longitude = null
|
|
|
|
ALTER TABLE sust.deliveries ADD CONSTRAINT deliveries_member_id_delivered_by_fkey FOREIGN KEY (delivered_by_id) REFERENCES members(id)
|
|
|
|
update deliveries set delivered_by_id = 186
|
|
|
|
update members set member_city = 'Sankt Thomas' where member_city = 'St. Thomas'
|
|
|
|
update members set member_city = trim(member_city)
|
|
|
|
insert into deliveries (delivery_date, member_id, delivered_by_id, delivery_year)
|
|
values (now(), 126, 186, '2026-01-01'),
|
|
(now(), 176, 186, '2026-01-01'),
|
|
(now(), 4, 186, '2026-01-01'),
|
|
(now(), 128, 186, '2026-01-01'),
|
|
(now(), 18, 186, '2026-01-01')
|
|
|
|
insert into deliveries (delivery_date, member_id, delivered_by_id, delivery_year)
|
|
values (now(), 8, 186, '2024-02-21')
|
|
|
|
select count(*) from deliveries d
|
|
|
|
update deliveries set delivery_year = '2024-01-01'
|
|
|
|
create table sust.payments_csv
|
|
(id serial,
|
|
pc_valuta_date date,
|
|
pc_client varchar(50),
|
|
pc_reference varchar(50),
|
|
pc_intended varchar(50),
|
|
pc_iban varchar(50),
|
|
pc_entry_date date,
|
|
pc_amount money,
|
|
pc_currency varchar(3),
|
|
pc_timestamp timestamp)
|
|
|
|
COPY payments_csv(pc_valuta_date, pc_client, pc_reference, pc_intended, pc_iban, pc_entry_date, pc_amount, pc_currency, pc_timestamp)
|
|
FROM 'C:\Users\zisco\OneDrive\Dokumente\SUST\UmsatzListe_AT493443700000113407_v1.csv'
|
|
DELIMITER ';'
|
|
CSV HEADER;
|
|
|
|
CREATE UNIQUE INDEX payments_ukey_member_year ON sust.payments USING btree (member_id, payment_year)
|
|
|
|
select count(*) from payments_csv where pc_processed = true
|
|
select from payments_csv where pc_processed = false
|
|
|
|
select * from payments_csv pc where PC.pc_client like '%Anna%';
|
|
select * from members mem where mem.member_firstname like 'Anna%';
|
|
select * from payments p where p.member_id = 58;
|
|
|
|
ALTER TABLE payments_csv ADD PRIMARY KEY (id);
|
|
|
|
select * from payments p where p.member_id in (205,206);
|
|
select * from payments p where date_part('year', p.payment_year) = 2025;
|
|
|
|
select * from payments_csv where pc_processed = false;
|
|
|
|
select pc.pc_reference from payments_csv pc where pc_processed =false order by pc_reference asc
|
|
|
|
select m.member_surname, m.member_firstname, m.member_firstname_spouse from members m
|
|
except
|
|
select mp.member_surname, mp.member_firstname, mp.member_firstname_spouse from members_paid mp
|
|
|
|
select * from members_paid mp where id = 8
|
|
|
|
SELECT ma.*, m.member_suffix, m.member_email
|
|
FROM members_active ma
|
|
JOIN members m ON m.id = ma.id
|
|
-- WHERE m.member_email IS NOT NULL
|
|
-- AND TRIM(m.member_email) <> ''
|
|
ORDER BY m.member_surname, m.member_firstname;
|
|
|
|
|
|
-- already delivered
|
|
select m.*, d.*
|
|
from members_active m
|
|
right join deliveries d on d.member_id = m.id
|
|
where d.delivery_year = '2025-01-01'
|
|
order by delivery_date asc
|
|
|
|
select * from deliveries d
|
|
where d.delivery_year = '2025-01-01'
|
|
|
|
insert into deliveries (delivery_date, member_id, delivery_year)
|
|
select now(), ss.id, '2024-01-01' from
|
|
(select id from members_active m
|
|
except
|
|
select member_id from deliveries d) ss
|
|
|
|
select count(*) from deliveries d
|
|
|
|
-- already paid?
|
|
SELECT m.*
|
|
FROM members_active m
|
|
JOIN deliveries d ON d.member_id = m.id
|
|
WHERE m.id IN (
|
|
SELECT md.id
|
|
FROM members_delivered md
|
|
EXCEPT
|
|
SELECT mp.id
|
|
FROM members_paid mp
|
|
WHERE mp.payment_year IN ('2025-01-01')
|
|
)
|
|
ORDER BY m.member_surname, m.member_firstname, d.delivery_date ASC;
|
|
|
|
SELECT id
|
|
FROM members_active
|
|
WHERE member_email is Null
|
|
AND id NOT IN (
|
|
SELECT member_id FROM (
|
|
VALUES (116), (26), (65), (66), (70), (8), (88), (130), (146), (163), (195),
|
|
(76), (120), (125), (126), (131), (136), (143), (151), (152), (160),
|
|
(166), (170), (172), (179), (196), (34), (40), (41), (42), (47),
|
|
(52), (71), (147), (194), (124), (178), (23), (123), (144), (168),
|
|
(182), (198), (31), (33), (43), (58), (75), (94), (100), (108), (11),
|
|
(111), (115), (117), (118), (119), (122), (127), (128), (13), (138),
|
|
(141), (148), (15), (153), (158), (159), (16), (161), (162), (165),
|
|
(169), (171), (175), (176), (177), (18), (180), (181), (19), (192),
|
|
(193), (2), (202), (204), (205), (24), (27), (28), (29), (3), (30),
|
|
(35), (36), (4), (46), (48), (5), (59), (6), (61), (62), (63), (68),
|
|
(77), (78), (79), (80), (81), (83), (84), (86), (87), (9), (90),
|
|
(92), (93), (95), (96), (97), (98), (99), (203)
|
|
) AS extracted_members(member_id)
|
|
);
|
|
|
|
-- Members active in 2025 but haven't paid for 2025
|
|
SELECT m.*
|
|
FROM members_active m
|
|
WHERE m.id NOT IN (
|
|
SELECT mp.id
|
|
FROM members_paid mp
|
|
WHERE mp.payment_year = '2025-01-01'
|
|
AND mp.id IS NOT NULL
|
|
)
|
|
ORDER BY m.member_surname, m.member_firstname;
|
|
|
|
-- number of active/inactive/deactivated members
|
|
SELECT SUM(m.member_count)
|
|
FROM members m
|
|
JOIN member_states s ON m.current_state_code = s.state_code
|
|
WHERE s.state_code = 'I';
|
|
|
|
SELECT *
|
|
FROM members m
|
|
JOIN member_states s ON m.current_state_code = s.state_code
|
|
WHERE s.state_code = 'I';
|
|
|
|
update members m
|
|
set member_count = 1
|
|
--JOIN member_states s ON m.current_state_code = s.state_code
|
|
WHERE current_state_code = 'I' and m.member_count =0;
|
|
|
|
create trigger update_members_updated_on_membership_end before
|
|
update
|
|
of member_membership_end on
|
|
sust.members for each row execute function update_active_on_members_death()
|