title: How to get status of a record on specific day. SQL
body: <p>I'm trying to create a query to see if a record(Alert) was open during a specific time period. </p>
  
  <p>The first table has only the current status of the record. </p>
  
  <pre><code>    table: ALERT
  
      intID  |CurrentStateID| DateCreated
     -----------------------------------
      3      |Closed        | 10/11/2009
  </code></pre>
  
  <p>the second table has the history of the status for the alert. </p>
  
  <pre><code>    tblState
  
      intContextID|strToStateName|datTimeStamp
     -----------------------------------
      3       |Unassigned    |10/11/2009
      3       |Closed        |10/14/2009
  </code></pre>
  
  <p>Here is my desired output:</p>
  
  <pre><code>    DESIRED OUTPUT
  
      DATE        |DAY        |TOTAL_OPEN
      -----------------------------------
      10/10/2009  |Friday     |0
      10/11/2009  |Saturday   |1
      10/12/2009  |Sunday     |1
      10/13/2009  |Monday     |1
      10/14/2009  |Tuesday    |0
      10/15/2009  |Wednesday  |0
  </code></pre>
  
  <p>I was able to write some of the code but I think its the join on the AllDays table that might be wrong. </p>
  
  <pre class="lang-sql prettyprint-override"><code>DECLARE @StartDate DATETIME = '2009-10-10';
  DECLARE @EndDate DATETIME = '2009-10-15 23:59:59';
  
  WITH AllDays
  AS (
      SELECT @StartDate AS [Date]
          , 1 AS [level]
  
      UNION ALL
  
      SELECT DATEADD(DAY, 1, [Date])
          , [level] + 1
      FROM AllDays
      WHERE [Date] &lt; @EndDate
      )
  SELECT CAST(AllDays.[Date] AS DATE) AS 'DATE'
      , datename(dw, AllDays.[Date]) AS 'DAY'
      ,ISNULL(TOTAL_OPEN, 0) as TOTAL_OPEN
  FROM AllDays
  LEFT JOIN (
      SELECT DISTINCT s.datTimeStamp AS 'DATE'
          , count(A.intID) AS 'TOTAL_OPEN'
      FROM Alert A
      INNER JOIN tblState S ON A.intID = S.intContextID
      WHERE strToStateName = 'Unassigned'
      GROUP BY datTimeStamp
      ) AS TOTAL_OPEN ON TOTAL_OPEN.DATE = AllDays.[Date]
  </code></pre>
  
  <p>The Alert was open from 10-11 to 10-13 but since I'm joining on datetimestamp the results only show 1 for 10/11.</p>
  
  <p>Here's the schema <a href="http://sqlfiddle.com/#!18/d47b0/11/0" rel="nofollow noreferrer">a link</a>! </p>