SQL join limiting to 1 row where time is oldest for date

Question

I have 2 tables that I'm joining; I want all rows from employee table to retrieve latest time for a date in transactions table; the transaction table can have multiple records for employee on same date. Right now, cannot find way to limit transactions to last record (latest time entry)

This is an access table; Adodb query from excel to access.

SELECT E.ID, E.Employee, E.lastname, T.in, T.out, T.date, T.type, T.EmployeeID FROM employee E RIGHT JOIN transactions T ON E.ID = INT(T.EmployeeID) WHERE " & _
    "T.date = #" & Format(yesterdate, "Short Date") & "# AND T.type = 'JobCode5' AND E.location = 'EastBuilding' AND E.ID = 89 AND E.stat = TRUE

I expect to get 1 transaction per applicable employee; preferably the transaction with the latest time on given date.

Possible duplicate of [SQL select only rows with max value on a column](https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) — Ryan Wilson, Jun 04 '19 at 16:11

Gordon Linoff · Answer 1 · 2019-06-06T16:04:47.217

0

You can use a correlated subquery to get the latest transaction. There is a bit of a trick in MS Access to handle employees with no transactions:

SELECT E.ID, E.Employee, E.lastname,
       T.in, T.out, T.date, T.type, T.EmployeeID 
FROM employee as E LEFT JOIN
     transactions as T
     ON E.ID = INT(T.EmployeeID) 
WHERE (T.out = (SELECT MAX(t2.out)
                FROM transactions as T2
                WHERE T2.EmployeeID = T.EmployeeID AND
                      T2.Date = T.Date AND
                      T.type = 'JobCode5'
               ) OR
       T.date IS NULL
      ) AND
      <your filters here>

edited Jun 06 '19 at 16:04

answered Jun 04 '19 at 16:12

Gordon Linoff

1,242,037
58
646
786

has to be on specified date; looking for oldest time on specified date. This example finds oldest date for all employees – raylward102 Jun 04 '19 at 16:38
@raylward102 . . . This finds the oldest time for *each* employee. If you want only one transaction per employee/date combination, you need a column that contains the date. – Gordon Linoff Jun 04 '19 at 16:44
t.date = date value; t.out = time value. There can be 2-3 t.out times for specified date; want to capture greatest t.out – raylward102 Jun 04 '19 at 16:51
@raylward102 . . . Presumably, you want this per employee. If not, remove the correlation condition on employee. – Gordon Linoff Jun 04 '19 at 16:56
Basically want this query to return all active employees for a location and a transaction record for today's date and the latest time out. if there is no transaction for todays date, I still want the employee and am ok with null out times. Basically its an attendance record for today showing 1 row per employee – raylward102 Jun 04 '19 at 17:28
Hi Gordon; Didn't realize you had updated the above query. I've tested and is almost exactly what I want!! I'm hoping to get-include a row for active, location employee for specified date, even if they didn't have a transaction for specified date – raylward102 Jun 04 '19 at 21:24
@raylward102 . . . This should return employees with no transaction on the date. – Gordon Linoff Jun 04 '19 at 21:43
Running your exact query, results in 19000+ records; I only have 34 active employees at that location. After adding my filters to your query, I get it down to 20 records, but missing records for employees who are absent for filtered date. This query is so close to what I need; any chance we can get the last puzzle piece? My filters are the ones posted in the first example: type, location, date, stat. End result has to be 34 records total – raylward102 Jun 06 '19 at 16:00
I tried that; made no difference. Your previous example and this one, aren't making rows for transactions that are null (dont exist for specified date). Example: I run this against yesterdays data and receive 20 records (14 records missing because those employees had no transactions) . When I run that query against today's date, I receive 0 records, because the query isn't returning null records for specified date (no out times for today becuase today isn't over). I should be getting 34 records back every time, if I have 34 active employees at said location. This is an attendance record – raylward102 Jun 06 '19 at 16:23

score 0 · Answer 2 · answered Jun 06 '19 at 17:31

Subquery does the job but runs rather slow

SELECT E.employee, E.lastname, (SELECT TOP 1 IIF(ISNULL([in]), '', [in]) FROM [transactions] WHERE [date] = #6/3/2019# AND [type] = 'JobCode5' AND INT([EmployeeID]) = E.ID ORDER BY transactions.ID DESC) AS IN_TIME, " & _
        "(SELECT TOP 1 IIF(ISNULL([out]), '', [out]) FROM [transactions] WHERE [date] = #6/3/2019# AND [type] = 'JobCode5' AND INT([EmployeeID]) = E.ID ORDER BY transactions.ID DESC) AS AS_TIME FROM employee E WHERE E.stat = True AND E.location = 'EastBuilding'

SQL join limiting to 1 row where time is oldest for date

2 Answers2