How can I get the min and max date per date of employees?

Question

This is my table looked like:

Employee_Number       |       DateTime
00000001              |       2014/01/14  09:20
00000001              |       2014/01/14  12:35
00000001              |       2014/01/14  13:35
00000002              |       2014/01/14  09:20
00000001              |       2014/01/14  22:49
00000001              |       2014/01/15  09:35
00000001              |       2014/01/15  10:35
00000001              |       2014/01/15  17:35
00000002              |       2014/01/14  12:34
00000002              |       2014/01/14  17:34

I want to do a select statement where I will get the min and the max datetime of employee per day something like this:

Employee_Number       |       DateTime             MIN                  MAX
00000001              |       2014/01/14      2014/01/14  09:20    2014/01/14 22:49
00000001              |       2014/01/15      2014/01/15  09:35    2014/01/15 17:35
00000002              |       2014/01/14      2014/01/14  09:20    2014/01/14  17:34

I already searched google to find answer to my dilemma but the very near sql statement that I can have is this:

declare @tmp table (
tranDate int,
tranTime datetime

                   )

 insert into @tmp
 select Convert(int, convert(nvarchar(100), DateTime,112)) ,DateTime from tblExtract

 select tranDate, min(tranTime) as 'min' , max(tranTime) as 'max' from @tmp
 group by tranDate

The problem is it only shows the min and max of the day not per employee_number. How can I solve this?

Try grouping with "Employee_Number" – Bharadwaj Mar 25 '14 at 02:20 — Bharadwaj, Mar 25 '14 at 02:20
@Bharadwaj I already do it but It returns an error. – Lucas Juan Mar 25 '14 at 02:22 — Lucas Juan, Mar 25 '14 at 02:22
You don't have "Employee_Number" in the tmp table. – Bharadwaj Mar 25 '14 at 02:24 — Bharadwaj, Mar 25 '14 at 02:24

score 2 · Answer 1 · answered Mar 25 '14 at 02:23

2

try this, assuming that the DateTime column is not stored as a string

select Employee_Number, Cast([DateTime] as Date) as 'DateTime', MIN([DateTime]) as 'MIN', MAX([DateTime]) as 'MAX' 
from Employee_Table
group by Employee_Number, Cast([DateTime] as Date)

answered Mar 25 '14 at 02:23

Leo

14,625
2
37
55

Yup, I guess it's the simplest way to do it without having to create a temp table – Leo Mar 25 '14 at 02:47

score 1 · Answer 2 · answered Mar 25 '14 at 02:23

1

Select DateAdd(d, 0, DateDiff(d, 0, DateTime)) tranDate, Employee_Number, min(DateTime), max(DateTime)
From tblExtract
Group By 
DateAdd(d, 0, DateDiff(d, 0, DateTime)), Employee_Number

answered Mar 25 '14 at 02:23

Ken

336
1
6

A_V · Answer 3 · 2014-03-25T13:52:23.700

1

In MSSQL:

 select 
    emloyee_id,
    convert(date,datetime) as Date_time
    min(datetime) as Min_date,
    max(datetime) as Max_date  
 from tblEmployee
 group by emloyee_id,convert(date,datetime)

group by based on employee_id and datetime (extracting only date part) will give us one row per combination of employee_id and date ,then we can select min and max of each group

edited Mar 25 '14 at 13:52

answered Mar 25 '14 at 03:19

A_V

11
2

score 0 · Accepted Answer · answered Mar 25 '14 at 02:25

First of all, you must add Employee_Number to the tmp table.

Next, In the last statement, you must write

group by tranDate, Employee_Number

The reason is that you will get the minimum corresponding to each tranDate and Employee_Number pair, as required.

How can I get the min and max date per date of employees?

4 Answers4

Linked