title: SQL Query for Count value from the latest date
body: <p>I need to have a query that returns the ff:</p>
  
  <ol>
  <li>Count from the latest Date in each Name</li>
  <li>If the value of Count from the latest Date is -1 then it will return the count of the Date before the latest Date</li>
  <li>If the value of Count from the latest Date is -1 and the other Date is -1. Then return 0</li>
  <li>If the value of Count from the latest Date is -1 and no other Date of that Name. Then return 0</li>
  </ol>
  
  <p>Example Table:</p>
  
  <pre><code>ID  Name  Date         Count
  1   Adj   09/29/2012   2
  2   Adj   09/30/2012   4
  3   Ped   09/29/2012  -1
  4   Ped   09/30/2012   5
  5   Mel   09/29/2012   3
  6   Mel   09/30/2012  -1
  7   Rod   09/30/2012   7
  8   Ney   09/30/2012  -1
  9   Jin   09/29/2012  -1
  10  Jin   09/30/2012  -1
  </code></pre>
  
  <p>Desired Output:</p>
  
  <pre><code>Name   Count
  Adj    4
  Ped    5
  Mel    3
  Rod    7
  Ney    0
  Jin    0
  </code></pre>
  
  <p>I am very confused on how to approach this in SQL since I only knew simple query.</p>
  
  <p>Any idea on how to make a query for this? Thanks.</p>
  
  <p>Btw, I'm sorry I forgot to include this. I am using SQL Server 2000.</p>