use credit
go

select * from member  -- 10000 rows
select * from charge  -- 1,600,000 rows

-- about 9% of members had no charges at all
select distinct m.member_no from member m, charge c
where m.member_no = c.member_no

select m.member_no from member m
 where not exists (select * from charge c where m.member_no = c.member_no)


-- Find members that did not have a charge after 06-20-1999

SET STATISTICS IO ON

SELECT m.member_no 
FROM member m, charge c
WHERE  m.member_no = c.member_no
GROUP BY m.member_no
HAVING MAX(c.charge_dt) < '19990620'

SELECT m.member_no
FROM member m
WHERE NOT EXISTS (SELECT * FROM charge c
                  WHERE  c.member_no = m.member_no AND c.charge_dt >= '19990620')
AND EXISTS (SELECT * FROM charge c
			WHERE  c.member_no = m.member_no)

SELECT member_no
FROM (SELECT m.member_no,
       (SELECT MAX(charge_dt)  
        FROM   charge c
        WHERE m.member_no = c.member_no) as maxdt
      FROM member m
) as ch
WHERE ch.maxdt < '19990620'

