>SELECT Timehd.empid, Timehd.date,; > IIF(Timeitms.timeout>17.AND.Timeitms.timein>17,Timeitms.timeout-Timeitms.timein,Timeitms.timeout-17); > FROM prewel!timehd INNER JOIN prewel!timeitms ; > ON Timehd.timecrdno = Timeitms.timecrdno; > WHERE Timehd.date = CTOD(?NEXTCHKDATE1)-6; > AND Timeitms.jobno <> "LUNCH"; > GROUP BY Timehd.empid, Timehd.date; > HAVING Timeitms.timeout>17; > ORDER BY Timehd.empid >>
>DECLARE @dNextChkDate DATETIME >SET @dNextChkDate = CONVERT(DATETIME, '04/21/2000',101)-(14*1) > >SELECT Timehd.empid, Timehd.date, Timeitms.timeout, Timeitms.Timein, >CASE >WHEN Timeitms.timeout>17 AND Timeitms.timein>17 THEN Timeitms.timeout-Timeitms.timein >ELSE Timeitms.timeout-17 >END AS Newcolumn >FROM timehd INNER JOIN timeitms >ON Timehd.timecrdno = Timeitms.timecrdno >WHERE Timehd.date = @dNextChkDate-6 >AND Timeitms.jobno <> 'LUNCH'>