none
Time doesn't appear in proper format

    Question

  • Successfully calculated TotalTime a person has worked and overtime he has done if total working time is greater than 08:00 but if not greater than it puts duration of time person has not worked in Overtime column BUT NOW overtime column should display that time in  proper negative format e.g. if person has worked from 14:49 to 15:20 hours than Overtime column should show -00:29 but my column is displaying it like this 23:29, why 23 ?

    CODE

    with times as (
    SELECT    t1.EmplID
            , t3.EmplName
            , min(t1.RecTime) AS InTime
            , max(t2.RecTime) AS [TimeOut]
            , cast(min(t1.RecTime) as datetime) AS InTimeSub
            , cast(max(t2.RecTime) as datetime) AS TimeOutSub
            , t1.RecDate AS [DateVisited]
    FROM  AtdRecord t1 
    INNER JOIN 
          AtdRecord t2 
    ON    t1.EmplID = t2.EmplID 
    AND   t1.RecDate = t2.RecDate
    AND   t1.RecTime < t2.RecTime
    inner join 
          HrEmployee t3 
    ON    t3.EmplID = t1.EmplID 
    group by 
              t1.EmplID
            , t3.EmplName
            , t1.RecDate
    )
    SELECT EmplID
    ,EmplName
    ,InTime
    ,[TimeOut]
    ,[DateVisited]
    ,convert(char(5),cast([TimeOutSub] - InTimeSub as time), 108) totaltime
    ,convert(char(5), case when TimeOutSub - InTimeSub >= '08:01' then 
    cast(TimeOutSub - dateadd(hour, 8, InTimeSub) as time) else cast(8 - (TimeOutSub - InTimeSub) as time) end, 108) as overtime
    FROM times 





    • Edited by Evil Poster Thursday, January 23, 2014 10:27 AM
    • Moved by Papy Normand Thursday, January 23, 2014 11:56 AM Coming from SQL Server Data Access : related to the createion of TRANSACT-SQL statements
    Thursday, January 23, 2014 10:03 AM

All replies

  • Issue: I have successfully calculated TotalTime a person has worked and overtime he has done if total working time is greater than 08:00 but if not greater than it puts 00:00 in Overtime for that employee BUT NOW I DON'T WANT THIS, I WANT THAT IF PERSON HAS WORKED LESS THAN 8 hours than overtime column should display that time in negative e.g. if person has worked 7:00 hours than Overtime column should show -1 (in proper hours format)

    CODE:

    with times as (
    SELECT    t1.EmplID
            , t3.EmplName
            , min(t1.RecTime) AS InTime
            , max(t2.RecTime) AS [TimeOut]
            , cast(min(t1.RecTime) as datetime) AS InTimeSub
            , cast(max(t2.RecTime) as datetime) AS TimeOutSub
            , t1.RecDate AS [DateVisited]
    FROM  AtdRecord t1 
    INNER JOIN 
          AtdRecord t2 
    ON    t1.EmplID = t2.EmplID 
    AND   t1.RecDate = t2.RecDate
    AND   t1.RecTime < t2.RecTime
    inner join 
          HrEmployee t3 
    ON    t3.EmplID = t1.EmplID 
    group by 
              t1.EmplID
            , t3.EmplName
            , t1.RecDate
    )
    SELECT EmplID
    ,EmplName
    ,InTime
    ,[TimeOut]
    ,[DateVisited]
    ,convert(char(5),cast([TimeOutSub] - InTimeSub as time), 108) totaltime
    ,convert(char(5), case when TimeOutSub - InTimeSub >= '08:01' then 
    cast(TimeOutSub - dateadd(hour, 8, InTimeSub) as time) else '00:00' end, 108) as overtime
    FROM times

    Thursday, January 23, 2014 7:07 AM
  • Hello ?

    Thursday, January 23, 2014 7:56 AM
  • else cast(datediff(hh,timeoutsub,dateadd(hour, 8, InTimeSub)) as time) end, 108) as overtime
    
    

    try this as a replacement for else part ?? (sorry couldnt test)

    above part will give you the time ->convert it to varchar->prefix a '-'

    logic is:

    (outtime-(intime+8))*-1


    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>

    Thursday, January 23, 2014 8:15 AM
  • As i understand what you need to do is just to remove the last case when and make it like this

    with times as (
    SELECT    t1.EmplID
            , t3.EmplName
            , min(t1.RecTime) AS InTime
            , max(t2.RecTime) AS [TimeOut]
            , cast(min(t1.RecTime) as datetime) AS InTimeSub
            , cast(max(t2.RecTime) as datetime) AS TimeOutSub
            , t1.RecDate AS [DateVisited]
    FROM  AtdRecord t1 
    INNER JOIN 
          AtdRecord t2 
    ON    t1.EmplID = t2.EmplID 
    AND   t1.RecDate = t2.RecDate
    AND   t1.RecTime < t2.RecTime
    inner join 
          HrEmployee t3 
    ON    t3.EmplID = t1.EmplID 
    group by 
              t1.EmplID
            , t3.EmplName
            , t1.RecDate
    )
    SELECT EmplID
    ,EmplName
    ,InTime
    ,[TimeOut]
    ,[DateVisited]
    ,convert(char(5),cast([TimeOutSub] - InTimeSub as time), 108) totaltime
    ,convert(char(5),
    cast(TimeOutSub - dateadd(hour, 8, InTimeSub) as time) , 108) as overtime
    FROM times


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Thursday, January 23, 2014 8:16 AM
  • As I am not sure of your table structure and data, will not be able to suggest anything in your code.

    However, you can have a look at the below snippet as reference.

    create Table T1(TimeoutSub time, TimeInSub time)
    
    Insert into T1 Select '18:00','08:00'
    Insert into T1 Select '18:00','11:00'
    
    Select *, 
    Case when DATEDIFF(HOUR,Cast(Dateadd(hour,8,TimeInSub) as time),TimeoutSub)>0 Then 
    	DATEDIFF(HOUR,Cast(Dateadd(hour,8,TimeInSub) as time),TimeoutSub)
    Else 0 End as 'OverTime',
    Case when DATEDIFF(HOUR,Cast(Dateadd(hour,8,TimeInSub) as time),TimeoutSub)<0 Then 
    	DATEDIFF(HOUR,Cast(Dateadd(hour,8,TimeInSub) as time),TimeoutSub)
    Else 0 End as 'ShortTime'
    From T1
    
    Drop table T1

    Thursday, January 23, 2014 9:23 AM
  • now it shows negative sign with value but values are wrong, e.g. -23:29
    or -18:01 for a person whose total time is 05:59
    actually it shows that second value correctly after : but first is false, e.g. -23:29 should be -00:29 , -18:01 should be -00:01

    Thursday, January 23, 2014 9:42 AM

  • ,convert(char(5),cast([TimeOutSub] - InTimeSub as time), 108) totaltime
    ,convert(char(5), case when TimeOutSub - InTimeSub >= '08:01' then 
    cast(TimeOutSub - dateadd(hour, 8, InTimeSub) as time) else cast(8 - (TimeOutSub - InTimeSub) as time) end, 108) as overtime
    FROM times


    Could you remove the cast to time and see the output?

    Satheesh
    My Blog


    Thursday, January 23, 2014 10:28 AM
  • same result
    • Edited by Evil Poster Thursday, January 23, 2014 10:42 AM
    Thursday, January 23, 2014 10:41 AM
  • A Simple example
    declare @person table
    (empid int, swipein datetime, swipeout datetime) ;
    insert into @person values
    (1,getdate(),dateadd(MINUTE,500,getdate())),
    (1,getdate(),dateadd(MINUTE,450,getdate())),
    (1,getdate(),dateadd(MINUTE,480,getdate()));
    with cte as (select empid,swipein,swipeout,datediff(minute,swipein,swipeout) Diffminutes from @person	)
    select *,case when 60*8>Diffminutes then '-' + convert(varchar(10),abs(Diffminutes-60*8) /60 )+':'+convert(varchar(10),abs(Diffminutes-60*8) %60)
    			when 60*8<=Diffminutes then convert(varchar(10),abs(Diffminutes-60*8) /60 )+':'+convert(varchar(10),abs(Diffminutes-60*8) %60)
    		 end from cte


    Satheesh
    My Blog


    Thursday, January 23, 2014 10:50 AM
  • great but how to correct my code sir ?

    Thursday, January 23, 2014 11:30 AM
  • Hello ,

    This thread is not related to SQL Server Data Access , so I am moving it towards a more suitable forum where I hope it will be more quickly answered ( and where it will be more useful and interesting for other persons meeting this kind of problems ).

    Have a nice day


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

    Thursday, January 23, 2014 11:53 AM
  • yes please move

    Thursday, January 23, 2014 12:05 PM
  • try this, not tested
    with times as (
    SELECT    t1.EmplID
            , t3.EmplName
            , min(t1.RecTime) AS InTime
            , max(t2.RecTime) AS [TimeOut]
            , cast(min(t1.RecTime) as datetime) AS InTimeSub
            , cast(max(t2.RecTime) as datetime) AS TimeOutSub
            , t1.RecDate AS [DateVisited]
    FROM  AtdRecord t1 
    INNER JOIN 
          AtdRecord t2 
    ON    t1.EmplID = t2.EmplID 
    AND   t1.RecDate = t2.RecDate
    AND   t1.RecTime < t2.RecTime
    inner join 
          HrEmployee t3 
    ON    t3.EmplID = t1.EmplID 
    group by 
              t1.EmplID
            , t3.EmplName
            , t1.RecDate
    ),
    CTE_FINAL AS(
    SELECT EmplID
    ,EmplName
    ,InTime
    ,[TimeOut]
    ,[DateVisited]
    ,datediff(minute,InTimeSub,TimeOutSub) Diffminutes
    FROM times )
    
    select *,
    	case when 60*8>Diffminutes then 
    		'-' + convert(varchar(10),abs(Diffminutes-60*8) /60 )+':'+convert(varchar(10),abs(Diffminutes-60*8) %60)
    		when 60*8<=Diffminutes then convert(varchar(10),abs(Diffminutes-60*8) /60 )+':'+convert(varchar(10),abs(Diffminutes-60*8) %60)
    	end from cte


    Satheesh
    My Blog


    Thursday, January 23, 2014 12:16 PM