February 5, 2014 at 2:49 am
Shift_table
Shiftno starttime endtime
1 06:00:00 14:00:00
2 14:00:00 22:00:00
3 22:00:00 06:00:00
Now i've an information that an employee works @ 03:30 AM, need to decide on which shift he is in, from the above shift master table i want to compare this 3:30 is between what ?, i need the answer to be 3 as 03:30 AM is in between 22:00 and 06:00.
But when i try to use between, it is not fetching the results. all the columns and my look data variable are in TIME data type, also tried with datetime datatype
Declare bettime time
Select @bettime = '03:30:00'
Select Shiftno from Shift_table where @bettime between starttime and endtime
Any clue or help please in SQL code.......
Ananth-India
February 5, 2014 at 4:43 am
DECLARE @BetTime DATETIME
SELECT @BetTime = CONVERT(DATETIME,'03:30:00')
;WITH ShiftTimes AS
(
SELECT 1 AS ShiftNo,CONVERT(DATETIME,'06:00:00') AS StartTime,
CONVERT(DATETIME,'14:00:00') AS EndTime
UNION
SELECT 2,CONVERT(DATETIME,'14:00:00'),CONVERT(DATETIME,'22:00:00')
UNION
SELECT 3,CONVERT(DATETIME,'22:00:00'),CONVERT(DATETIME,'06:00:00')
)
SELECT TOP 1 * FROM ShiftTimes
WHERE EndTime >= @BetTime
ORDER BY EndTime
February 5, 2014 at 8:44 am
Here's a possible solution, it's not pretty but it's better than my first version full of functions on the columns.
DECLARE @Timetime = '03:30:00';
WITH Shift_table(Shiftno, starttime, endtime) AS(
SELECT 1, CAST( '06:00:00' AS time), CAST( '14:00:00' AS time) UNION ALL
SELECT 2, '14:00:00', '22:00:00' UNION ALL
SELECT 3, '22:00:00', '06:00:00')
SELECT *
FROM Shift_table
WHERE @Time BETWEEN starttime AND endtime
OR ( endtime < starttime AND (@Time > starttime OR @Time < endtime ))
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply