title: Microsoft SQL-Apply "Where" Clause to Particular Columns
body: <p>I am trying to get the number of days that a person has total recording time over a certain amount for multiple amounts. Right now I am only able to get one column of accurate data for number of days(I am currently getting days over 60 in all of the days columns. Essentially I want to return a 0 for all persons not having any days for that particular recording length increment. I also want to get the number of days for all increments for persons having days with recording lengths in those increments.</p>
  
  <p>Essentially I wish I could put the "Where" clause with column name as I define the column.(I know it doesn't work that way) What would the best way to be to tackle this problem?</p>
  
  <p>Here is my code:</p>
  
  <pre><code>WITH CTERecordingsByDate AS
  (
      SELECT      Person.FirstName,
                  Person.LastName,
                  CAST(Created AS DATE) AS Whole_Date,
                  SUM(Length)/60 AS Recordings_Sum, --Length is in seconds
      FROM        Recordings
      JOIN        Person ON Recordings.PersonID=Person.ID
      WHERE       Created BETWEEN '2013-07-01 00:00:00.000' 
                          AND     '2013-08-01 00:00:00.000'
      GROUP BY    Person.FirstName, 
                  Person.LastName,
                  CAST(Created AS DATE)
  )
  
  SELECT      FirstName, 
              LastName, 
              COUNT(*) AS DaysAbove60
              COUNT(*) AS DaysAbove100
              COUNT(*) AS DaysAbove140
  FROM        CTERecordingsByDate
  WHERE       Recordings_Sum &gt;= 60
  AND         Recordings_Sum &lt; 100
  GROUP BY    FirstName, LastName
  ORDER BY    COUNT(*) DESC
  </code></pre>