title: SQL Server 2008 CASE Logic in SELECT statement
body: <p>Hello again SQL Server 2008 gurus.</p>
  <p>I need to apply the following rules to the setting of a worker's start and end times for their work day (hourly employees) in a <code>SELECT</code> statement. I apologize in advance for my SQL ignorance.</p>
  <ol>
  <li><p>The rule is to set their start time to a value stored in a table field for that worker, if they login on or before their start time (a time stored in the worker <code>starttime</code> column) and therefore get credit for starting at their start time.</p>
  <p>If they log out within a 10 minute period before or anytime after their end time stored in a column for the worker, they get credit for their full day, another value stored in a column of the worker table, otherwise they are penalized some percentage of an hour, i.e. their log out time rounded to .25 of an hour less closest to the time they logged out. i.e. if they are set to log out at 4:30, and they log out at 4:18, their log out time is 4:15. If they log out at  4:20, and are set to log out at 4:30, their log out time is 4:30.</p>
  </li>
  <li><p>The first rule applies to all hourly employees where their workday hours is less than or equal to their expected workday value. The caveat is, for those where overtime is ok (a bit value set to 1). If overtime is permitted, the number of billable hours can exceed the full day value stored for them, and therefore the value of their logout - login time can exceed their fullday value.</p>
  <p>The question is, can these rules be calculated in the SELECT statement and if so can I get some help with the code?</p>
  </li>
  </ol>
  <p>The columns containing the information are:</p>
  <pre><code>    worker.startime (TIME)
      worker.endtime  (TIME)
      worker.overtimeallowed (BIT)
      worker.workdayhours (decimal (12,2))
      worker.penaltyvalue (decimal (12,2))
  </code></pre>
  <p>If it requires a UDF or stored procedure (since I'm using the Telerik ReportViewer) I'm not sure it would be supported, but that's probably another question.</p>
  <p>So far I've gotten some help with applying some CASE logic - calculating whether a worker get's credit for their 1/2 lunch. The code that was supplied works as promised. This, I believe may be an extension to that logic - so I'll provide the code I have here:</p>
  <pre><code>      -- for testing purposes only.
        DECLARE @StartDate AS DateTime
        SET @StartDate = CAST('03/25/2012' AS DATE)
        DECLARE @EndDate AS DateTime
        SET @EndDate = CAST('04/10/2012' AS DATE)
  
        SELECT 
        w.Firstname 
       ,w.Lastname 
       ,wf.Login
       ,wf.logout
       ,ROUND(CAST(DATEDIFF(MI, wf.Login, wf.Logout) AS DECIMAL)/60,2) 
       - CASE 
          WHEN DATEDIFF(hour, wf.Login, wf.Logout) &lt; w.MinimumHours THEN      
            w.LunchDeduction
          ELSE
          0
         END AS [Hours Credited]
       FROM Workers AS w 
       JOIN Workflow AS wf 
       ON wf.LoggedInWorkerid = w.ID 
       JOIN Roles AS r
       ON w.RoleID = r.RoleID
       WHERE (r.Descript = 'Hourly')
       AND wf.Login &gt;= @StartDate AND wf.Logout &lt;= @EndDate 
       ORDER BY w.Lastname, w.Firstname
  </code></pre>