Not exists/Where NOT in Doing a pie chart in crystal. Items are   


Inquiries  
Referrals  
Followups  


A referral has to have an inquiry, A followup has to have a referral.

I want to show pie chart of referrals to inquiries so the slice would be the referred count, the rest of the pie would be the non-referred inquires.    

Table A:

customer001 INQ 1/1/2013  
customer001 INQ 9/30/2013.  
customer001 REF 10/15/2013.    
customer001 FOL 10/30/2013.    


This customer has 4 records. First off I will ignore the followup record in my crystal reports record filter.

That leaves:

customer001 INQ 1/1/2013  
customer001 INQ 9/30/2013  
customer001 REF 10/15/2013  


Now when I run my chart the pie will show 2 INQ's and 1 REF in the pie chart.
But I want it to be a 50/50 pie, because the INQ record that is most recent belongs to the REF record of 10/15.  

I need a SQL statement that removes the 9/30/2013 record out of a view.
I have written code (Top(1) MAX of the date field in a view that gives me this output with the most recent INQ record inserted to the end of all records. Now I need a second statement that strips the duplicate record out.  

ID---------Code--Date----INQcode---INQdate  
customer001 INQ 1/1/2013 INQ 9/30/2013   
customer001 INQ 9/30/2013 INQ 9/30/2013  
customer001 REF 10/15/2013 INQ 9/30/2013.  


So in my second unwritten view I need to write out only the records where code&lt;&gt; INQcode AND Date&lt;&gt;INQdate
If you use the ROW_NUMBER function partitioning over "Code" and ordering by "Date" descending like the following query

SELECT ROW_NUMBER() OVER (PARTITION BY CODE ORDER BY DATE DESC) AS rno,
     ID,
     CODE,
     CONVERT (VARCHAR, DATE, 101) AS fdate
FROM   A
WHERE  CODE &lt;&gt; 'FOL'


you will get.

    RNO ID           CODE       FDATE
    1   customer001  INQ        09/30/2013
    2   customer001  INQ        01/01/2013
    1   customer001  REF        10/15/2013


Then if you use a left outer join using that same select statement on RNO where the code of the right table is 'REF' like below

SELECT a1.ID,
   a1.CODE AS Code1,
   a2.CODE AS Code2,
   a1.fdate AS fdate1,
   a2.fdate AS fdate2
FROM
(
  SELECT ROW_NUMBER() OVER (PARTITION BY CODE ORDER BY DATE DESC) AS rno,
     ID,
     CODE,
     CONVERT (VARCHAR, DATE, 101) AS fdate
  FROM A
  WHERE CODE &lt;&gt; 'FOL'
) AS a1
LEFT OUTER JOIN
(
  SELECT ROW_NUMBER() OVER (PARTITION BY CODE ORDER BY DATE DESC) AS rno,
     ID,
     CODE,
     CONVERT (VARCHAR, DATE, 101) AS fdate
  FROM   A
  WHERE  CODE &lt;&gt; 'FOL'
  ) AS a2
  ON a1.rno = a2.rno
  AND a2.CODE = 'REF'
  WHERE  a1.CODE &lt;&gt; 'REF'
  ORDER BY FDATE1


and finally use a where clause filter on the left table where code is not equal to 'REF' you will get the following.

    ID          CODE1   CODE2   FDATE1      FDATE2
    customer001 INQ     (null)  01/01/2013  (null)
    customer001 INQ     REF     09/30/2013  10/15/2013


Hope this helps or is what you are looking for.