# How can I find Minimum and Maximum clock time

• ### Question

• I have a table  with the following structure

```Create table EmployeeClocktbl
(EmpCode varchar 10,ClockDate DateTime)```

The table has the following record

```Empcode	ClockDate
1	10/03/2020 08:28
1	10/03/2020 09:28
1	10/03/2020 12:28
1	10/03/2020 17:28
2	10/03/2020 08:20
2	10/03/2020 14:20
2	10/03/2020 17:20```

I am looking for the Sql to get Max time and Min time of  the given employee and for the given date. If no record exist in the clocking table, then the time should  be as '00:00' Please help. I am looking some thing like  format given below from the given record above

```Declare @Empcode varchar(10)
Declare @Date Datetime
set @EmpCode = '10';
set @Date = '03-10-2020'
,ISNULL((SELECT  <Mintime> FROM  EmployeeClocktbl clock  WHERE  clock.EmpCode = Employee.EmployeeID and clock.ClockDate =  @Date),'00:00')  MinTime
,ISNULL((SELECT  <Maxtime> FROM  EmployeeClocktbl clock  WHERE  clock.EmpCode = Employee.EmployeeID and clock.ClockDate =  @Date),'00:00')  MinTime```

polachan

• Edited by Thursday, March 19, 2020 11:03 AM correction
Thursday, March 19, 2020 11:02 AM

• I am looking for the time part only  not date for example

Empcode  1 and for the date 10-03-2020 Min time 08:28  and Max time  17:28 . If not record exist the time should be shown '00:00'

polachan

• Edited by Thursday, March 19, 2020 11:34 AM
• Marked as answer by Friday, March 20, 2020 3:41 PM
Thursday, March 19, 2020 11:34 AM

• Marked as answer by Friday, March 20, 2020 3:42 PM
Thursday, March 19, 2020 3:26 PM

### All replies

• select cast(max(ClockDate) AS DATE) MAXDT,CAST(MIN(ClockDate)  AS DATE) mindt

from EmployeeClock clock JOIN Employee ON clock.EmpCode = Employee.EmployeeID

where empid=@par AND CAST(clock.ClockDate AS DATE) = CAST(@Date AS DATE)

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Thursday, March 19, 2020 11:08 AM
• I am looking for the time part only  not date for example

Empcode  1 and for the date 10-03-2020 Min time 08:28  and Max time  17:28 . If not record exist the time should be shown '00:00'

polachan

• Edited by Thursday, March 19, 2020 11:34 AM
• Marked as answer by Friday, March 20, 2020 3:41 PM
Thursday, March 19, 2020 11:34 AM
• select cast(max(ClockDate) AS TIME) MAXDT,CAST(MIN(ClockDate)  AS TIME) mindt

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Thursday, March 19, 2020 11:36 AM

how can can I format as '08:20' and '18:00'. If no record it should be '00:00'. for example if I give the  where condition

where empcode =2 and ClockDate = '03-14-2020'  no record exist that case the mindate and max date should be '00:00'

polachan

Thursday, March 19, 2020 11:55 AM
• DECLARE @Sample TABLE
(
tranid int,
response varchar(20),
tran_date datetime
);

INSERT @Sample
VALUES (111, 'REQUEST', CONVERT(DATETIME, '31-10-18 10:34:09', 5)),
(111, 'RESPONSE', CONVERT(DATETIME, '31-10-18 10:35:09', 5)),
(333, 'REQUEST', CONVERT(DATETIME, '31-10-18 11:35:09', 5)),
(333, 'RESPONSE', CONVERT(DATETIME, '31-10-18 11:35:15', 5)),
(222, 'REQUEST', CONVERT(DATETIME, '31-10-18 12:59:09', 5)),
(222, 'RESPONSE', CONVERT(DATETIME, '31-10-18 13:02:12', 5)),
(444, 'RESPONSE', NULL);

SELECT TranID,
COALESCE(CONVERT(CHAR(5),  CAST(MIN(Tran_Date)AS TIME)),'00:00'),
COALESCE(CONVERT(CHAR(5),  CAST(MAX(Tran_Date)AS TIME)),'00:00')
FROM @Sample
GROUP BY TranID;

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Thursday, March 19, 2020 11:59 AM
• It is not working

for example  the employee 3  clocked in  10/03/2020 08:20  , there is no  other rows on the table , Then the min time and max   time should not be same . Min should be 08:20 and Max '00:00. The max time only should be taken if there is more than one rows

```Empcide    ClockedDate
3	10/03/2020 08:20```

polachan

Thursday, March 19, 2020 1:56 PM

```Create table EmployeeClocktbl
(EmpCode varchar(10),ClockDate DateTime)
insert into EmployeeClocktbl values
(1,'10/03/2020 08:28')
,(1,'10/03/2020 09:28')
,(1,'10/03/2020 12:28')
,(1,'10/03/2020 17:28')
,(2,'10/03/2020 08:20')
,(2,'10/03/2020 14:20')
,(2,'10/03/2020 17:20')

declare @dt date='10/04/2020'
Declare @Empcode varchar(10)='2'

;with emp as
(select distinct EmpCode from EmployeeClocktbl)

,aggTab as (
Select EmpCode, min(ClockDate)  min_ClockDate,
max(ClockDate) max_ClockDate
from EmployeeClocktbl
Where
cast(ClockDate as date)=@dt
group by EmpCode

)

select e.EmpCode, Cast(isnull(min_ClockDate,'1900-01-01') as time(0)) as mintime,
Cast(isnull(max_ClockDate,'1900-01-01') as time(0)) as maxtime
from emp e left join aggTab a  on e.EmpCode=a.EmpCode
where e.EmpCode=@EmpCode

drop table EmployeeClocktbl```

Thursday, March 19, 2020 1:56 PM
• it is not working when there  is only one row against employee , then the  min time shows as max time too. That cases the max time should be '00:00'

polachan

Thursday, March 19, 2020 1:57 PM
• ```Create table EmployeeClocktbl
(EmpCode varchar(10),ClockDate DateTime)
insert into EmployeeClocktbl values
(1,'10/03/2020 08:28')
,(1,'10/03/2020 09:28')
,(1,'10/03/2020 12:28')
,(1,'10/03/2020 17:28')
,(2,'10/03/2020 08:20')
,(2,'10/03/2020 14:20')
,(2,'10/04/2020 17:20')

declare @dt date='10/05/2020'
Declare @Empcode varchar(10)='2'

;with emp as
(select distinct EmpCode from EmployeeClocktbl)

,aggTab as (
Select EmpCode, min(ClockDate)  min_ClockDate,
max(ClockDate) max_ClockDate,count(*) cnt
from EmployeeClocktbl
Where
cast(ClockDate as date)=@dt
group by EmpCode

)

select e.EmpCode, Cast(isnull(min_ClockDate,'1900-01-01') as time(0)) as mintime,
Cast(isnull(Case When cnt>1 then max_ClockDate else null end,'1900-01-01')  as time(0)) as maxtime
from emp e left join aggTab a  on e.EmpCode=a.EmpCode
where e.EmpCode=@EmpCode

drop table EmployeeClocktbl```

Thursday, March 19, 2020 2:05 PM
• I am looking for something simple way to get Min and max time in different rows . How can I get  the simillar sql something like given below

select   CAST(MIN(ClockDate)  AS TIME) mindt
from EmployeeClock clock JOIN Employee ON clock.EmpCode = Employee.EmployeeID
where empid=@par AND CAST(clock.ClockDate AS DATE) = CAST(@Date AS DATE)

select cast(max(ClockDate) AS DATE) MAXDT
from EmployeeClock clock JOIN Employee ON clock.EmpCode = Employee.EmployeeID
where empid=@par AND CAST(clock.ClockDate AS DATE) = CAST(@Date AS DATE) and cast(max(ClockDate) AS DATE) <>  CAST(MIN(ClockDate)

polachan

• Edited by Thursday, March 19, 2020 3:05 PM
Thursday, March 19, 2020 2:47 PM

• Marked as answer by Friday, March 20, 2020 3:42 PM
Thursday, March 19, 2020 3:26 PM
• Hi polachan,

```create table #clock(EmpCode varchar(10),ClockDate DateTime)
insert #clock  values
(1,'10/03/2020 08:28')
,(1,'10/03/2020 09:28')
,(1,'10/03/2020 12:28')
,(1,'10/03/2020 17:28')
,(2,'10/03/2020 08:20')
,(2,'10/03/2020 14:20')
,(2,'10/03/2020 17:20')
,(3,'10/03/2020 08:20')

;with cte1 as(
select EmpCode ,cast(min(ClockDate) as time(0)) mintime,cast(max(ClockDate) as time(0)) maxtime,
count(EmpCode) cnt
from #clock
group by EmpCode
)
select EmpCode, mintime ,case when cnt <>1 then maxtime else '00:00' end as maxtime
from cte1

/*
EmpCode	mintime	maxtime
1	08:28:00	17:28:00
2	08:20:00	17:20:00
3	08:20:00	00:00:00
*/```

Best Regards,

Lily

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

Friday, March 20, 2020 3:17 AM