在SQL查询中获取Shift值的难度

Currently my table structire for table SHIFT is as follow:

目前我的SHIFT表格结构如下：

ID   Name       Start    End 
1    Shift1     06:00    14:00
2    Shift2     14:00    22:00
3    Shift3     22:00    06:00

Now I pass parameter to this query in hour like 11 or 15 or 22 or 03

现在我将参数传递给此查询，如11或15或22或03

For that parameter, I would like to get the result that in which shift the passed hour will reside.

对于该参数，我想得到的结果是传递的小时将驻留在哪个班次中。

So if I pass 11, it shoud give me Shift1. If I pass 23, it should give me Shift3.

所以，如果我通过11，它会给我Shift1。如果我通过23，它应该给我Shift3。

Following query that I wrote works fine for any value from 07 to 21, it is giving me blank value and for obvious reasons.

以下查询我写的从07到21的任何值都可以正常工作，它给了我空白值，原因显而易见。

select * from MII_SHIFT
where '[Param.1]' >= left(START,2) and '[Param.1]' <  left(END,2)

Can anyone help me how can I change the query so that I can get proper response for 22,23,00,01,02,03,04,05.

任何人都可以帮助我如何更改查询，以便我可以获得22,23,00,01,02,03,04,05的正确响应。

Thanks

谢谢

4 个解决方案

#1

SELECT *
FROM shift
WHERE 
     (    left(START,2) > left(END,2)
     AND  ('[Param.1]' >= left(START,2) OR '[Param.1]' <  left(END,2))
     )
OR   (    left(START,2) < left(END,2)
     AND  '[Param.1]' >= left(START,2) AND '[Param.1]' <  left(END,2) 
     )

I answer a similar answer a litle time ago.

我在一段时间之前回答了类似的答案。

Shorts start < end (5-9): the value need be between start and end
短裤开始 <结束（5-9）：值需要在开始和结束之间< li>
Jacket start > end (10-4): the value is < start or > end
护套开始>结束（10-4）：值为 end 或>

#2

Assuming the values are stored as strings, then this is pretty easy:

假设值存储为字符串，那么这很容易：

select s.*
from shifts s
where (start < end and right('00' + @param1, 2) >= start and right('00' + @param1, 2) < end) or
      (start > end and (right('00' + @param1, 2) >= start or right('00' + @param1, 2) < end))

This assumes that @param1 is a string. The right() is used to left pad the string with zeroes. If that is already true, then the code would be even simpler.

这假设@ param1是一个字符串。 right（）用于左键填充字符串为零。如果这已经是真的，那么代码会更简单。

EDIT:

编辑：

With padding, this simplifies to:

使用填充，这简化为：

select s.*
from shifts s
where (start < end and @param1 >= start and @param1< end) or
      (start > end and (@param1 >= start or @param1 < end))

#3

Simplest way is most likely to convert the times into dates, and if the end date is earlier than start, then add one day. You could use time datatype as input too, instead of just hour, but this is now an example with int:

最简单的方法是将时间转换为日期，如果结束日期早于开始日期，则添加一天。您也可以使用time数据类型作为输入，而不仅仅是小时，但现在这是一个使用int的示例：

declare @hour int, @date datetime

set @hour = 3

set @date = convert(datetime, convert(varchar(2), @hour) + ':00', 108) 

select Name
from (
  select Name, 
    [Start] as Start1, 
    case when [End] < [Start] then dateadd(day, 1, [End]) else [End] End as End1,
    case when [End] < [Start] then dateadd(day, -1, [Start]) else [Start] End as Start2, 
    [End] as End2   
  from (
    select Name, convert(datetime, [Start], 108) as [Start], convert(datetime, [End], 108) as [End]
    from Table1
  ) X
) Y
where ((Start1 <= @date and @date < End1) or (Start2 <= @date and @date < End2))

Edit: added 2nd start / end columns to the derived table to handle second part of the shift.

编辑：将第二个开始/结束列添加到派生表以处理班次的第二部分。

Example in SQL Fiddle

SQL小提琴中的示例

#4

Thankk you all. With the hep from all of your refrences, I was able to build the query which gave me appropriate results.

谢谢大家。有了你所有的参考资料，我能够构建查询，给出了适当的结果。

Query is as foolow:

查询是愚蠢的：

SELECT Name FROM  SHIFT WHERE 

(LEFT(START,2) < LEFT(END,2) AND '[Param.1]' >= LEFT(START,2) AND '[Param.1]' < LEFT(END,2)) 

OR

(LEFT(START,2) > LEFT(END,2) AND ('[Param.1]' >= LEFT(START,2) OR '[Param.1]' < LEFT(END,2)))

#1

SELECT *
FROM shift
WHERE 
     (    left(START,2) > left(END,2)
     AND  ('[Param.1]' >= left(START,2) OR '[Param.1]' <  left(END,2))
     )
OR   (    left(START,2) < left(END,2)
     AND  '[Param.1]' >= left(START,2) AND '[Param.1]' <  left(END,2) 
     )