SQLTeam.com | Weblogs | Forums

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

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

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

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 !!

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

hi

please see my attempt ..


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

hi

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

image

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

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

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

hi Micheale

no thoughts from your side :curious

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

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

hi

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

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

hi

i will be busy for the next 2 to 3 hours

i will try after that

hi Micheale

the code is working fine
.. somethings wrong ..

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

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

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

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 ..

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'