Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Shift1 : 07:00:00-12:00:00
- Shift2 : 12:00:00-22:00:00
- Shift3 : 22:00:00-07:00:00
- SELECT RequestNumber
- FROM Table
- WHERE (CONVERT(Time, RegisteredDateTime) BETWEEN '" & Shift1.Split("-")(0) &"' AND ' " & Shift1.Split("-")(1) & "')
- Dim StartNumber As Integer = Convert.ToInt32(Shft3Arr(1).Split(":")(0))
- Dim EndShift As String = (StartNumber - 1) & ":59:59"
- query += "(CONVERT(Time, Complaints.RegisteredDateTime) >= '" + Shft3Arr(0) + "') OR (CONVERT
- (Time, DATEADD(DD, 1, Complaints.RegisteredDateTime)) <= '" + EndShift + "')"
- WHERE CONVERT(Time, RegisteredDateTime) > convert(time,'07:00:00') and CONVERT(Time, RegisteredDateTime) <= convert(time,'12:00:00')
- WHERE CONVERT(Time, RegisteredDateTime) > convert(time,'12:00:00') and CONVERT(Time, RegisteredDateTime) <= convert(time,'22:00:00')
- WHERE CONVERT(Time,RegisteredDateTime) > convert(time,'22:00:00') and CONVERT(Time, DATEADD(DD,1,RegisteredDateTime)) <= convert(time,'06:59:59')
- SELECT RequestNumber
- FROM Table
- WHERE DateAdd(hh,-7,CONVERT(Time, RegisteredDateTime))
- BETWEEN ....
- SELECT RequestNumber
- FROM Table
- WHERE CONVERT(Time, RegisteredDateTime)
- BETWEEN DateAdd(hh,7,@date1) AND DateAdd(hh,7,@date2)
- AND CONVERT(Time,DATEADD(DD,1,RegisteredDateTime)) < '07:00:00'
- AND CONVERT(Time,RegisteredDateTime) > '07:00:00'
- AND DATEDIFF(DD,CONVERT(DATE,RegisteredDateTime),
- CONVERT(Date,DATEADD(DD,1,RegisteredDateTime))) <=1
- Shift1 : 07:00:00-12:00:00
- Shift2 : 12:00:00-22:00:00
- Shift3 : 22:00:00-07:00:00
- declare @shift_start_time time ;
- declare @shift_end_time time;
- IF shift 1:
- select @shift_start_time ='00:00:00'
- select @shift_end_time ='04:59:59'
- IF shift 2:
- select @shift_start_time ='05:00:00'
- select @shift_end_time ='14:59:59'
- IF shift 3:
- select @shift_start_time ='15:00:00'
- select @shift_end_time ='23:59:59'
- SELECT RegisteredDateTime
- FROM t_shift
- WHERE CONVERT(Time, DateAdd(hh,-7, RegisteredDateTime))
- between @shift_start_time and @shift_end_time
- select resuestNumber from table where requestNumber not in (query 1) and requestNumber not in(query 2)
Add Comment
Please, Sign In to add comment