To get Total Hour & Late Hour from Time In, Time Out, 1st Break & 2nd Break

Transact-SQL
1

Hi All,

Basically I have Time_In, Time_Out, 1st Break Hour & 2nd Break Hour. I want to get the Output :

  1. Total Hour after minus 1st Break & 2nd Break
  2. Total Late Hour which fall under 22:00:00 to 05:00:00

Below it's the sql statement which I have include last 2 column output desire. How can I handle using MS SQL Statement?

select Time_In='0:00:00',Time_Out='19:00:00',Break1st='2:00:00',Break2nd1st='9:00:00',Break2nd='12:00:00',Break2_2nd='13:00:00',OutPutTotalHr='11:00:00',OutPutLateHour='2:00:00' union all
select Time_In='0:01:00',Time_Out='18:00:00',Break1st='4:30:00',Break2nd1st='9:00:00',Break2nd='NULL',Break2_2nd='NULL',OutPutTotalHr='13:29:00',OutPutLateHour='4:29:00' union all
select Time_In='7:00:00',Time_Out='20:30:00',Break1st='11:30:00',Break2nd1st='12:00:00',Break2nd='17:00:00',Break2_2nd='18:00:00',OutPutTotalHr='12:00:00',OutPutLateHour='' union all
select Time_In='8:30:00',Time_Out='0:00:00',Break1st='13:00:00',Break2nd1st='13:10:00',Break2nd='17:45:00',Break2_2nd='21:00:00',OutPutTotalHr='12:05:00',OutPutLateHour='2:00:00' union all
select Time_In='8:30:00',Time_Out='1:00:00',Break1st='17:30:00',Break2nd1st='21:00:00',Break2nd='23:00:00',Break2_2nd='23:30:00',OutPutTotalHr='12:30:00',OutPutLateHour='2:30:00' union all
select Time_In='9:00:00',Time_Out='0:00:00',Break1st='11:30:00',Break2nd1st='12:30:00',Break2nd='17:30:00',Break2_2nd='20:30:00',OutPutTotalHr='11:00:00',OutPutLateHour='2:00:00' union all
select Time_In='9:00:00',Time_Out='22:30:00',Break1st='13:00:00',Break2nd1st='15:00:00',Break2nd='17:30:00',Break2_2nd='21:00:00',OutPutTotalHr='8:00:00',OutPutLateHour='0:30:00' union all
select Time_In='9:20:00',Time_Out='0:00:00',Break1st='12:30:00',Break2nd1st='13:30:00',Break2nd='18:00:00',Break2_2nd='21:50:00',OutPutTotalHr='9:50:00',OutPutLateHour='2:00:00' union all
select Time_In='0:00:00',Time_Out='22:30:00',Break1st='2:00:00',Break2nd1st='9:00:00',Break2nd='12:00:00',Break2_2nd='13:00:00',OutPutTotalHr='14:30:00',OutPutLateHour='4:30:00'

The idea after I do manipulation of Time_In & Time_Out manually with date assign yesterday as example:-
select Time_In_Maniuplation='2020-09-17 00:00:00',Time_Out_Manipulation='2020-09-17 19:00:00' union all
select Time_In_Maniuplation='2020-09-17 00:01:00',Time_Out_Manipulation='2020-09-17 18:00:00' union all
select Time_In_Maniuplation='2020-09-17 07:00:00',Time_Out_Manipulation='2020-09-17 20:30:00' union all
select Time_In_Maniuplation='2020-09-17 08:30:00',Time_Out_Manipulation='2020-09-18 00:00:00' union all
select Time_In_Maniuplation='2020-09-17 08:30:00',Time_Out_Manipulation='2020-09-18 01:00:00' union all
select Time_In_Maniuplation='2020-09-17 09:00:00',Time_Out_Manipulation='2020-09-18 00:00:00' union all
select Time_In_Maniuplation='2020-09-17 09:20:00',Time_Out_Manipulation='2020-09-18 00:00:00' union all
select Time_In_Maniuplation='2020-09-17 00:00:00',Time_Out_Manipulation='2020-09-17 19:00:00'

Please advise,

Regards,
MIcheale

2

I manage to get Total Hour, but not Late Hour:-
declare @tblTesting table
(
Time_In time(0),
Time_Out time(0),
Break1st time(0),
Break2nd1st time(0),
Break2nd time(0),
Break2_2nd time(0),
OutPutTotalHr time(0),
OutPutLateHour time(0)
)
insert into @tblTesting
(Time_In,Time_Out,Break1st,Break2nd1st,Break2nd,Break2_2nd,OutPutTotalHr,OutPutLateHour)
select Time_In='00:00:00',Time_Out='19:00:00',Break1st='02:00:00',Break2nd1st='09:00:00',Break2nd='12:00:00',Break2_2nd='13:00:00',OutPutTotalHr='11:00:00',OutPutLateHour='02:00:00' union all
select Time_In='00:01:00',Time_Out='18:00:00',Break1st='04:30:00',Break2nd1st='09:00:00',Break2nd=NULL,Break2_2nd=NULL,OutPutTotalHr='13:29:00',OutPutLateHour='04:29:00' union all
select Time_In='07:00:00',Time_Out='20:30:00',Break1st='11:30:00',Break2nd1st='12:00:00',Break2nd='17:00:00',Break2_2nd='18:00:00',OutPutTotalHr='12:00:00',OutPutLateHour='' union all
select Time_In='08:30:00',Time_Out='00:00:00',Break1st='13:00:00',Break2nd1st='13:10:00',Break2nd='17:45:00',Break2_2nd='21:00:00',OutPutTotalHr='12:05:00',OutPutLateHour='02:00:00' union all
select Time_In='08:30:00',Time_Out='01:00:00',Break1st='17:30:00',Break2nd1st='21:00:00',Break2nd='23:00:00',Break2_2nd='23:30:00',OutPutTotalHr='12:30:00',OutPutLateHour='02:30:00' union all
select Time_In='09:00:00',Time_Out='00:00:00',Break1st='11:30:00',Break2nd1st='12:30:00',Break2nd='17:30:00',Break2_2nd='20:30:00',OutPutTotalHr='11:00:00',OutPutLateHour='02:00:00' union all
select Time_In='09:00:00',Time_Out='22:30:00',Break1st='13:00:00',Break2nd1st='15:00:00',Break2nd='17:30:00',Break2_2nd='21:00:00',OutPutTotalHr='8:00:00',OutPutLateHour='00:30:00' union all
select Time_In='09:20:00',Time_Out='00:00:00',Break1st='12:30:00',Break2nd1st='13:30:00',Break2nd='18:00:00',Break2_2nd='21:50:00',OutPutTotalHr='9:50:00',OutPutLateHour='02:00:00' union all
select Time_In='00:00:00',Time_Out='22:30:00',Break1st='02:00:00',Break2nd1st='09:00:00',Break2nd='12:00:00',Break2_2nd='13:00:00',OutPutTotalHr='14:30:00',OutPutLateHour='04:30:00'

select
[Time_In],
[Time_Out],
TotalHour=CONVERT(char(8),DATEADD(minute,DATEDIFF(MINUTE,[Time_In],[Time_Out])-(case when [Break1st] is not null and [Break2nd1st] is not null then DATEDIFF(MINUTE,[Break1st],[Break2nd1st]) else 0 end) - (case when [Break2nd] is not null and [Break2_2nd] is not null then DATEDIFF(MINUTE,[Break2nd],[Break2_2nd]) else 0 end),0),114),
LateHour=(case when [Time_In] between CONVERT(char(10), GetDate(),126)+' 22:00:00' and CONVERT(char(10), GetDate()+1,126)+' 05:00:00' and [Break1st] between CONVERT(char(10), GetDate(),126)+' 22:00:00' and CONVERT(char(10), GetDate()+1,126)+' 05:00:00' then
CONVERT(char(8),DATEADD(minute,DATEDIFF(MINUTE,[Time_In],[Break1st]),0),114)
else
null
end)

from
(
select
[Time_In]=CONVERT(char(10), GetDate(),126)+' '+CONVERT(varchar,[Time_In],8),
[Time_Out]= CONVERT(char(10), DatePrefix,126)+' '+CONVERT(varchar,[Time_Out],8),
[Break1st]=CONVERT(char(10), GetDate(),126)+' '+CONVERT(varchar,[Break1st],8),
[Break2nd1st]=CONVERT(char(10), GetDate(),126)+' '+CONVERT(varchar,[Break2nd1st],8),
[Break2nd]=CONVERT(char(10), GetDate(),126)+' '+CONVERT(varchar,[Break2nd],8),
[Break2_2nd]=CONVERT(char(10), GetDate(),126)+' '+CONVERT(varchar,[Break2_2nd],8)
from (
select
[Time_In],
[Time_Out],
DatePrefix =case when [Time_Out]<[Time_In] then
getDate()
else
DateAdd(day,1,getDate())
end ,
[Break1st],
[Break2nd1st],
[Break2nd],
[Break2_2nd]
from @tblTesting
)A
)B

3

hi

what does this mean ??

  1. Total Late Hour which fall under 22:00:00 to 05:00:00

Looks really easy to do ..

Please explain

OR

we can do it together !!
I can remote desktop to your computer and we can chat on the phone at the same time !!
and resolve it quickly !!

4

Thanks Haris for the offer.

I manage to do it up to this stage:-
declare @tblTesting table
(
Time_In time(0),
Time_Out time(0),
Break1st time(0),
Break2nd1st time(0),
Break2nd time(0),
Break2_2nd time(0),
OutPutTotalHr time(0),
OutPutLateHour time(0)
)
insert into @tblTesting
(Time_In,Time_Out,Break1st,Break2nd1st,Break2nd,Break2_2nd,OutPutTotalHr,OutPutLateHour)
select Time_In='07:00:00',Time_Out='20:30:00',Break1st='11:30:00',Break2nd1st='12:00:00',Break2nd='17:00:00',Break2_2nd='18:00:00',OutPutTotalHr='12:00:00',OutPutLateHour='' union all
select Time_In='08:30:00',Time_Out='00:00:00',Break1st='13:00:00',Break2nd1st='13:10:00',Break2nd='17:45:00',Break2_2nd='21:00:00',OutPutTotalHr='12:05:00',OutPutLateHour='02:00:00' union all
select Time_In='08:30:00',Time_Out='01:00:00',Break1st='17:30:00',Break2nd1st='21:00:00',Break2nd='23:00:00',Break2_2nd='23:30:00',OutPutTotalHr='12:30:00',OutPutLateHour='02:30:00' union all
select Time_In='09:00:00',Time_Out='00:00:00',Break1st='11:30:00',Break2nd1st='12:30:00',Break2nd='17:30:00',Break2_2nd='20:30:00',OutPutTotalHr='11:00:00',OutPutLateHour='02:00:00' union all
select Time_In='09:00:00',Time_Out='22:30:00',Break1st='13:00:00',Break2nd1st='15:00:00',Break2nd='17:30:00',Break2_2nd='21:00:00',OutPutTotalHr='8:00:00',OutPutLateHour='00:30:00' union all
select Time_In='09:20:00',Time_Out='00:00:00',Break1st='12:30:00',Break2nd1st='13:30:00',Break2nd='18:00:00',Break2_2nd='21:50:00',OutPutTotalHr='9:50:00',OutPutLateHour='02:00:00' union all
select Time_In='00:00:00',Time_Out='22:30:00',Break1st='02:00:00',Break2nd1st='09:00:00',Break2nd='12:00:00',Break2_2nd='13:00:00',OutPutTotalHr='14:30:00',OutPutLateHour='02:30:00' union all
select Time_In='00:00:00',Time_Out='19:00:00',Break1st='02:00:00',Break2nd1st='09:00:00',Break2nd='12:00:00',Break2_2nd='13:00:00',OutPutTotalHr='11:00:00',OutPutLateHour='02:00:00' union all
select Time_In='00:01:00',Time_Out='18:00:00',Break1st='04:30:00',Break2nd1st='09:00:00',Break2nd=NULL,Break2_2nd=NULL,OutPutTotalHr='13:29:00',OutPutLateHour='04:29:00'

select
[Time_In],
[Time_Out],
TotalHour,
LateHour=CONVERT(varchar,dateadd(second,datediff(second,0,LateHourI),LateHourII),8)
from(
select
[Time_In],
[Time_Out],
TotalHour=CONVERT(char(8),DATEADD(minute,DATEDIFF(MINUTE,[Time_In],[Time_Out])-(case when [Break1st] is not null and [Break2nd1st] is not null then DATEDIFF(MINUTE,[Break1st],[Break2nd1st]) else 0 end) - (case when [Break2nd] is not null and [Break2_2nd] is not null then DATEDIFF(MINUTE,[Break2nd],[Break2_2nd]) else 0 end),0),114),
LateHourI=(case when [Time_In] between CONVERT(char(10), GetDate(),126)+' 00:00:00' and CONVERT(char(10), GetDate(),126)+' 05:00:00' and [Break1st] between CONVERT(char(10), GetDate(),126)+' 00:00:00' and CONVERT(char(10), GetDate(),126)+' 05:00:00' then
CONVERT(char(8),DATEADD(minute,DATEDIFF(MINUTE,[Time_In],[Break1st]),0),114)
when [Time_Out] between CONVERT(char(10), GetDate(),126)+' 00:00:00' and CONVERT(char(10), GetDate(),126)+' 05:00:00' and Break2_2nd < CONVERT(char(10), GetDate(),126)+' 22:00:00' then
CONVERT(char(8),DATEADD(minute,DATEDIFF(MINUTE,CONVERT(char(10), [Time_Out],126)+' 22:00:00',[Time_Out]),0),114)
when [Time_Out] between CONVERT(char(10), GetDate(),126)+' 00:00:00' and CONVERT(char(10), GetDate(),126)+' 05:00:00' and Break2_2nd > CONVERT(char(10), GetDate(),126)+' 22:00:00' then
CONVERT(char(8),DATEADD(minute,DATEDIFF(MINUTE,Break2_2nd,[Time_Out]),0),114)
else
'00:00:00'
end),
LateHourII=(case when [Time_Out] between CONVERT(char(10), [Time_Out],126)+' 22:00:00' and CONVERT(char(10), DateAdd(day,1,[Time_Out]),126)+' 00:00:00' and Break2_2nd between CONVERT(char(10), [Time_Out],126)+' 22:00:00' and CONVERT(char(10), DateAdd(day,1,[Time_Out]),126)+' 00:00:00' then
CONVERT(char(8),DATEADD(minute,DATEDIFF(MINUTE,[Time_Out],[Break2_2nd]),0),114)
when [Time_Out] between CONVERT(char(10), [Time_Out],126)+' 22:00:00' and CONVERT(char(10), DateAdd(day,1,[Time_Out]),126)+' 00:00:00' and Break2_2nd < CONVERT(char(10), [Time_Out],126)+' 22:00:00' then
CONVERT(char(8),DATEADD(minute,DATEDIFF(MINUTE,CONVERT(char(10), [Time_Out],126)+' 22:00:00',[Time_Out]),0),114)
when Break2nd1st < CONVERT(char(10), [Time_Out],126)+' 22:00:00' and Break2nd > CONVERT(char(10), [Time_Out],126)+' 22:00:00' then
CONVERT(char(8),DATEADD(minute,DATEDIFF(MINUTE,CONVERT(char(10), [Time_Out],126)+' 22:00:00',Break2nd),0),114)
when Break2nd1st > CONVERT(char(10), [Time_Out],126)+' 22:00:00' and Break2nd > CONVERT(char(10), [Time_Out],126)+' 22:00:00' then
CONVERT(char(8),DATEADD(minute,DATEDIFF(MINUTE,Break2nd1st,Break2nd),0),114)
else
'00:00:00'
end)
from
(
select
[Time_In]=CONVERT(char(10), GetDate(),126)+' '+CONVERT(varchar,[Time_In],8),
[Time_Out]= CONVERT(char(10), DatePrefix,126)+' '+CONVERT(varchar,[Time_Out],8),
[Break1st]=CONVERT(char(10), GetDate(),126)+' '+CONVERT(varchar,[Break1st],8),
[Break2nd1st]=CONVERT(char(10), GetDate(),126)+' '+CONVERT(varchar,[Break2nd1st],8),
[Break2nd]=CONVERT(char(10), GetDate(),126)+' '+CONVERT(varchar,[Break2nd],8),
[Break2_2nd]=CONVERT(char(10), GetDate(),126)+' '+CONVERT(varchar,[Break2_2nd],8)
from (
select
[Time_In],
[Time_Out],
DatePrefix =case when [Time_Out]<[Time_In] then
getDate()
else
DateAdd(day,1,getDate())
end ,
[Break1st],
[Break2nd1st],
[Break2nd],
[Break2_2nd]
from @tblTesting
)A
)B
)C

I do numbers of nested sql & I not sure is that the possibilities rules of Late Hour applied.

Example Explanation:-
select Time_In='08:30:00',Time_Out='01:00:00',Break1st='17:30:00',Break2nd1st='21:00:00',Break2nd='23:00:00',Break2_2nd='23:30:00',OutPutTotalHr='12:30:00',OutPutLateHour='02:30:00'

This person work from 08:30:00 till 01:00:00, but took 2 break. 1st break from 17:30:00 to 21:00:00 & 2nd break 23:00:00 to 23:30:00
Mean, 22:00:00 to 23:00:00 - Work 1 Hour
23:30:00 to 01:00:00 - work 1 Hour 30 minutes
Total Late Hour: 02:30:00

Basically this is the Punch Card Working Hour Calculation. If the person work from 22:00:00 to 05:00:00, he/she entitled late hour claims. SO, I need to generate the Total Late Hour. There is 2 break need to take into consideration. If the break fall under 22:00:00 to 05:00:00, we need to minus out. Hope my explanation make clear to you.

How can I make the sql more robust. My sql statement too many nested sql.

Thanks.

Regards,
Micheale

6

hi

please see my attempt ..

image
image1103×83 4.14 KB

image

please click arrow to the left for SQL 
; with 
    time_consider as 
( 
	select '22:00' as start_time , '5:00' as end_time , time_in , time_out  from #abc 
) , 
    breaks as 
(
	select Break1st , Break2nd1st from #abc 
		union all 
	select Break2nd , Break2_2nd from #abc 
), 
    breaks_we_want as 
(
	select * from breaks a , time_consider b where a.Break1st > b.start_time and a.Break2nd1st < b.end_time
) , 
   we_got_the_break_time as 
(
	select sum(datediff(minute,cast(Break1st as datetime),cast(Break2nd1st as datetime)))  as sum_break_minutes from breaks_we_want
) , 
    now_OutputLateHour as 
(
	select 
	      cast(start_time as datetime) as startime , 
		  case when dateadd(dd,1,cast(time_out as datetime)) < dateadd(dd,1,cast(end_time as datetime))  then dateadd(dd,1,cast(time_out as datetime)) else dateadd(dd,1,cast(end_time as datetime))   end as endtime 
    from 
	    time_consider
)
select 
       (datediff(minute,a.startime,a.endtime) - b.sum_break_minutes) as InMinutes_OutputLateHour, CAST( CAST(((datediff(minute,a.startime,a.endtime) - b.sum_break_minutes)) AS int) / 60 AS varchar) + ':'  + right('0' + CAST(CAST(((datediff(minute,a.startime,a.endtime) - b.sum_break_minutes)) AS int) % 60 AS varchar(2)),2) as OutputLateHour 
from 
    now_OutputLateHour a , we_got_the_break_time b 
go
please click arrow to the left for Sample Data Create Script 1 row 
drop table #abc 

select  
Time_In='08:30:00',Time_Out='01:00:00',Break1st='17:30:00',Break2nd1st='21:00:00',Break2nd='23:00:00',Break2_2nd='23:30:00',OutPutTotalHr='12:30:00',OutPutLateHour='02:30:00' into #abc 

select 'data ' , * from #abc
7

hi

if we do SQL for one pattern .. if data pattern changes .. then SQL might or might not work

image

8

Hi,

Same days, this is 24 hour format. Also, depend on break time. If no break time, mean fall on same days.

Thanks.

Regards,
Micheale

9

Hi,

I manage to done the SQL Statement. But, my way writing with many nested loops. How can I do it better using your method? Please advise.

declare @tblTesting table
(
Time_In time(0),
Time_Out time(0),
Break1st time(0),
Break2nd1st time(0),
Break2nd time(0),
Break2_2nd time(0),
OutPutTotalHr time(0),
OutPutLateHour time(0)
)
insert into @tblTesting
(Time_In,Time_Out,Break1st,Break2nd1st,Break2nd,Break2_2nd,OutPutTotalHr,OutPutLateHour)
select Time_In='07:00:00',Time_Out='20:30:00',Break1st='11:30:00',Break2nd1st='12:00:00',Break2nd='17:00:00',Break2_2nd='18:00:00',OutPutTotalHr='12:00:00',OutPutLateHour='' union all
select Time_In='08:30:00',Time_Out='00:00:00',Break1st='13:00:00',Break2nd1st='13:10:00',Break2nd='17:45:00',Break2_2nd='21:00:00',OutPutTotalHr='12:05:00',OutPutLateHour='02:00:00' union all
select Time_In='08:30:00',Time_Out='01:00:00',Break1st='17:30:00',Break2nd1st='21:00:00',Break2nd='23:00:00',Break2_2nd='23:30:00',OutPutTotalHr='12:30:00',OutPutLateHour='02:30:00' union all
select Time_In='09:00:00',Time_Out='00:00:00',Break1st='11:30:00',Break2nd1st='12:30:00',Break2nd='17:30:00',Break2_2nd='20:30:00',OutPutTotalHr='11:00:00',OutPutLateHour='02:00:00' union all
select Time_In='09:00:00',Time_Out='22:30:00',Break1st='13:00:00',Break2nd1st='15:00:00',Break2nd='17:30:00',Break2_2nd='21:00:00',OutPutTotalHr='8:00:00',OutPutLateHour='00:30:00' union all
select Time_In='09:20:00',Time_Out='00:00:00',Break1st='12:30:00',Break2nd1st='13:30:00',Break2nd='18:00:00',Break2_2nd='21:50:00',OutPutTotalHr='9:50:00',OutPutLateHour='02:00:00' union all
select Time_In='00:00:00',Time_Out='22:30:00',Break1st='02:00:00',Break2nd1st='09:00:00',Break2nd='12:00:00',Break2_2nd='13:00:00',OutPutTotalHr='14:30:00',OutPutLateHour='02:30:00' union all
select Time_In='00:00:00',Time_Out='19:00:00',Break1st='02:00:00',Break2nd1st='09:00:00',Break2nd='12:00:00',Break2_2nd='13:00:00',OutPutTotalHr='11:00:00',OutPutLateHour='02:00:00' union all
select Time_In='00:01:00',Time_Out='18:00:00',Break1st='04:30:00',Break2nd1st='09:00:00',Break2nd=NULL,Break2_2nd=NULL,OutPutTotalHr='13:29:00',OutPutLateHour='04:29:00'


select 
	[Time_In],
	[Time_Out],
	[StartBreakHr1stO],
	[EndBreakHr1stO],
	[StartBreakHrO],
	[EndBreakHrO],
	[OutPutTotalHr],
	TotalHour=CONVERT(varchar,DATEADD(minute,0,TotalHour)-DATEADD(minute,0,MinusBreakNull),8),
	[OutPutLateHour],
	LateHour
from
(
	select
	[Time_In],
	[Time_Out],
	[StartBreakHr1stO],
	[EndBreakHr1stO],
	[StartBreakHrO],
	[EndBreakHrO],
	[OutPutTotalHr],
	TotalHour,
	MinusBreakNull = (case when [StartBreakHr1stO] is null and [EndBreakHr1stO] is null then 
					case when TotalHour between '04:00:00' and '06:00:00' then
						'00:45:00'
					when TotalHour > '06:00:00' then
						'01:00:00'
					else
						'00:00:00'
					end 
				else 
					'00:00:00'
				end),
	[OutPutLateHour],
	LateHour=CONVERT(varchar,LateHourI,8)
	from(
	select
	[Time_In],
	[Time_Out],
	[StartBreakHr1stO],
	[EndBreakHr1stO],
	[StartBreakHrO],
	[EndBreakHrO],
	[OutPutTotalHr],
	[OutPutLateHour],
	TotalHour=CONVERT(char(8),DATEADD(minute,DATEDIFF(MINUTE,[Time_In],[Time_Out])-(case when [Break1st] is not null and [Break2nd1st] is not null then DATEDIFF(MINUTE,[Break1st],[Break2nd1st]) else 0 end) - (case when [Break2nd] is not null and [Break2_2nd] is not null then DATEDIFF(MINUTE,[Break2nd],[Break2_2nd]) else 0 end),0),114),
	LateHourI=(case 
				when [Time_In] between CONVERT(char(10), GetDate(),126)+' 00:00:00' and CONVERT(char(10), GetDate(),126)+' 05:00:00' and [Break1st] between CONVERT(char(10), GetDate(),126)+' 00:00:00' and CONVERT(char(10), GetDate(),126)+' 05:00:00' then
					case when [Time_Out] between CONVERT(char(10), [Time_In],126)+' 22:00:00' and CONVERT(char(10), [Time_In],126)+' 23:59:59' then
					CONVERT(char(8),DATEADD(minute, DATEDIFF(MINUTE,CONVERT(char(10), [Time_In],126)+' 22:00:00' ,[Time_Out]),114)
					+DATEADD(minute,DATEDIFF(MINUTE,[Time_In],[Break1st]),0),114)
					else
					CONVERT(char(8),DATEADD(minute,DATEDIFF(MINUTE,[Time_In],[Break1st]),0),114)	
					end	
				when [Time_Out] between CONVERT(char(10), [Time_In],126)+' 22:00:00' and CONVERT(char(10), [Time_In],126)+' 23:59:59' and [Break1st] is not null and [Break2nd1st] is not null and [Break2nd] is null and [Break2_2nd] is null and [Break2nd1st] >= CONVERT(char(10), [Time_In],126)+' 22:00:00'  then
					CONVERT(char(8),DATEADD(minute,DATEDIFF(MINUTE,[Break2nd1st],[Time_Out]),0),114)
				when [Break1st] is not null and [Break2nd1st] is not null and [Break2nd] is null and [Break2_2nd] is null and [Time_Out]>CONVERT(char(10), [Time_Out],126)+' 22:00:00' and  [Break2nd1st] < CONVERT(char(10), [Time_In],126)+' 22:00:00' then
					CONVERT(char(8),DATEADD(minute,DATEDIFF(MINUTE,CONVERT(char(10), [Time_Out],126)+' 22:00:00',[Time_Out]),0),114)
				when [Break1st] is not null and [Break2nd1st] is not null and [Break2nd] is null and [Break2_2nd] is null and [Time_Out] between CONVERT(char(10), [Time_Out],126)+' 00:00:00' and CONVERT(char(10), [Time_Out],126)+' 05:00:00' and  [Break2nd1st] < CONVERT(char(10), [Time_Out],126)+' 22:00:00' then
					DateAdd(minute, 0,CONVERT(char(8),DATEADD(minute,DATEDIFF(MINUTE,CONVERT(char(10), [Time_Out],126)+' 00:00:00',[Time_Out]),0),114))+
					DateAdd(minute, 0,CONVERT(char(8),DATEADD(minute,DATEDIFF(MINUTE,CONVERT(char(10), [Time_Out],126)+' 22:00:00',CONVERT(char(10), [Time_Out],126)+' 00:00:00'),0),114))
				when [Break2_2nd] between  CONVERT(char(10), [Break2_2nd],126)+' 00:00:00' and CONVERT(char(10), [Break2_2nd],126)+' 05:00:00' and [Time_Out] between  CONVERT(char(10), [Time_Out],126)+' 00:00:00' and CONVERT(char(10), [Time_Out],126)+' 05:00:00' then
					CONVERT(char(8),DATEADD(minute,DATEDIFF(MINUTE,DateAdd(Day,1,[Break2_2nd]),[Time_Out]),0),114)
				when [Break2_2nd] <  CONVERT(char(10), [Break2_2nd],126)+' 22:00:00' and [Time_Out] > CONVERT(char(10), [Time_In],126)+' 22:00:00' then
					DATEADD(minute, DATEDIFF(MINUTE,CONVERT(char(10), [Time_In],126)+' 22:00:00' ,[Time_Out]),114)
				when [Break2_2nd] is null and [Break2nd1st] is null  and [Time_Out] > CONVERT(char(10), [Time_In],126)+' 22:00:00' then
					DATEADD(minute, DATEDIFF(MINUTE,CONVERT(char(10), [Time_In],126)+' 22:00:00' ,[Time_Out]),114)
				when [Break2_2nd] >= CONVERT(char(10), [Time_In],126)+' 22:00:00' and [Time_Out]>CONVERT(char(10), [Time_Out],126)+' 22:00:00' then
					DATEADD(minute, DATEDIFF(MINUTE,[Break2_2nd] ,[Time_Out]),114)
				when [Break2_2nd] is not null and [Time_Out] between CONVERT(char(10), [Time_In],126)+' 22:00:00' and CONVERT(char(10), [Time_Out],126)+' 05:00:00' and [Break2_2nd] >= CONVERT(char(10), [Time_In],126)+' 22:00:00'  then
					
					case when [Break2nd1st]is not null and [Break2nd1st]<CONVERT(char(10), [Time_In],126)+' 22:00:00' and [Break2_2nd] between CONVERT(char(10), [Time_In],126)+' 22:00:00' and CONVERT(char(10), [Time_In],126)+' 23:59:59' then
						CONVERT(char(8),
						DATEADD(minute,DATEDIFF(MINUTE,CONVERT(char(10), [Break2_2nd],126)+' 22:00:00',[Break2nd]),0)
						+
						DATEADD(minute,DATEDIFF(MINUTE,[Break2_2nd],[Time_Out]),0),114)
					else
						CONVERT(char(8),
						DATEADD(minute,DATEDIFF(MINUTE,[Break2nd],CONVERT(char(10), [Time_Out],126)+' 00:00:00'),0)
						+
						DATEADD(minute,DATEDIFF(MINUTE,CONVERT(char(10), [Time_Out],126)+' 00:00:00',[Time_Out]),0)
						,114)
					end
				
				else
					'00:00:00'
				end)
	from
	(
			select
			[OutPutLateHour],
			[OutPutTotalHr],
			[StartBreakHr1stO],
			[EndBreakHr1stO],
			[StartBreakHrO],
			[EndBreakHrO],
			[Time_In]=CONVERT(char(10), GetDate(),126)+' '+CONVERT(varchar,[Time_In],8),
			[Time_Out]= CONVERT(char(10), DatePrefix,126)+' '+CONVERT(varchar,[Time_Out],8),
			[Break1st]=CONVERT(char(10), GetDate(),126)+' '+CONVERT(varchar,[Break1st],8),
			[Break2nd1st]=CONVERT(char(10), GetDate(),126)+' '+CONVERT(varchar,[Break2nd1st],8),
			[Break2nd]=CONVERT(char(10), GetDate(),126)+' '+CONVERT(varchar,[Break2nd],8),
			[Break2_2nd]=CONVERT(char(10), GetDate(),126)+' '+CONVERT(varchar,[Break2_2nd],8)
			from (
				select
				[OutPutTotalHr],
				[Time_In],
				[Time_Out],
				[StartBreakHr1stO]=[Break1st],
				[EndBreakHr1stO]=[Break2nd1st],
				[StartBreakHrO]=[Break2nd],
				[EndBreakHrO]=[Break2_2nd],
				DatePrefix =case when [Time_Out]>[Time_In] then
					getDate()
				else
					DateAdd(day,1,getDate())
				end ,
				[Break1st],
				[Break2nd1st],
				[Break2nd],
				[Break2_2nd],
				[OutPutLateHour]
				from @tblTesting
				where [OutPutLateHour] is not null
				)A
			)B
	)C
	where CONVERT(varchar,LateHourI,8)<>[OutPutLateHour]
)D

My LateHourI maybe can be beautify by you, I am using case when ... else ... end to handle the possibilities:-

 LateHourI=(case 
    				when [Time_In] between CONVERT(char(10), GetDate(),126)+' 00:00:00' and CONVERT(char(10), GetDate(),126)+' 05:00:00' and [Break1st] between CONVERT(char(10), GetDate(),126)+' 00:00:00' and CONVERT(char(10), GetDate(),126)+' 05:00:00' then
    					case when [Time_Out] between CONVERT(char(10), [Time_In],126)+' 22:00:00' and CONVERT(char(10), [Time_In],126)+' 23:59:59' then
    					CONVERT(char(8),DATEADD(minute, DATEDIFF(MINUTE,CONVERT(char(10), [Time_In],126)+' 22:00:00' ,[Time_Out]),114)
    					+DATEADD(minute,DATEDIFF(MINUTE,[Time_In],[Break1st]),0),114)
    					else
    					CONVERT(char(8),DATEADD(minute,DATEDIFF(MINUTE,[Time_In],[Break1st]),0),114)	
    					end	
    				when [Time_Out] between CONVERT(char(10), [Time_In],126)+' 22:00:00' and CONVERT(char(10), [Time_In],126)+' 23:59:59' and [Break1st] is not null and [Break2nd1st] is not null and [Break2nd] is null and [Break2_2nd] is null and [Break2nd1st] >= CONVERT(char(10), [Time_In],126)+' 22:00:00'  then
    					CONVERT(char(8),DATEADD(minute,DATEDIFF(MINUTE,[Break2nd1st],[Time_Out]),0),114)
    				when [Break1st] is not null and [Break2nd1st] is not null and [Break2nd] is null and [Break2_2nd] is null and [Time_Out]>CONVERT(char(10), [Time_Out],126)+' 22:00:00' and  [Break2nd1st] < CONVERT(char(10), [Time_In],126)+' 22:00:00' then
    					CONVERT(char(8),DATEADD(minute,DATEDIFF(MINUTE,CONVERT(char(10), [Time_Out],126)+' 22:00:00',[Time_Out]),0),114)
    				when [Break1st] is not null and [Break2nd1st] is not null and [Break2nd] is null and [Break2_2nd] is null and [Time_Out] between CONVERT(char(10), [Time_Out],126)+' 00:00:00' and CONVERT(char(10), [Time_Out],126)+' 05:00:00' and  [Break2nd1st] < CONVERT(char(10), [Time_Out],126)+' 22:00:00' then
    					DateAdd(minute, 0,CONVERT(char(8),DATEADD(minute,DATEDIFF(MINUTE,CONVERT(char(10), [Time_Out],126)+' 00:00:00',[Time_Out]),0),114))+
    					DateAdd(minute, 0,CONVERT(char(8),DATEADD(minute,DATEDIFF(MINUTE,CONVERT(char(10), [Time_Out],126)+' 22:00:00',CONVERT(char(10), [Time_Out],126)+' 00:00:00'),0),114))
    				when [Break2_2nd] between  CONVERT(char(10), [Break2_2nd],126)+' 00:00:00' and CONVERT(char(10), [Break2_2nd],126)+' 05:00:00' and [Time_Out] between  CONVERT(char(10), [Time_Out],126)+' 00:00:00' and CONVERT(char(10), [Time_Out],126)+' 05:00:00' then
    					CONVERT(char(8),DATEADD(minute,DATEDIFF(MINUTE,DateAdd(Day,1,[Break2_2nd]),[Time_Out]),0),114)
    				when [Break2_2nd] <  CONVERT(char(10), [Break2_2nd],126)+' 22:00:00' and [Time_Out] > CONVERT(char(10), [Time_In],126)+' 22:00:00' then
    					DATEADD(minute, DATEDIFF(MINUTE,CONVERT(char(10), [Time_In],126)+' 22:00:00' ,[Time_Out]),114)
    				when [Break2_2nd] is null and [Break2nd1st] is null  and [Time_Out] > CONVERT(char(10), [Time_In],126)+' 22:00:00' then
    					DATEADD(minute, DATEDIFF(MINUTE,CONVERT(char(10), [Time_In],126)+' 22:00:00' ,[Time_Out]),114)
    				when [Break2_2nd] >= CONVERT(char(10), [Time_In],126)+' 22:00:00' and [Time_Out]>CONVERT(char(10), [Time_Out],126)+' 22:00:00' then
    					DATEADD(minute, DATEDIFF(MINUTE,[Break2_2nd] ,[Time_Out]),114)
    				when [Break2_2nd] is not null and [Time_Out] between CONVERT(char(10), [Time_In],126)+' 22:00:00' and CONVERT(char(10), [Time_Out],126)+' 05:00:00' and [Break2_2nd] >= CONVERT(char(10), [Time_In],126)+' 22:00:00'  then
    					
    					case when [Break2nd1st]is not null and [Break2nd1st]<CONVERT(char(10), [Time_In],126)+' 22:00:00' and [Break2_2nd] between CONVERT(char(10), [Time_In],126)+' 22:00:00' and CONVERT(char(10), [Time_In],126)+' 23:59:59' then
    						CONVERT(char(8),
    						DATEADD(minute,DATEDIFF(MINUTE,CONVERT(char(10), [Break2_2nd],126)+' 22:00:00',[Break2nd]),0)
    						+
    						DATEADD(minute,DATEDIFF(MINUTE,[Break2_2nd],[Time_Out]),0),114)
    					else
    						CONVERT(char(8),
    						DATEADD(minute,DATEDIFF(MINUTE,[Break2nd],CONVERT(char(10), [Time_Out],126)+' 00:00:00'),0)
    						+
    						DATEADD(minute,DATEDIFF(MINUTE,CONVERT(char(10), [Time_Out],126)+' 00:00:00',[Time_Out]),0)
    						,114)
    					end
    				
    				else
    					'00:00:00'
    				end)

Thanks.

Reagrds,
Micheale

10

hi

please see my SQL ..see how i am doing it .. hope this helps
other wise better to have a discussion .. will be over in 10 minutes
instead of back and forth many times

; with 
    time_consider as 
( 
	select '22:00' as start_time , '5:00' as end_time , time_in , time_out  from #abc 
) , 
    breaks as 
(
	select Break1st , Break2nd1st from #abc 
		union all 
	select Break2nd , Break2_2nd from #abc 
), 
    breaks_we_want as 
(
	select * from breaks a , time_consider b where a.Break1st > b.start_time and a.Break2nd1st < b.end_time
) , 
   we_got_the_break_time as 
(
	select sum(datediff(minute,cast(Break1st as datetime),cast(Break2nd1st as datetime)))  as sum_break_minutes from breaks_we_want
) , 
    now_OutputLateHour as 
(
	select 
	      cast(start_time as datetime) as startime , 
		  case when dateadd(dd,1,cast(time_out as datetime)) < dateadd(dd,1,cast(end_time as datetime))  then dateadd(dd,1,cast(time_out as datetime)) else dateadd(dd,1,cast(end_time as datetime))   end as endtime 
    from 
	    time_consider
)
select 
       (datediff(minute,a.startime,a.endtime) - b.sum_break_minutes) as InMinutes_OutputLateHour, CAST( CAST(((datediff(minute,a.startime,a.endtime) - b.sum_break_minutes)) AS int) / 60 AS varchar) + ':'  + right('0' + CAST(CAST(((datediff(minute,a.startime,a.endtime) - b.sum_break_minutes)) AS int) % 60 AS varchar(2)),2) as OutputLateHour 
from 
    now_OutputLateHour a , we_got_the_break_time b 
go
1 Like
11

hi Micheale

no thoughts from your side :curious

12

Many Thanks. Issue Resolved. Refer to your solution. I able to figure out the solution.

13

Sorry Haris,

I have manipulate your solution into this:-
drop table #abc

select * into #abc  from (
select ID=1,start_time='22:00', end_time='5:00',Time_In='00:01:00',Time_Out='18:00:00',Break1st='04:30:00',Break2nd1st='09:00:00',Break2nd=NULL,Break2_2nd=NULL,OutPutTotalHr='13:29:00',OutPutLateHour='04:29:00' union all 
select ID=2,start_time='22:00', end_time='5:00',Time_In='09:00:00',Time_Out='22:30:00',Break1st='13:00:00',Break2nd1st='15:00:00',Break2nd='17:30:00',Break2_2nd='21:00:00',OutPutTotalHr='8:00:00',OutPutLateHour='00:30:00' union all 
select ID=3,start_time='22:00', end_time='5:00',Time_In='00:00:00',Time_Out='22:30:00',Break1st='02:00:00',Break2nd1st='09:00:00',Break2nd='12:00:00',Break2_2nd='13:00:00',OutPutTotalHr='14:30:00',OutPutLateHour='04:30:00' union all 
select ID=4,start_time='22:00', end_time='5:00',Time_In='00:00:00',Time_Out='19:00:00',Break1st='02:00:00',Break2nd1st='09:00:00',Break2nd='12:00:00',Break2_2nd='13:00:00',OutPutTotalHr='11:00:00',OutPutLateHour='02:00:00' union all  
select ID=5,start_time='22:00', end_time='5:00',Time_In='08:30:00',Time_Out='01:00:00',Break1st='17:30:00',Break2nd1st='21:00:00',Break2nd='23:00:00',Break2_2nd='23:30:00',OutPutTotalHr='12:30:00',OutPutLateHour='02:30:00' union all 
select ID=6,start_time='22:00', end_time='5:00',Time_In='07:00:00',Time_Out='20:30:00',Break1st='11:30:00',Break2nd1st='12:00:00',Break2nd='17:00:00',Break2_2nd='18:00:00',OutPutTotalHr='12:00:00',OutPutLateHour=''  union all 
select ID=7,start_time='22:00', end_time='5:00',Time_In='08:30:00',Time_Out='00:00:00',Break1st='13:00:00',Break2nd1st='13:10:00',Break2nd='17:45:00',Break2_2nd='21:00:00',OutPutTotalHr='12:05:00',OutPutLateHour='02:00:00' union all  
select ID=8,start_time='22:00', end_time='5:00',Time_In='08:30:00',Time_Out='01:00:00',Break1st='17:30:00',Break2nd1st='21:00:00',Break2nd='23:00:00',Break2_2nd='23:30:00',OutPutTotalHr='12:30:00',OutPutLateHour='02:30:00' union all  
select ID=9,start_time='22:00', end_time='5:00',Time_In='09:00:00',Time_Out='00:00:00',Break1st='11:30:00',Break2nd1st='12:30:00',Break2nd='17:30:00',Break2_2nd='20:30:00',OutPutTotalHr='11:00:00',OutPutLateHour='02:00:00' union all  
select ID=10,start_time='22:00', end_time='5:00',Time_In='09:20:00',Time_Out='00:00:00',Break1st='12:30:00',Break2nd1st='13:30:00',Break2nd='18:00:00',Break2_2nd='21:50:00',OutPutTotalHr='9:50:00',OutPutLateHour='02:00:00'
) A

;with 
    time_consider as 
( 
	select *  from #abc 
) , 
    breaks as 
(
	select ID,Break1st , Break2nd1st from #abc 
		union all 
	select ID,Break2nd , Break2_2nd from #abc 
), 
    breaks_we_want as 
(
	select a.*,b.start_time,b.end_time from breaks a , time_consider b where a.ID=b.ID and a.Break1st > b.start_time and a.Break2nd1st < b.end_time
) , 
   we_got_the_break_time as 
(
	select ID,
	sum(datediff(minute,cast(Break1st as datetime),cast(Break2nd1st as datetime)))  as sum_break_minutes 
	from breaks_we_want
	group by ID
) , 
    now_OutputLateHour as 
(
	select 
	      ID,
		  cast(start_time as datetime) as startime , 
		  case when dateadd(dd,1,cast(time_out as datetime)) < dateadd(dd,1,cast(end_time as datetime))  then dateadd(dd,1,cast(time_out as datetime)) else dateadd(dd,1,cast(end_time as datetime))   end as endtime 
    from 
	    time_consider
)
select 
       a.ID,
	   (datediff(minute,a.startime,a.endtime) - b.sum_break_minutes) as InMinutes_OutputLateHour, CAST( CAST(((datediff(minute,a.startime,a.endtime) - b.sum_break_minutes)) AS int) / 60 AS varchar) + ':'  + right('0' + CAST(CAST(((datediff(minute,a.startime,a.endtime) - b.sum_break_minutes)) AS int) % 60 AS varchar(2)),2) as OutputLateHour 
from now_OutputLateHour a , we_got_the_break_time b 
where a.ID=b.ID
go

However, only 2 correct.

Please advise.

Thanks.

Regards,
Micheale

14

hi

when you put into #abc .. make them all "time" datatype fields

15

Hi,

Still no luck. Can you try below code in your SQL Management Studio?
drop table #abc

select * into #abc  from (
select ID=1,start_time=cast('22:00:00' as time), end_time=cast('05:00:00' as time),Time_In=cast('00:01:00' as time),Time_Out=cast('18:00:00' as time),Break1st=cast('04:30:00' as time),Break2nd1st=cast('09:00:00' as time),Break2nd=NULL,Break2_2nd=NULL,OutPutTotalHr=cast('13:29:00' as time),OutPutLateHour=cast('04:29:00' as time) union all 
select ID=2,start_time=cast('22:00:00' as time), end_time=cast('05:00:00' as time),Time_In=cast('09:00:00' as time),Time_Out=cast('22:30:00' as time),Break1st=cast('13:00:00' as time),Break2nd1st=cast('15:00:00' as time),Break2nd=cast('17:30:00' as time),Break2_2nd=cast('21:00:00' as time),OutPutTotalHr=cast('8:00:00' as time),OutPutLateHour=cast('00:30:00' as time) union all 
select ID=3,start_time=cast('22:00:00' as time), end_time=cast('05:00:00' as time),Time_In=cast('00:00:00' as time),Time_Out=cast('22:30:00' as time),Break1st=cast('02:00:00' as time),Break2nd1st=cast('09:00:00' as time),Break2nd=cast('12:00:00' as time),Break2_2nd=cast('13:00:00' as time),OutPutTotalHr=cast('14:30:00' as time),OutPutLateHour=cast('04:30:00' as time) union all 
select ID=4,start_time=cast('22:00:00' as time), end_time=cast('05:00:00' as time),Time_In=cast('00:00:00' as time),Time_Out=cast('19:00:00' as time),Break1st=cast('02:00:00' as time),Break2nd1st=cast('09:00:00' as time),Break2nd=cast('12:00:00' as time),Break2_2nd=cast('13:00:00' as time),OutPutTotalHr=cast('11:00:00' as time),OutPutLateHour=cast('02:00:00' as time) union all  
select ID=5,start_time=cast('22:00:00' as time), end_time=cast('05:00:00' as time),Time_In=cast('08:30:00' as time),Time_Out=cast('01:00:00' as time),Break1st=cast('17:30:00' as time),Break2nd1st=cast('21:00:00' as time),Break2nd=cast('23:00:00' as time),Break2_2nd=cast('23:30:00' as time),OutPutTotalHr=cast('12:30:00' as time),OutPutLateHour=cast('02:30:00' as time) union all 
select ID=6,start_time=cast('22:00:00' as time), end_time=cast('05:00:00' as time),Time_In=cast('07:00:00' as time),Time_Out=cast('20:30:00' as time),Break1st=cast('11:30:00' as time),Break2nd1st=cast('12:00:00' as time),Break2nd=cast('17:00:00' as time),Break2_2nd=cast('18:00:00' as time),OutPutTotalHr=cast('12:00:00' as time),OutPutLateHour=null  union all 
select ID=7,start_time=cast('22:00:00' as time), end_time=cast('05:00:00' as time),Time_In=cast('08:30:00' as time),Time_Out=cast('00:00:00' as time),Break1st=cast('13:00:00' as time),Break2nd1st=cast('13:10:00' as time),Break2nd=cast('17:45:00' as time),Break2_2nd=cast('21:00:00' as time),OutPutTotalHr=cast('12:05:00' as time),OutPutLateHour=cast('02:00:00' as time) union all  
select ID=8,start_time=cast('22:00:00' as time), end_time=cast('05:00:00' as time),Time_In=cast('08:30:00' as time),Time_Out=cast('01:00:00' as time),Break1st=cast('17:30:00' as time),Break2nd1st=cast('21:00:00' as time),Break2nd=cast('23:00:00' as time),Break2_2nd=cast('23:30:00' as time),OutPutTotalHr=cast('12:30:00' as time),OutPutLateHour=cast('02:30:00' as time) union all  
select ID=9,start_time=cast('22:00:00' as time), end_time=cast('05:00:00' as time),Time_In=cast('09:00:00' as time),Time_Out=cast('00:00:00' as time),Break1st=cast('11:30:00' as time),Break2nd1st=cast('12:30:00' as time),Break2nd=cast('17:30:00' as time),Break2_2nd=cast('20:30:00' as time),OutPutTotalHr=cast('11:00:00' as time),OutPutLateHour=cast('02:00:00' as time) union all  
select ID=10,start_time=cast('22:00:00' as time), end_time=cast('05:00:00' as time),Time_In=cast('09:20:00' as time),Time_Out=cast('00:00:00' as time),Break1st=cast('12:30:00' as time),Break2nd1st=cast('13:30:00' as time),Break2nd=cast('18:00:00' as time),Break2_2nd=cast('21:50:00' as time),OutPutTotalHr=cast('9:50:00' as time),OutPutLateHour=cast('02:00:00' as time)
) A

;with time_consider as 
( 
	select *  from #abc 
), breaks as 
(
	select ID,Break1st , Break2nd1st from #abc 
		union all 
	select ID,Break2nd , Break2_2nd from #abc 
), breaks_we_want as 
(
	select a.*,b.start_time,b.end_time 
	from breaks a , time_consider b 
	where a.ID=b.ID and a.Break1st > b.start_time and a.Break2nd1st < b.end_time
), we_got_the_break_time as 
(
	select ID,
	sum(datediff(minute,cast(Break1st as datetime),cast(Break2nd1st as datetime)))  as sum_break_minutes 
	from breaks_we_want
	group by ID
), now_OutputLateHour as 
(
	select 
	      ID,
		  cast(start_time as datetime) as startime , 
		  case when dateadd(dd,1,cast(time_out as datetime)) < dateadd(dd,1,cast(end_time as datetime))  then dateadd(dd,1,cast(time_out as datetime)) else dateadd(dd,1,cast(end_time as datetime))   end as endtime 
    from 
	    time_consider
)

select 
     a.ID,
	 (datediff(minute,a.startime,a.endtime) - b.sum_break_minutes) as InMinutes_OutputLateHour, CAST( CAST(((datediff(minute,a.startime,a.endtime) - b.sum_break_minutes)) AS int) / 60 AS varchar) + ':'  + right('0' + CAST(CAST(((datediff(minute,a.startime,a.endtime) - b.sum_break_minutes)) AS int) % 60 AS varchar(2)),2) as OutputLateHour 
from now_OutputLateHour a , we_got_the_break_time b 
where a.ID=b.ID
go

Thanks.

Regards,
Micheale

16

hi

i will be busy for the next 2 to 3 hours

i will try after that

17

hi Micheale

the code is working fine
.. somethings wrong ..

Lets have a live discussion .. it will be over quickly ..

18

The data you have .. ..the last time ...

my logic gets breaks > 22:00 and less than 5:00 ..
the data is not matching this .. only 2 records are matching this

19

Hi,

Please see all my explanation for each select below how I get the output late hour:-

select ID=1,start_time='22:00', end_time='5:00',Time_In='00:01:00',Time_Out='18:00:00',Break1st='04:30:00',Break2nd1st='09:00:00',Break2nd=NULL,Break2_2nd=NULL,OutPutTotalHr='13:29:00',OutPutLateHour='04:29:00'
Explanation:
Work from 00:01:00 - 04:30:00 && 09:00:00 - 18:00
Time_In 00:01 Break1st 04:30 > Fall under 22:00:00 to 05:00 = 04:29 minutes

select ID=2,start_time='22:00', end_time='5:00',Time_In='09:00:00',Time_Out='22:30:00',Break1st='13:00:00',Break2nd1st='15:00:00',Break2nd='17:30:00',Break2_2nd='21:00:00',OutPutTotalHr='8:00:00',OutPutLateHour='00:30:00'
Explanation:
Work from 09:00:00 - 22:30:00 && Took Break 13:00:00-15:00:00 && Took Break 17:30:00-21:00:00
Break2_2nd 21:00 Time Out 22:30 > Fall under 22:00:00 to 05:00 = 30 minute

select ID=3,start_time='22:00', end_time='5:00',Time_In='00:00:00',Time_Out='22:30:00',Break1st='02:00:00',Break2nd1st='09:00:00',Break2nd='12:00:00',Break2_2nd='13:00:00',OutPutTotalHr='14:30:00',OutPutLateHour='02:30:00'
Explanation:
Work from 00:00:00 - 02:00:00 && 09:00:00 - 22:30:00 && Took Break 12:00:00-13:00:00
Time In 00:00:00 Break1st 02:00:00 fall under 22:00:00 to 05:00 = 2 Hour
Break2nd1st 09:00 Time Out 22:30:00 fall under 22:00 to 05:00 = 30 minutes

select ID=4,start_time='22:00', end_time='5:00',Time_In='00:00:00',Time_Out='19:00:00',Break1st='02:00:00',Break2nd1st='09:00:00',Break2nd='12:00:00',Break2_2nd='13:00:00',OutPutTotalHr='11:00:00',OutPutLateHour='02:00:00'
Explanation:
Work from 00:00:00 - 02:00:00 && 09:00:00 - 19:00:00 && Took Break 12:00:00-13:00:00
Time_In 00:00:00 Break1st 02:00:00 fall under 22:00:00 to 05:00 = 2 Hour

select ID=6,start_time='22:00', end_time='5:00',Time_In='07:00:00',Time_Out='20:30:00',Break1st='11:30:00',Break2nd1st='12:00:00',Break2nd='17:00:00',Break2_2nd='18:00:00',OutPutTotalHr='12:00:00',OutPutLateHour=''
Explanation:
Work from 07:00:00 - 20:30:00 && Took Break 11:30:00-12:00:00 && Took Break 17:00:00 -18:00:00
NULL fall under 22:00:00 to 05:00 = NULL

select ID=7,start_time='22:00', end_time='5:00',Time_In='08:30:00',Time_Out='00:00:00',Break1st='13:00:00',Break2nd1st='13:10:00',Break2nd='17:45:00',Break2_2nd='21:00:00',OutPutTotalHr='12:05:00',OutPutLateHour='02:00:00'
Explanation:
Work from 08:30:00 - 00:00:00 && Took Break 13:00:00-13:10:00 && Took Break 17:45:00-21:00:00
Break2_2nd 21:00:00 Time_Out 00:00:00 fall under 22:00:00 to 05:00 = 2 Hour

select ID=9,start_time='22:00', end_time='5:00',Time_In='09:00:00',Time_Out='00:00:00',Break1st='11:30:00',Break2nd1st='12:30:00',Break2nd='17:30:00',Break2_2nd='20:30:00',OutPutTotalHr='11:00:00',OutPutLateHour='02:00:00'
Explanation:
Work from 09:00:00 - 00:00:00 && Took Break 11:30:00-12:30:00 && Took Break 17:30:00-20:30:00
Break2_2nd 20:30:00 Time_Out 00:00:00 fall under 22:00:00 to 05:00 = 2 Hour

select ID=10,start_time='22:00', end_time='5:00',Time_In='09:20:00',Time_Out='00:00:00',Break1st='12:30:00',Break2nd1st='13:30:00',Break2nd='18:00:00',Break2_2nd='21:50:00',OutPutTotalHr='9:50:00',OutPutLateHour='02:00:00'
Explanation:
Work from 09:20:00 - 00:00:00 && Took Break 12:30:00-13:30:00 && Took Break 18:00:00-21:50:00
Break2_2nd 21:50:00 Time_Out 00:00:00 fall under 22:00:00 to 05:00 = 2 Hour

Thanks.

Regards,
Micheale

20

hi

i think the SQL has to be written .. from below ..

1) time in to break 1st .. 
2) break 1st                   [ before or after 22:00 ]  [ before or after 5:00]
3) break 2nd to timeout        [ before or after 22:00 ]  [ before or after 5:00]
4) break2nd 1st                [ before or after 22:00 ]  [ before or after 5:00]
5  break2nd 2nd to timeout     [ before or after 22:00 ]  [ before or after 5:00]

Is this correct !!!

Please lets discuss live and finish quickly ..

21

put all this into a Table .. and use dynamic SQL to generate the statement

Example Dynamic SQL
if x =1 then 'where name = har'
if x =2 then 'where city = London'

image
image348×560 4.69 KB