CODEWITHSUNDEEP
sqlsql-server
Soham Shah
Soham Shah

Reputation: 571

Difficulty in getting Shift Value in SQL Query

Currently my table structire for table SHIFT is as follow:

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

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.

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

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.

Thanks

Upvotes: 0

Views: 86

Answers (4)

James Z
James Z

Reputation: 12317

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:

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270513

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.

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))

Upvotes: 1

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

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) 
     )

enter image description here

I answer a similar answer a litle time ago.

  • Shorts start < end (5-9): the value need be between start and end
  • Jacket start > end (10-4): the value is < start or > end

Upvotes: 2

Soham Shah
Soham Shah

Reputation: 571

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)))

Upvotes: 0

Related Questions