>I'm trying to create a View in SQL 2000 from/for a TimeClock database that will select the last record for employees (that aren't currently clocked in)
>
>Here is a Select statement and the data below is returned:
>
>

>SELECT     TOP 100 PERCENT dbo.EmployeeHours.EmployeeId, dbo.EmployeeHours.TimeIn, dbo.EmployeeHours.TimeOut, dbo.EmployeeHours.JobCode, 
>                      dbo.MasterJobCodeList.Description, dbo.EmployeeHours.CostCode
>FROM         dbo.EmployeeHours INNER JOIN
>                      dbo.MasterCostCodeList ON dbo.EmployeeHours.CostCode = dbo.MasterCostCodeList.Level1 INNER JOIN
>                      dbo.MasterJobCodeList ON dbo.EmployeeHours.JobCode = dbo.MasterJobCodeList.JobCode
>WHERE     (dbo.MasterCostCodeList.Active = 1) AND (NOT (dbo.EmployeeHours.TimeOut IS NULL))
>ORDER BY dbo.EmployeeHours.TimeIn DESC
>

>
>

>EmpID	timeIn	TimeOut	JobCode	Description	CostCode
>118	8/14/2013 6:59	8/14/2013 7:02	3	Jobs	18725
>133	8/14/2013 6:59	8/14/2013 8:11	2	Drafting	18871
>72	8/14/2013 6:58	8/14/2013 8:20	3	Jobs	17819
>10	8/14/2013 6:57	8/14/2013 8:04	10	Laser	18860
>10	8/14/2013 6:52	8/14/2013 6:57	10	Laser	18861
>8	8/14/2013 6:46	8/14/2013 8:00	2	Drafting	18658
>115	8/14/2013 6:44	8/14/2013 7:18	3	Jobs	18886
>122	8/14/2013 6:44	8/14/2013 7:10	3	Jobs	16112
>47	8/13/2013 16:08	8/13/2013 16:30	3	Jobs	18748
>122	8/13/2013 15:33	8/13/2013 16:42	10	Laser	16112
>10	8/13/2013 15:18	8/13/2013 15:42	10	Laser	18658
>122	8/13/2013 15:17	8/13/2013 15:33	3	Jobs	16112
>122	8/13/2013 15:13	8/13/2013 15:17	10	Laser	16112
>47	8/13/2013 15:11	8/13/2013 16:08	3	Jobs	18905
>7	8/13/2013 15:09	8/13/2013 16:33	3	Jobs	18833
>34	8/13/2013 15:04	8/13/2013 16:22	2	Drafting	18903
>7	8/13/2013 15:04	8/13/2013 15:09	3	Jobs	18658
>

>
>How would one go about retrieving the last record for each EmpID and returning only one record for each EmpID?
>
>I hope I explained that clearly.

SELECT dbo.EmployeeHours.EmployeeId,
       dbo.EmployeeHours.TimeIn,
       dbo.EmployeeHours.TimeOut,
       dbo.EmployeeHours.JobCode, 
       dbo.MasterJobCodeList.Description,
       dbo.EmployeeHours.CostCode
FROM dbo.EmployeeHours
INNER JOIN dbo.MasterCostCodeList ON dbo.EmployeeHours.CostCode = dbo.MasterCostCodeList.Level1
INNER JOIN dbo.MasterJobCodeList ON dbo.EmployeeHours.JobCode = dbo.MasterJobCodeList.JobCode
INNER JOIN (SELECT EmployeeId,
                  MAX(TimeIn) AS TimeIn
            FROM dbo.EmployeeHours
            GROUP BY EmployeeId) EmplMax
      ON EmplMax.EmployeeId dbo.EmployeeHours.EmployeeId
     AND EmplMax.TimeIn = dbo.EmployeeHours.TimeIn
WHERE dbo.MasterCostCodeList.Active = 1
  AND NOT dbo.EmployeeHours.TimeOut IS NULL
ORDER BY dbo.EmployeeHours.TimeIn DESC