How to select first entry of the day grouped by user in SQL

I've looked around and can't quite grasp the whole answer to this SQL query question needed to extract data from an MS Access 2000 table.

Here's an example of what the table [Time Sub] looks like:

**CLIENT_ID, DATE_ENTERED, CODE, MINUTES开发者_C百科**  
11111, 5/12/2008 3:50:52 PM, M, 38  
11111, 5/12/2008 2:55:50 PM, M, 2  
11714, 5/13/2008 1:15:32 PM, M, 28  
11111, 5/13/2008 6:15:12 PM, W, 11  
11112, 5/12/2008 2:50:52 PM, M, 89  
11112, 5/12/2008 5:10:52 PM, M, 9  
91112, 5/14/2008 1:10:52 PM, L, 96
11112, 5/12/2008 5:11:52 PM, M, 12

I need to select the first entry of each day per client that's NOT code L or W.

I know this can be done in a SQL statement, but I just can't figure out how. I can get close, but never come up with the right output.

Any help is appreciated.

Thanks, Mike

---

Select ...
From Table As T
Where Date_Entered = (
                        Select Min(T2.Date_Entered)
                        From Table As T2
                        Where T2.Client_Id = T.Client_Id
                            And DateDiff("d", 0, T2.Date_Entered) = DateDiff("d", 0, T.Date_Entered)
                            And T2.Code Not In("L","W")
                        )

---

Give this a spin. I think it's what you're asking for.

SELECT
  [Time Sub].CLIENT_ID,
  [Time Sub].CODE,
  MinDate.TheDay
FROM
  [Time Sub] INNER JOIN
    (
    SELECT
      [Time Sub].CLIENT_ID,
      MIN([Time Sub].DATE_ENTERED) as MinimumDate,
      Format([Time Sub].DATE_ENTERED, "YYYY-MM-DD") AS TheDay
    FROM
      [Time Sub]
    GROUP BY
      [Time Sub].CLIENT_ID,
      Format([Time Sub].DATE_ENTERED, "YYYY-MM-DD")
    ) AS MinDate
  ON MinDate.MinimumDate = [Time Sub].DATE_ENTERED AND MinDate.CLIENT_ID = [Time Sub].CLIENT_ID
WHERE
  [Time Sub].CODE NOT IN ("L", "W")

---

I have never used MS Access, so you may have to correct for SQL differences:

select * from [Time Sub] A 
where
  DATE_ENTERED = (
    select top 1 DATE_ENTERED
    from [Time Sub] B
    where
      Int(A.DATE_ENTERED) = Int(B.DATE_ENTERED)
      and A.CLIENT_ID = B.CLIENT_ID
    order by
      DATE_ENTERED        
    )
  and (CODE <> 'L')
  and (CODE <> 'W')

---

I figured it out after using pieces of what everyone else had already done. Here's what I came up with:

SELECT [Time sub].CLIENT_ID, 
       [Time sub].Code, 
       [Time sub].Minutes, 
       FirstDay.MinEntry

FROM [Time sub] 
     INNER JOIN [
        SELECT
         [Time sub].CLIENT_ID,
         MIN([Time sub].[Date_Entered]) AS MinEntry

        FROM
         [Time sub]

        WHERE
          [Time sub].Code NOT IN ("T", "L")

       GROUP BY
          [Time sub].CLIENT_ID,
          DateValue([Time sub].[Date_Entered])

 ].AS FirstDay 

 ON FirstDay.MinEntry = [Time sub].[Date_Entered]

 ORDER BY FirstDay.MinEntry, [Time sub].CLIENT_ID;