how to check difference in time

Question

following code returns

CREATE TABLE #emp1 
  ( 
     empid   VARCHAR(50), 
     empname VARCHAR(50), 
     intime  SMALLDATETIME, 
     outtime SMALLDATETIME 
  ) 

INSERT INTO #emp1 
VALUES     (2500, 
            'Arun', 
            '2014-01-01 09:00:00', 
            '2014-01-01 10:30:0'), 
            (2500, 
             'Arun', 
             '2014-01-01 11:00:00:00', 
             '2014-01-01 11:00:0'), 
            (2500, 
             'Arun', 
             '2014-01-01 11:30:00:00', 
             '2014-01-01 19:00:00') 

SELECT empid, 
       Cast(intime AS DATE) 
       AS workingday, 
       empname, 
       RIGHT('0' + CONVERT(VARCHAR, Floor(Sum( Datediff( mi, intime, outtime)/ 
       60.0))), 
       2) 
       + ':' 
       + RIGHT('0' + CONVERT(VARCHAR, Sum( Datediff( mi, intime, outtime))%60), 
       2) AS 
       [total work_hour(s)], 
       CONVERT(VARCHAR(5), Max(outtime) - Min(intime), 108) 
       AS Difference, 
       Cast(Dateadd(minute, Sum(Datediff(mi, intime, outtime)) - 45, 0) AS TIME) 
       AS 
       TotalTime, 
       Cast(Dateadd(minute, CASE 
                              WHEN Sum(Datediff(mi, intime, outtime)) > 525 THEN 
                              Sum( 
                              Datediff(mi, intime, outtime)) - 525 
                              ELSE 0 
                            END, 0) AS TIME) 
       AS OverTime 
FROM   #emp1 
GROUP  BY empid, 
          empname, 
          Cast(intime AS DATE) 

DROP TABLE #emp1 
as



EmpId  workingday  EmpName  total work_hour(s)  Difference  TotalTime         OverTime
2500   2014-01-01  Arun     09:00               10:00       08:15:00.0000000  00:15:00.0000000

Here TotalTime is 08:15 and OverTime is 00:15. Everything is fine.

However, I need another field ActualTime

i.e. if TotalTime is greater than 8 hours then ActualTime is the difference of TotalTime and OverTime. In this example ActualTime should be 08:00.

If TotalTime is less than 8 hours then ActualTime is the same as TotalTime.

Finally, I also want to avoid excess zeros in TotalTime and OverTime

Answer 1

I imagine you want something like this: *EDIT nullcheck added

;with x as
(
SELECT empid, 
       Cast(intime AS DATE) workingday, 
       empname, 
       cast(dateadd(mi, sum(datediff(mi, 0, outtime - intime)), 0) as time) twh, 
       cast(dateadd(mi,datediff(mi,0,max(outtime)-min(intime)), 0) as time) Difference,
       case when count(outtime)=count(*) then '' end nullcheck
FROM #emp1 
GROUP BY empid, empname, Cast(intime AS DATE) 
)
select empid, workingday, empname, 
nullcheck+left(twh, 5) [total_hours], 
nullcheck+left(Difference, 5) Difference, 
nullcheck+left(cast(dateadd(mi, -45, twh)as time ),5) TotalTime,
nullcheck+left(case when twh > '08:45' then cast(dateadd(mi, -525, twh) as time) else '00:00' end, 5) OverTime,
nullcheck+left(case when twh > '08:00' then '08:00' else twh end, 5) actualtime
from x

Result:

empid workingday empname total_hours Difference TotalTime OverTime actualtime
2500  2014-01-01 Arun    09:00       10:00      08:15     00:15    08:00

Answer 2

First I'd wrap your entire current query in a Common Table Expression, so that the parts of it can be reused.

WITH CalculatedTime AS (SELECT empid, 
       Cast(intime AS DATE) AS workingday, 
       empname, 
       RIGHT('0' + CONVERT(VARCHAR, Floor(Sum( Datediff( mi, intime, outtime)/ 
       60.0))), 
       2) 
       + ':' 
       + RIGHT('0' + CONVERT(VARCHAR, Sum( Datediff( mi, intime, outtime))%60), 
       2) AS [total work_hour(s)], 
       CONVERT(VARCHAR(5), Max(outtime) - Min(intime), 108) 
       AS Difference, 
       Cast(Dateadd(minute, Sum(Datediff(mi, intime, outtime)) - 45, 0) AS TIME) 
       TotalTime, 
       Cast(Dateadd(minute, CASE 
                              WHEN Sum(Datediff(mi, intime, outtime)) > 525 THEN 
                              Sum( 
                              Datediff(mi, intime, outtime)) - 525 
                              ELSE 0 
                            END, 0) AS TIME) 
       OverTime 
FROM   #emp1 
GROUP  BY empid, 
          empname, 
          Cast(intime AS DATE) )

Now we can refer to CalculatedTime as a table in itself, and we don't need to repeat any of that nasty logic used for calculating the TotalTime column.

So the final step is to use a case statement to perform the calculation you are after:

SELECT *,
    CASE WHEN DATEDIFF(minute,TotalTime,'08:00') > 0 THEN TotalTime
    ELSE DATEADD(minute, DATEDIFF(minute,OverTime, 0), TotalTime)
    END AS ActualTime
FROM CalculatedTime

The formatting part of the problem (remove the extra zeros) is a separate question that has been answered many times before on StackOverflow.