Getting Wrong Total output?

Question

Example

I want to calculate the worktime between 09:00:00 to 18:00:00,

09:00:00 - Normal_Intime
18:00:00 - Normal_Outtime

Suppose

1) Intime is 10:00:00, Outtime is 17:00:00 It should give the output as 07:00:00
2) Intime is 08:00:00. Outtime is 19:00:00 It should give the output as 09:00:00

above output should compare with Normal_Intime, Normal_Outtime .

Means Normal_Intime is 09:00:00 only and Normal_Output is 18:00:00 only, so Intime should not > 09:00:00 and Outtime < 18:00:00.

Query

select Intime, Outtime, CONVERT(char(8), CASE WHEN OutTime > Normal_Outtime THEN cast(Normal_Outtime AS datetime) ELSE cast(OutTime AS datetime) END - CASE WHEN InTime > Normal_Intime THEN cast(InTime AS datetime) ELSE cast(Normal_Intime AS datetime) END, 8) AS WorkTime from table

Output

Intime       Outtime        Worktime
15:37:48    22:08:15 02:22:12
07:33:34    07:59:13 22:59:13

the Second Row giving wrong output It should give only 00:25:39

If both in and out time is smaller than NormalInTime and both in and out time is greater than NormalOutTime. How to make a condtion for this

How to make a query for this conditon.

Need Query Help.

Answer 1

Have you tried this one:

SELECT CONVERT(varchar, Outtime - Intime, 108) FROM YourTable

Outtime - Intime will give you a timespan that you want and convert with 108 parameter will give you just hours, minutes and seconds from that timespan more info about convert

Answer 2

This should get you what you want

DECLARE @Table TABLE(
     InTime DATETIME,
     OutTime DATETIME
)

INSERT INTO @Table (InTime,OutTime) SELECT '15:37:48', '22:08:15'
INSERT INTO @Table (InTime,OutTime) SELECT '07:33:34', '07:59:13'

DECLARE @Normal_InTime DATETIME,
     @Normal_OutTime DATETIME

SELECT  @Normal_InTime = '09:00:00',
     @Normal_OutTime = '18:00:00'

SELECT  *,
     --both dates are smaller than NotmalInTime
     CASE
      WHEN InTime < @Normal_InTime AND OutTime < @Normal_InTime THEN OutTime - InTime
      WHEN OutTime > @Normal_OutTime AND InTime > @Normal_OutTime THEN OutTime - InTime
      WHEN OutTime > @Normal_OutTime 
       THEN @Normal_OutTime - InTime
      WHEN InTime < @Normal_InTime
       THEN OutTime - @Normal_InTime
      ELSE OutTime - InTime
     END
FROM    @Table

But you need to take into account. What happens when the user books in before 24:00 and then books out the next day.

And as i said, what happens in the event that book in and bookout is either before NormalTimeIn or after NormalTimeOut?

you need to specify this.

Answer 3

YOU SHOULD CONSIDER THE DATE ALSO WHILE CALCULATING THE TIME.

declare 
@Normal_Intime datetime,
@Normal_Outtime datetime,
@Actual_Intime datetime,
@Actual_Outtime datetime
set @Normal_Intime = cast('09:00:00' as datetime)
set @Normal_Outtime =cast('18:00:00' as datetime) 

-- CASE 1
set @Actual_Outtime = cast('17:00:00' as datetime)  
set @Actual_Intime = cast('10:00:00' as datetime)  

select case when @Actual_Intime > @Normal_Intime then @Actual_Intime else @Normal_Intime end -
case when @Actual_Outtime < @Normal_Outtime then @Actual_Outtime else @Normal_Outtime end

-- CASE 2    

set @Actual_Outtime = cast('19:00:00' as datetime)    
set @Actual_Intime = cast('08:00:00' as datetime)    

select case when @Actual_Intime > @Normal_Intime then @Actual_Intime else @Normal_Intime end -
case when @Actual_Outtime < @Normal_Outtime then @Actual_Outtime else @Normal_Outtime end

-- CASE 3  

set @Actual_Outtime = cast('07:59:13' as datetime)    
set @Actual_Intime = cast('07:33:34' as datetime)    

select case when @Actual_Intime > @Normal_Intime then @Actual_Intime else @Normal_Intime end -
case when @Actual_Outtime < @Normal_Outtime then @Actual_Outtime else @Normal_Outtime end