You can use CASE function

SUM( CASE WHEN ShiftStart < '09/25/2003 1:30:00 PM' 
         AND ShiftEnd > '09/25/2003 1:59:59 PM' THEN 1 ELSE 0 END )/2 as one30_pm

>In VFP I can do this:
>

>SELECT LocationID, SUM(IIF(ShiftStart < CTOT('09/25/2003 1:30:00 PM') 
>         AND ShiftEnd > CTOT('09/25/2003 1:59:59 PM'),1,0))/2 as one30_pm ;
>	FROM Emp JOIN Shift ;
>	ON emp.empid = shift.empid ;
>	GROUP BY LocationID
>

>
>How can I do this in SQL Server?
>
>TIA