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

Question

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

Answer 1

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

Answer 2

See if this query is close to what you want:

SELECT
    ts.CLIENT_ID,
    DateValue(DATE_ENTERED) AS date_at_midnite,
    Min(DATE_ENTERED) AS first_entry
FROM [Time Sub] AS ts
WHERE ts.CODE <> "L" AND ts.CODE <> "W"
GROUP BY ts.CLIENT_ID, DateValue(DATE_ENTERED)

I wasn't sure whether you wanted to display CODE and MINUTES with the first entry for each client. If so, you could save that query, then create a new query using the saved one which you then JOIN (on CLIENT_ID and DATE_ENTERED) with [Time Sub].

Update: Here is my latest query. I renamed the table as Time_Sub to avoid needing to enclose the name in square brackets --- square brackets create problems for subqueries enclosed in square brackets.

SELECT
    ts1.CLIENT_ID,
    ts1.date_at_midnite,
    ts1.first_entry,
    ts2.CODE,
    ts2.MINUTES
FROM
    [SELECT
        CLIENT_ID,
        DateValue(DATE_ENTERED) AS date_at_midnite,
        Min(DATE_ENTERED) AS first_entry
    FROM
        Time_Sub
    WHERE
        CODE <> "L"
        AND CODE <> "W"
    GROUP BY
        CLIENT_ID,
        DateValue(DATE_ENTERED)
    ]. AS ts1
    INNER JOIN Time_Sub AS ts2
        ON (ts1.first_entry = ts2.DATE_ENTERED)
        AND (ts1.CLIENT_ID = ts2.CLIENT_ID)
ORDER BY
    ts1.CLIENT_ID,
    ts1.date_at_midnite;

This is the result set from that query using your sample data.

CLIENT_ID date_at_midnite first_entry          CODE MINUTES
11111     5/12/2008       5/12/2008 2:55:50 PM M     2
11112     5/12/2008       5/12/2008 2:50:52 PM M    89
11714     5/13/2008       5/13/2008 1:15:32 PM M    28

Answer 3

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

Answer 4

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

Answer 5

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;