Advertisement
Guest User

Untitled

a guest
Feb 19th, 2019
61
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.75 KB | None | 0 0
  1. declare @clockIn time = '08:00'
  2. declare @clockOut time = '19:00'
  3.  
  4. declare @a time= '00:00'
  5. ...all possible limits defined here...
  6. declare @f time= '23:59'
  7.  
  8. tests using clockIn only:
  9.  
  10. -- ab overtime
  11. if (@clockIn >= @a and @clockIn < @b)
  12. begin
  13. set @ax = @ax + ABS(DATEDIFF(hour,@a,@b))
  14. end
  15. -- cd work
  16. if (@clockIn >= @c and @clockIn <= @d)
  17. begin
  18. set @at = @at + ABS(DATEDIFF(hour,@c,@d))
  19. end
  20. -- ef lunch
  21. if (@clockIn >= @e and @clockIn <= @f)
  22. begin
  23. set @ai = @ai + ABS(DATEDIFF(hour,@e,@f))
  24. end
  25. -- gh work
  26. if (@clockIn >= @g and @clockIn <= @h)
  27. begin
  28. set @at = @at + ABS(DATEDIFF(hour,@g,@h))
  29. end
  30. -- ij overtime
  31. if (@clockIn > @i and @clockIn < @j)
  32. begin
  33. set @ax = @ax + ABS(DATEDIFF(hour,@i,@h))
  34. end
  35.  
  36. select @ax as overtime,@at as work,@ai as lunch
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement