title: Need help writing query.
body: <p>Edit: Below is the data that I'm working with. The query that I wrote gives me duplicates based on the OCCPSTAT field (Far right column of data, either C or N). </p>
  
  <p>I need a query that would pull the record closest to the date I define (code does this) but, if there is a C and N copy of the record, I want only the N record. </p>
  
  <pre><code>5000    1000    D001    RNT 100 11/1/2014 0:00  C
  5000    1000    D001    GAS 200 11/1/2014 0:00  C
  5000    1000    D001    ELC 300 11/1/2014 0:00  C
  5000    1000    D001    WAT 400 11/1/2014 0:00  C
  5000    1000    D001    TAX 500 11/1/2014 0:00  C
  5000    1000    D001    INS 600 11/1/2014 0:00  C
  5000    1000    D001    RNT 200 11/1/2015 0:00  C
  5000    1000    D001    GAS 300 11/1/2015 0:00  C
  5000    1000    D001    ELC 400 11/1/2015 0:00  C
  5000    1000    D001    WAT 500 11/1/2015 0:00  C
  5000    1000    D001    TAX 600 11/1/2015 0:00  C
  5000    1000    D001    INS 700 11/1/2015 0:00  C
  5000    1000    D001    RNT 300 11/1/2016 0:00  C
  5000    1000    D001    GAS 400 11/1/2016 0:00  C
  5000    1000    D001    ELC 500 11/1/2016 0:00  C
  5000    1000    D001    WAT 600 11/1/2016 0:00  C
  5000    1000    D001    TAX 700 11/1/2016 0:00  C
  5000    1000    D001    INS 800 11/1/2016 0:00  C
  5000    1000    D001    RNT 500 11/1/2017 0:00  C
  5000    1000    D001    GAS 600 11/1/2017 0:00  C
  5000    1000    D001    ELC 700 11/1/2017 0:00  C
  5000    1000    D001    WAT 800 11/1/2017 0:00  C
  5000    1000    D001    TAX 900 11/1/2017 0:00  C
  5000    1000    D001    INS 100011/1/2017 0:00  C
  5000    1000    D001    RNT 100 11/1/2014 0:00  N
  5000    1000    D001    GAS 200 11/1/2014 0:00  N
  5000    1000    D001    ELC 300 11/1/2014 0:00  N
  5000    1000    D001    WAT 400 11/1/2014 0:00  N
  5000    1000    D001    TAX 500 11/1/2014 0:00  N
  5000    1000    D001    INS 600 11/1/2014 0:00  N
  5000    1000    D001    RNT 200 11/1/2015 0:00  N
  5000    1000    D001    GAS 300 11/1/2015 0:00  N
  5000    1000    D001    ELC 400 11/1/2015 0:00  N
  5000    1000    D001    WAT 500 11/1/2015 0:00  N
  5000    1000    D001    TAX 600 11/1/2015 0:00  N
  5000    1000    D001    INS 700 11/1/2015 0:00  N
  5000    1000    D001    RNT 300 11/1/2016 0:00  N
  5000    1000    D001    GAS 400 11/1/2016 0:00  N
  5000    1000    D001    ELC 500 11/1/2016 0:00  N
  5000    1000    D001    WAT 600 11/1/2016 0:00  N
  5000    1000    D001    TAX 700 11/1/2016 0:00  N
  5000    1000    D001    INS 800 11/1/2016 0:00  N
  5000    1000    D001    RNT 500 11/1/2017 0:00  N
  5000    1000    D001    GAS 600 11/1/2017 0:00  N
  5000    1000    D001    ELC 700 11/1/2017 0:00  N
  5000    1000    D001    WAT 800 11/1/2017 0:00  N
  5000    1000    D001    TAX 900 11/1/2017 0:00  N
  5000    1000    D001    INS 1000    11/1/2017 0:00  N
  </code></pre>
  
  <p>The query is:</p>
  
  <pre><code>SELECT
      BLDGID,
      LEASID,
      SUITID,
      INCCAT,
      AMOUNT,
      EFFDATE, 
      OCCPSTAT
  
  FROM( 
      SELECT
      CM.BLDGID,
      CM.LEASID,
      LS.SUITID,
      CM.INCCAT,
      CM.AMOUNT,
      CM.EFFDATE,
      LS.OCCPSTAT,
  ROW_NUMBER() OVER(PARTITION BY CM.BLDGID, CM.LEASID, LS.SUITID, CM.INCCAT, LS.OCCPSTAT
  ORDER BY CM.EFFDATE DESC) AS rowno 
  
   FROM SQLDATA.DBO.CMRECC CM INNER JOIN SQLDATA.DBO.LEAS LS ON CM.LEASID = LS.LEASID
   WHERE (CM.EFFDATE &lt; '20170601') AND (CM.BLDGID = '85000') AND (LS.OCCPSTAT = 'C' OR LS.OCCPSTAT = 'N')
  
  
  ) AS d 
  WHERE rowno = 1
  </code></pre>