--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)