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 >>