How to select the minimum date, EmployeeID and BranchID per day

Question

It's been a while since I wrote "complex" queries in SQL...

I have a table Attendance with columns

BranchID (int)
SignDate (DateTime) 
EmployeeID (nvarchar)

Each Branch has tens of employees.

What I need is a list the earliest SignDate for each branch per day and EmployeeID who signed.

I tried several ways, but no luck...

I'm using SQL Server.

Thanks.

You said you've tried several ways... what have you tried? Is the problem the time component of the `SignDate`? If so then `cast(SignDate as date)` — allmhuran, Aug 31 '21 at 20:05
What happens if two rows have the same `SignDate` and `BranchId` values but different `EmployeeId` values? — Dai, Aug 31 '21 at 20:08

Aaron Bertrand · Accepted Answer · 2021-08-31T20:09:39.003

3

;WITH src AS 
(
  SELECT BranchID, EmployeeID, SignDate,
    rn = ROW_NUMBER() OVER (
      PARTITION BY BranchID, CONVERT(date, SignDate) 
      ORDER BY SignDate, EmployeeID -- break ties
    )
  FROM dbo.Attendance
  -- some kind of WHERE maybe
)
SELECT BranchID, EmployeeID, SignDate
  FROM src
  WHERE rn = 1
  ORDER BY BranchID, SignDate;

How to select the minimum date, EmployeeID and BranchID per day

1 Answers1