Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- declare @clockIn time = '08:00'
- declare @clockOut time = '19:00'
- declare @a time= '00:00'
- ...all possible limits defined here...
- declare @f time= '23:59'
- tests using clockIn only:
- -- ab overtime
- if (@clockIn >= @a and @clockIn < @b)
- begin
- set @ax = @ax + ABS(DATEDIFF(hour,@a,@b))
- end
- -- cd work
- if (@clockIn >= @c and @clockIn <= @d)
- begin
- set @at = @at + ABS(DATEDIFF(hour,@c,@d))
- end
- -- ef lunch
- if (@clockIn >= @e and @clockIn <= @f)
- begin
- set @ai = @ai + ABS(DATEDIFF(hour,@e,@f))
- end
- -- gh work
- if (@clockIn >= @g and @clockIn <= @h)
- begin
- set @at = @at + ABS(DATEDIFF(hour,@g,@h))
- end
- -- ij overtime
- if (@clockIn > @i and @clockIn < @j)
- begin
- set @ax = @ax + ABS(DATEDIFF(hour,@i,@h))
- end
- select @ax as overtime,@at as work,@ai as lunch
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement