title: How can I get a count of Tickets Closed on a given day?
body: <p>I have a table that stores the status changes of a ticket.  Some tickets were re-opened after being closed.  And in certain cases (example below) re-opened several times.</p>
  
  <pre><code>Ticket  SEQUENCE    NEWVALUE    OLDVALUE        TIMESTAMP
  5879    15870       REQUEST     NULL        2015-06-10 12:13:28.000
  5879    16256       Pending     REQUEST     2015-06-11 14:26:38.000
  5879    21642       Open        Pending     2015-07-02 13:32:47.000
  5879    21943       Pending     Open        2015-07-06 09:55:37.000
  5879    23195       Open        Pending     2015-07-09 15:00:47.000
  5879    24917       Pending     Open        2015-07-17 15:09:28.000
  5879    25903       Closed      Pending     2015-07-22 11:13:03.000
  5879    25940       Open        Closed      2015-07-22 12:56:05.000
  5879    25972       Pending     Open        2015-07-22 13:54:30.000
  5879    25996       Closed      Pending     2015-07-22 14:31:05.000
  5879    26484       Pending     Closed      2015-07-24 14:43:28.000
  5879    26776       Closed      Pending     2015-07-27 11:34:00.000
  </code></pre>
  
  <p>I am trying to determine the last time a ticket was Closed.  But I have no idea how to even begin this query.  Could someone provide a clue?  Thanks so much!</p>
  
  <p>The ultimate result that I am trying to get would look like this:</p>
  
  <pre><code>Date        Tickets Submitted   Tickets Closed
  2015-06-01      123                 44
  2015-06-02      125                 45
  2015-06-03      147                 51
  2015-06-04      190                 58
  2015-06-05      132                 38
  2015-06-06      93                  28
  2015-06-07      126                 30
  2015-06-08      167                 50
  </code></pre>
  
  <p>My current query is:</p>
  
  <pre><code>SELECT CAST(SUBMITDATE as date) as 'Date'
  
  ,COUNT(*) as [Tickets Submitted]
  ,SUM(CASE WHEN NEWVALUE = 'Closed' THEN 1 ELSE 0 END) as [Tickets Closed]
  
  FROM dbo.MasterTable
  FULL OUTER JOIN FIELDHISTORY
  ON MasterTable.Ticket = FIELDHISTORY.Ticket
  
      WHERE SUBMITDATE &gt;= '2015-06-01'
          AND SUBMITDATE &lt; '2015-06-08'
  
  GROUP BY CAST(SUBMITDATE as date)
  ORDER BY CAST(SUBMITDATE as date)
  </code></pre>
  
  <p>The problem with this query is that it doesn't show me how many tickets were closed on that particular date. It merely shows the number of tickets that were closed from the pool of tickets that were opened on that particular day.  In other words, from the example above, on 6/1/2015, 123 tickets were created and 44 of those tickets are currently closed.  The 44 closed tickets figure can change if someone were to close another ticket that was created on 6/1.</p>
  
  <p>Hope that isn't too confusing!  =D</p>