Well George,
The CASE part of this Statement seems to work, but now I'm having some difficulty with the rest of it.

This is what I get from the VFP Statement:

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


EMPID   DATE     Exp_3
15   04/01/2000  3.000
16   04/01/2000  1.000
19   04/01/2000  0.500
36   04/01/2000  3.000
44   04/01/2000  0.500
47   04/01/2000  0.500
48   04/01/2000  1.000
53   04/01/2000  1.000

So far this is the closest I have gotten with SQL Server:

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'

Which in turn returns this:

EMPID        DATE           TIMEOUT   TIMEIN    NewColumn
 6    2000-04-01 00:00:00    8.330     6.830    -8.670
 6    2000-04-01 00:00:00    11.150    8.330    -5.850
 6    2000-04-01 00:00:00    11.810    11.150   -5.190
31    2000-04-01 00:00:00    9.350     6.760    -7.650
31    2000-04-01 00:00:00    11.930    9.350    -5.070
43    2000-04-01 00:00:00    7.780     7.030    -9.220
43    2000-04-01 00:00:00    11.950    7.780    -5.050
10    2000-04-01 00:00:00    8.250     7.600    -8.750
10    2000-04-01 00:00:00    9.130     8.250    -7.870
10    2000-04-01 00:00:00    12.050    9.130    -4.950
52    2000-04-01 00:00:00    10.980    6.160    -6.020
52    2000-04-01 00:00:00    12.060    10.980   -4.940
28    2000-04-01 00:00:00    7.310     7.030    -9.690
28    2000-04-01 00:00:00    9.050     7.310    -7.950
46    2000-04-01 00:00:00    11.700    8.580    -5.300
 8    2000-04-01 00:00:00    10.050    6.900    -6.950
34    2000-04-01 00:00:00    9.250     7.360    -7.750
53    2000-04-01 00:00:00    12.000    7.000    -5.000
53    2000-04-01 00:00:00    18.000    12.500    <b>1.000</b>
48    2000-04-01 00:00:00    12.000    7.000    -5.000
48    2000-04-01 00:00:00    18.000    12.500    <b>1.000</b>
16    2000-04-01 00:00:00    12.000    7.000    -5.000
16    2000-04-01 00:00:00    18.000    12.500    <b>1.000</b>
47    2000-04-01 00:00:00    12.000    7.000    -5.000
47    2000-04-01 00:00:00    17.500    12.500    <b>.500</b>
19    2000-04-01 00:00:00    12.000    7.000    -5.000
19    2000-04-01 00:00:00    17.500    12.500    <b>.500</b>
44    2000-04-01 00:00:00    12.000    7.000    -5.000
44    2000-04-01 00:00:00    17.500    12.500    <b>.500</b>
36    2000-04-01 00:00:00    12.000    7.000    -5.000
36    2000-04-01 00:00:00    20.000    12.500    <b>3.000</b>
15    2000-04-01 00:00:00    20.000    17.000    <b>3.000</b>
38    2000-04-01 00:00:00    13.000    8.410    -4.000

As you can see the correct values are in the result set. In VFP I just used the having clause to extinguish the unwanted values, but that doesn't seem to work with SQL Server. Do you have anymore suggestions?


>Have you tried the CASE expression? Something like...

>SELECT Timehd.empid, Timehd.date,
>	'newcolumn'=CASE
>	WHEN Timeitms.timeout>17 AND Timeitms.timein>17 THEN Timeitms.timeout-Timeitms.timein
>	ELSE Timeitms.timeout-17
>	END
> FROM  prewel!timehd INNER JOIN prewel!timeitms
>   ON  Timehd.timecrdno = Timeitms.timecrdno
> WHERE Timehd.date = ?m.dNextChkDate-13
>   AND Timeitms.jobno <> "LUNCH"
> GROUP BY Timehd.empid, Timehd.date
> HAVING Timeitms.timeout>17
> ORDER BY Timehd.empid

>HTH