title: SQL aggregate query with bit-field
body: <p>I have an SQL table that looks like this:</p>
  
  <pre><code>ID          uniqueidentifier
  ID_Task     uniqueidentifier
  ID_Employee uniqueidentifier
  Comment     nvarchar(256)
  Complete    int
  Date        datetime
  Days        decimal(6, 2)
  Locked      bit
  Created     datetime
  Edited      datetime
  Deleted     bit
  </code></pre>
  
  <p>What I want to do is add up all of the "Days", for each Date, for a given ID_Employee.  I managed to work out a query that looks like this:</p>
  
  <pre><code>SELECT  CAST([Note].Date AS DATE) As Date,
          SUM([Note].Days) AS Total
          FROM
          Note
          WHERE
          [Note].ID_Employee = N'E6A0E609-F8B2-4B48-A17C-4A4E117A4077'
          GROUP BY        
          CAST(Note.Date AS DATE)
  </code></pre>
  
  <p>This gives me a result set of the total "Days" for each unique "Date", such as:</p>
  
  <pre><code>2013-06-20  1.00
  2013-06-21  0.75
  </code></pre>
  
  <p>, showing employee E6A0E609-F8B2-4B48-A17C-4A4E117A4077 worked 1 man-day on the 20th and 3/4 of a man-day on the 21st.  There can be any number of individual records for any day of the year, as a given employee might have worked different amounts of time on different ID_Tasks.</p>
  
  <p>Now what I want to do is add another field to the result set, that's "false" if any one of the records had "Locked" set to 1, else "true" if they were all 0.  In simple language the question I'm wanting to ask the database is, "for all Date in the table, tell me the total Days worked and whether all records for that day have been Locked, for a given Employee".  So what I want the result set to show is:</p>
  
  <pre><code>2013-06-20  1.00    TRUE
  2013-06-21  0.75    FALSE
  </code></pre>
  
  <p>Meaning the employee has signed off his work for the 20th (TRUE, all records have 1 in the Locked bit-field), and one or more records not yet signed off for the 21st, meaning one or more records for this employee on the 21st have a 0 in the Locked bit-field).</p>
  
  <p>Did that make sense?  Anyway, I can't get my head around the best way of doing this.  </p>
  
  <p>Any SQL genius lurking who can assist?</p>
  
  <p>EDIT:</p>
  
  <p>OK, thanks to the ideas I got from the answers below, I added the CASE line to the query.  Basically it says, "if the lowest number in Locked for any record on this date is zero, then not all the records have been signed off, else they all have been".</p>
  
  <pre><code>SELECT  CAST([Note].Date AS DATE) As Date,
          SUM([Note].Days) AS Total,
          CASE MIN(CONVERT(INT, Locked)) WHEN 0 THEN 0 ELSE 1 END AS Locked
          FROM
          Note
          WHERE
          [Note].ID_Employee = N'E6A0E609-F8B2-4B48-A17C-4A4E117A4077'
          GROUP BY        
          CAST(Note.Date AS DATE)
  </code></pre>