Time difference between two hours of leave

/*
* Time difference between two hours of leave
*/
Create PROCEDURE [dbo]. [Os_vocation_hour]
@starttime varchar (20),--Start time
@endtime varchar (20)--End time

As
if (IsDate (@starttime) =0 or IsDate (@endtime) =0)--Non-date format
Begin
Select 0
End
else--Normal date format
Begin
DECLARE @day_jg varchar, @day_num float, @hour_num float, @hour_temps float, @hour_tempe float--Declare three variables
DECLARE @s1 datetime, @e1 datetime, @num_old float

Set @hour_temps =datepart (Hour, @starttime)--get Start hour
Set @hour_tempe =datepart (Hour, @endtime)--Get End hour

if (@hour_temps >=8 and @hour_temps <=17 and @hour_tempe >=8 and @hour_tempe <=17)-Day shift
Begin
Select @hour_num =datediff (Hour,cast (@starttime as DateTime), CAST (@endtime as DateTime)--Gets the difference between two hours of time
Select @day_num =datediff (Day,cast (@starttime as DateTime), CAST (@endtime as DateTime)--Gets the difference in days of two times
if (@day_num =0 and @hour_num >0)--day
Begin
-Same morning or same afternoon in the same day
if (@hour_temps >=8 and @hour_temps <=12 and @hour_tempe >=8 and @hour_tempe <=12) or (@hour_temps >=13 and @ Hour_temps<=17 and @hour_tempe >=13 and @hour_tempe <=17))
Set @day_jg =datediff (Mi,cast (@starttime as DateTime), CAST (@endtime as DateTime)
else if @hour_temps >=8 and @hour_temps <=12 and @hour_tempe >=13 and @hour_tempe <=17--start in the morning, end in the afternoon
Set @day_jg =datediff (Mi,cast (@starttime as DateTime), CAST (@endtime as DateTime))-60
Else--time period is wrong
Set @day_jg =-1000
End
else if (@day_num =1)--The next day
Begin
if (@hour_temps >=8 and @hour_temps <=12 and @hour_tempe >=8 and @hour_tempe <=12)--start in the morning, end in the morning
Begin
Set @day_jg =datediff (Mi,cast (@starttime as DateTime), CAST (@endtime as DateTime) -60-60*15
End
else if (@hour_temps >=8 and @hour_temps <=12 and @hour_tempe >=13 and @hour_tempe <=17)--start in the morning, end in the afternoon
Begin
Set @day_jg =datediff (Mi,cast (@starttime as DateTime), CAST (@endtime as DateTime) -60-60*15-60
End
else if (@hour_temps >=13 and @hour_temps <=17 and @hour_tempe >=8 and @hour_tempe <=12)--start in the afternoon, end in the morning
Begin
Set @day_jg =datediff (Mi,cast (@starttime as DateTime), CAST (@endtime as DateTime) -60*15
End
else if (@hour_temps >=13 and @hour_temps <=17 and @hour_tempe >=13 and @hour_tempe <=17)--start in the afternoon, end in the afternoon
Begin
Set @day_jg =datediff (Mi,cast (@starttime as DateTime), CAST (@endtime as DateTime) -60-60*15
End
End
else if (@day_num >1)-Multi-day
Begin
if (@hour_temps >=8 and @hour_temps <=12 and @hour_tempe >=8 and @hour_tempe <=12)--start in the morning, end in the morning
Begin
Set @day_jg =datediff (Mi,cast (@starttime as DateTime), CAST (@endtime as DateTime) -60-60*15-(@day_num-1) *24*60
End
else if (@hour_temps >=8 and @hour_temps <=12 and @hour_tempe >=13 and @hour_tempe <=17)--start in the morning, end in the afternoon
Begin
Set @day_jg =datediff (Mi,cast (@starttime as DateTime), CAST (@endtime as DateTime) -60-60*15-60-(@day_num-1) *24*60
End
else if (@hour_temps >=13 and @hour_temps <=17 and @hour_tempe >=8 and @hour_tempe <=12)--start in the afternoon, end in the morning
Begin
Set @day_jg =datediff (Mi,cast (@starttime as DateTime), CAST (@endtime as DateTime) -60*15-(@day_num-1) *24*60
End
else if (@hour_temps >=13 and @hour_temps <=17 and @hour_tempe >=13 and @hour_tempe <=17)--start in the afternoon, end in the afternoon
Begin
Set @day_jg =datediff (Mi,cast (@starttime as DateTime), CAST (@endtime as DateTime) -60-60*15-(@day_num-1) *24*60
End
--processing hours-processing days
Set @[email Protected]_num
Set @[email protected]

while (@s1 <[email protected])--use while loop to determine if weekends
Begin
if (Datename (Weekday, @s1) = ' Saturday ' or datename (Weekday, @s1) = ' Sunday ')--is the weekend, the number of days minus 1
Set @[email protected]_num-1
Set @s1 =dateadd (day,1, @s1)--Next day
End
Set @[email protected]_num-1--less than 1 days
Set @[email protected][email protected]_num*8*60--minutes
End

End
else if (@hour_temps >=20 and @hour_temps <=24 or @hour_temps >=0 and @hour_temps <=5) and (@hour_tempe >=20 and @hour_tempe <=24 or @hour_tempe >=0 and @hour_tempe <=5))--Night shift
Begin
Set @starttime =dateadd (hour,-12, @starttime)-12 hours in advance
Set @endtime =dateadd (hour,-12, @endtime)
Select @day_num =datediff (Day, @starttime, @endtime)--Gets the difference in days of two times
Select @hour_num =datediff (Hour,cast (@starttime as DateTime), CAST (@endtime as DateTime))
Set @hour_temps =datepart (Hour, @starttime)--get Start hour
Set @hour_tempe =datepart (Hour, @endtime)--Get End hour

if (@day_num =0 and @hour_num >0)--day
Begin
-Same morning or same afternoon in the same day
if (@hour_temps >=8 and @hour_temps <=13 and @hour_tempe >=8 and @hour_tempe <=13) or (@hour_temps >=14 and @ Hour_temps<=17 and @hour_tempe >=14 and @hour_tempe <=17))
Set @day_jg =datediff (Mi,cast (@starttime as DateTime), CAST (@endtime as DateTime)
else if @hour_temps >=8 and @hour_temps <=13 and @hour_tempe >=14 and @hour_tempe <=17
Set @day_jg =datediff (Mi,cast (@starttime as DateTime), CAST (@endtime as DateTime))-60
Else--time period is wrong
Set @day_jg =-1000
End
else if (@day_num =1)--The next day
Begin
if (@hour_temps >=8 and @hour_temps <=13 and @hour_tempe >=8 and @hour_tempe <=13)--start in the morning, end in the morning
Begin
Set @day_jg =datediff (Mi,cast (@starttime as DateTime), CAST (@endtime as DateTime) -60-60*15
End
else if (@hour_temps >=8 and @hour_temps <=13 and @hour_tempe >=14 and @hour_tempe <=17)--start in the morning, end in the afternoon
Begin
Set @day_jg =datediff (Mi,cast (@starttime as DateTime), CAST (@endtime as DateTime) -60-60*15-60
End
else if (@hour_temps >=14 and @hour_temps <=17 and @hour_tempe >=8 and @hour_tempe <=13)--start in the afternoon, end in the morning
Begin
Set @day_jg =datediff (Mi,cast (@starttime as DateTime), CAST (@endtime as DateTime) -60*15
End
else if (@hour_temps >=14 and @hour_temps <=17 and @hour_tempe >=14 and @hour_tempe <=17)--start in the afternoon, end in the afternoon
Begin
Set @day_jg =datediff (Mi,cast (@starttime as DateTime), CAST (@endtime as DateTime) -60-60*15
End
End
else if (@day_num >1)-Multi-day
Begin
if (@hour_temps >=8 and @hour_temps <=13 and @hour_tempe >=8 and @hour_tempe <=13)--start in the morning, end in the morning
Begin
Set @day_jg =datediff (Mi,cast (@starttime as DateTime), CAST (@endtime as DateTime) -60-60*15-(@day_num-1) *24*60
End
else if (@hour_temps >=8 and @hour_temps <=13 and @hour_tempe >=14 and @hour_tempe <=17)--start in the morning, end in the afternoon
Begin
Set @day_jg =datediff (Mi,cast (@starttime as DateTime), CAST (@endtime as DateTime) -60-60*15-60-(@day_num-1) *24*60
End
else if (@hour_temps >=14 and @hour_temps <=17 and @hour_tempe >=8 and @hour_tempe <=13)--start in the afternoon, end in the morning
Begin
Set @day_jg =datediff (Mi,cast (@starttime as DateTime), CAST (@endtime as DateTime) -60*15-(@day_num-1) *24*60
End
else if (@hour_temps >=14 and @hour_temps <=17 and @hour_tempe >=14 and @hour_tempe <=17)--start in the afternoon, end in the afternoon
Begin
Set @day_jg =datediff (Mi,cast (@starttime as DateTime), CAST (@endtime as DateTime) -60-60*15-(@day_num-1) *24*60
End
--processing hours-processing days

Set @[email Protected]_num
Set @[email protected]

while (@s1 <[email protected])--use while loop to determine if weekends
Begin
if (Datename (Weekday, @s1) = ' Saturday ' or datename (Weekday, @s1) = ' Sunday ')--is the weekend
Set @[email protected]_num-1
Set @s1 =dateadd (day,1, @s1)--Next day
End
Set @[email protected]_num-1
Set @[email protected][email protected]_num*8*60
End
End
Else--the time of the problem
Begin
Set @day_jg =-1000
End
Set @[email PROTECTED]_JG/60/8--number of days from minutes
if (@day_jg >0 and @day_jg <8*60)--less than 8 hours, counted 0.5 days
Set @day_num =0.5
else if (@day_jg >8*60 and (@day_jg/60)% 8!=0)--more than 8 hours, divisible by 8, plus 0.5 days
Set @[email protected]_num+0.5
DECLARE @t float
Set @t= cast ((CAST (@day_jg as Decimal (10,3)/60) as Decimal (10,1))--hours
Select Case where @t-floor (@t) >0 and @t-(Floor (@t) +0.5) <=0 then (Floor (@t) +0.5) when @t-(Floor (@t) +0.5) >0 then (f Loor (@t) +1) Else floor (@t) end
End

Time difference between two hours of leave