Guest User

Untitled

a guest
Jan 18th, 2019
79
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.10 KB | None | 0 0
  1. Shift1 : 07:00:00-12:00:00
  2. Shift2 : 12:00:00-22:00:00
  3. Shift3 : 22:00:00-07:00:00
  4.  
  5. SELECT RequestNumber
  6. FROM Table
  7. WHERE (CONVERT(Time, RegisteredDateTime) BETWEEN '" & Shift1.Split("-")(0) &"' AND ' " & Shift1.Split("-")(1) & "')
  8.  
  9. Dim StartNumber As Integer = Convert.ToInt32(Shft3Arr(1).Split(":")(0))
  10. Dim EndShift As String = (StartNumber - 1) & ":59:59"
  11. query += "(CONVERT(Time, Complaints.RegisteredDateTime) >= '" + Shft3Arr(0) + "') OR (CONVERT
  12. (Time, DATEADD(DD, 1, Complaints.RegisteredDateTime)) <= '" + EndShift + "')"
  13.  
  14. WHERE CONVERT(Time, RegisteredDateTime) > convert(time,'07:00:00') and CONVERT(Time, RegisteredDateTime) <= convert(time,'12:00:00')
  15.  
  16. WHERE CONVERT(Time, RegisteredDateTime) > convert(time,'12:00:00') and CONVERT(Time, RegisteredDateTime) <= convert(time,'22:00:00')
  17.  
  18. WHERE CONVERT(Time,RegisteredDateTime) > convert(time,'22:00:00') and CONVERT(Time, DATEADD(DD,1,RegisteredDateTime)) <= convert(time,'06:59:59')
  19.  
  20. SELECT RequestNumber
  21. FROM Table
  22. WHERE DateAdd(hh,-7,CONVERT(Time, RegisteredDateTime))
  23. BETWEEN ....
  24.  
  25. SELECT RequestNumber
  26. FROM Table
  27. WHERE CONVERT(Time, RegisteredDateTime)
  28. BETWEEN DateAdd(hh,7,@date1) AND DateAdd(hh,7,@date2)
  29.  
  30. AND CONVERT(Time,DATEADD(DD,1,RegisteredDateTime)) < '07:00:00'
  31. AND CONVERT(Time,RegisteredDateTime) > '07:00:00'
  32. AND DATEDIFF(DD,CONVERT(DATE,RegisteredDateTime),
  33. CONVERT(Date,DATEADD(DD,1,RegisteredDateTime))) <=1
  34.  
  35. Shift1 : 07:00:00-12:00:00
  36. Shift2 : 12:00:00-22:00:00
  37. Shift3 : 22:00:00-07:00:00
  38.  
  39. declare @shift_start_time time ;
  40. declare @shift_end_time time;
  41.  
  42. IF shift 1:
  43. select @shift_start_time ='00:00:00'
  44. select @shift_end_time ='04:59:59'
  45.  
  46. IF shift 2:
  47. select @shift_start_time ='05:00:00'
  48. select @shift_end_time ='14:59:59'
  49.  
  50. IF shift 3:
  51. select @shift_start_time ='15:00:00'
  52. select @shift_end_time ='23:59:59'
  53.  
  54. SELECT RegisteredDateTime
  55. FROM t_shift
  56. WHERE CONVERT(Time, DateAdd(hh,-7, RegisteredDateTime))
  57. between @shift_start_time and @shift_end_time
  58.  
  59. select resuestNumber from table where requestNumber not in (query 1) and requestNumber not in(query 2)
Add Comment
Please, Sign In to add comment