locked
How can I find Minimum and Maximum clock time RRS feed

  • 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
    Please help


    polachan


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

Answers

  • 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 polachan Thursday, March 19, 2020 11:34 AM
    • Marked as answer by polachan Friday, March 20, 2020 3:41 PM
    Thursday, March 19, 2020 11:34 AM
  • What is your expected result from your sample tables?

    Please make it clear. Thanks.

    • Marked as answer by polachan 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
    Answerer
  • 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 polachan Thursday, March 19, 2020 11:34 AM
    • Marked as answer by polachan 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
    Answerer
  • Sorry for disturbing, I am looking some bit  addition to your advised sql

    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
    Answerer
  • 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
  • Please check your DDL script to make it work.(make some efforts please)

    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 polachan Thursday, March 19, 2020 3:05 PM
    Thursday, March 19, 2020 2:47 PM
  • What is your expected result from your sample tables?

    Please make it clear. Thanks.

    • Marked as answer by polachan 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