--i Overall member
select a.store_cd, c.store_desc, b.balance_point, a.address1,a.address2, a.postcode, a.city, a.state, a.country, a.member_name, a.card_no, a.date_expired, a.home_phone, a.office_phone, a.mobile_phone, a.email
from principle_member a left join member_point_summary b on b.member_id = a.member_id
left join store c
on c.store_cd = a.store_cd;

--ii Non Expired member
select a.store_cd, c.store_desc, b.balance_point, a.address1,a.address2, a.postcode, a.city, a.state, a.country, a.member_name, a.card_no, a.date_expired, a.home_phone, a.office_phone, a.mobile_phone, a.email
from principle_member a left join member_point_summary b on b.member_id = a.member_id
left join store c
on c.store_cd = a.store_cd
where a.date_expired > '01-JUN-20';

--iii Expired member
select a.store_cd, c.store_desc, b.balance_point, a.address1,a.address2, a.postcode, a.city, a.state, a.country, a.member_name, a.card_no, a.date_expired, a.home_phone, a.office_phone, a.mobile_phone, a.email
from principle_member a left join member_point_summary b on b.member_id = a.member_id
left join store c
on c.store_cd = a.store_cd
where a.date_expired < '01-JUN-20';

select a.store_cd, c.store_desc, sum(b.balance_point)
from principle_member a left join member_point_summary b on b.member_id = a.member_id
left join store C
on C.STORE_CD = a.STORE_CD left join DAILY_POINTS_TRAN D on D.MEMBER_ID = a.MEMBER_ID
group by a.store_cd, c.store_desc

select store_cd, tran_type, sum(amount), sum(points) from DAILY_POINTS_TRAN_rpt
where tran_date between '01-OCT-16' and '30-SEP-18'
group by store_cd, tran_type

points balance = E - (R+F+A)