6

Question

How can I retrieve the current shift record based on getdate() and which lies between the existing ShiftStartTime and ShiftEndTime as per the defined table and data?

It is possible get shift if the time reaches 12.00 AM?

Table

create table ShiftMaster 
(
ShiftID char(4), 
ShiftName char(7), 
ShiftDescripiton varchar(20), 
ShiftStartTime time, 
ShiftEndTime time 
)

Data

insert into ShiftMaster 
values 
('SHF1', 'Shift A', 'First Shift', '07:15:01', '15:45:00'), 
('SHF2', 'Shift B', 'Second Shift', '15:45:01', '00:15:00'),
('SHF3', 'Shift C', 'Third Shift', '00:15:01', '07:15:00')

Original Data (Screenshot)

enter image description here

What I Have Tried

 SELECT * FROM VHASM2.ShiftMaster 
 WHERE FORMAT(getdate(),'HH:mm:ss') between ShiftStartTime and ShiftEndTime;

dbfiddle and Alternate Statement

select * 
from ShiftMaster
where 
'' + cast(datepart(hh, getdate()) as char(2)) + ':' + cast(datepart(n, getdate()) as char(2)) + ':' + cast(datepart(ss, getdate()) as char(2)) + '' >= ShiftStartTime 
and 
'' + cast(datepart(hh, getdate()) as char(2)) + ':' + cast(datepart(n, getdate()) as char(2)) + ':' + cast(datepart(ss, getdate()) as char(2)) + '' <= ShiftEndTime

The dbfiddle so far.

5
  • 6
    Help me write this query in SQL. – Erik Darling Jan 30 '19 at 18:37
  • 2
    Welcome to DBA.SE. I have formatted your question somewhat and added some additonal help and a dbfiddle to give you a sense of how good questions are asked. I hope I got your data and DDL right? Please feel free to comment and update your question if need be by editing it. – John K. N. Jan 30 '19 at 19:41
  • Thank you, not able to get 2nd shift from your Alternate Statement – 0143.NetUser Jan 30 '19 at 20:02
  • 2
    Please tag your SQL Server version – McNets Jan 30 '19 at 20:21
  • 1
    Yes, I realise that. My edit and addition of an alternate solution in dbfiddle was to give you an idea of how you can present your question. It wasn't intended as a solution. I was working on it, but it seems @ypercubeᵀᴹ was faster. Please don't forget to accept the solution that works for you. – John K. N. Jan 31 '19 at 6:44
7

You can separate the logic into 3 parts:

  • first, for "normal" shifts, that don't include midnight
  • and second and third for shifts that have midnight.

The query:

-- time now
declare @tn as time = getdate() ;

select *
from ShiftMaster
where ShiftStartTime <= @tn and @tn < ShiftEndTime
   or ShiftEndTime < ShiftStartTime and @tn < ShiftEndTime 
   or ShiftEndTime < ShiftStartTime and ShiftStartTime <= @tn 
  ;

Test in dbfiddle.uk.


Please also note that I used closed-open intervals and comparisons (avoiding the evil BETWEEN*), so the shift ('00:15:00', '07:15:00') stands for any time just of after 00:15 and before 07:15 (but not exactly 07:15). So every point in time should be in one and only one shift, assuming that you define the shifts correctly.

insert into ShiftMaster 
values 
('SHF1', 'Shift A', 'First Shift',
             '07:15:00', '15:45:00'),   -- notice the starts
('SHF2', 'Shift B', 'Second Shift',
             '15:45:00', '00:15:00'),   -- and the ends
('SHF3', 'Shift C', 'Third Shift',
             '00:15:00', '07:15:00') ;

With your method, there would be gaps of 1 second between shifts.

*: More info and detailed explanation about why to avoid BETWEEN with datetime types on the excellent blog post by Aaron Bertrand: What do BETWEEN and the devil have in common?.

2
  • thank you very much it solve my issue.I have one question, better i can use <= >= instead of between. – 0143.NetUser Jan 31 '19 at 17:41
  • No, as I say in the second part, I strongly suggest to avoid BETWEEN (and <= >=) but use closed - open intervals (and <=, <). Also read the linked article (about BETWEEN and the devil). – ypercubeᵀᴹ Jan 31 '19 at 17:49
1

The problem you are seeing is because of the 00:15:00 value. My current time, 21:43:26 is bigger than 00:15:00 and thus, the BETWEEN check fails. Because SQL Server cannot know that it is referring to the next day, at 15 past midnight.

A non-elegant, non-reusable work around

 IF NOT EXISTS
 (
 SELECT * FROM dbo.ShiftMaster 
 WHERE cast(convert(varchar, getdate(), 108)as time) 
 BETWEEN ShiftStartTime and  ShiftEndTime) 
 BEGIN
 SELECT * from ShiftMaster where ShiftID = 'SHF2';
 END
 ELSE 
  SELECT * FROM dbo.ShiftMaster 
 WHERE cast(convert(varchar, getdate(), 108)as time) BETWEEN ShiftStartTime and  ShiftEndTime;

Result

ShiftID   ShiftName   ShiftDescripiton    ShiftStartTime      ShiftEndTime
SHF2      Shift B     Second Shift        15:45:01.0000000    00:15:00.0000000

I would wait / look into a more elegant solution

1

If shifts don't change often you can use a function:

CREATE FUNCTION dbo.GetShift(@Date DATETIME)
RETURNS VARCHAR(10)
AS
BEGIN
    DECLARE @Time time = CAST(@Date AS TIME);

    RETURN
    CASE WHEN @Time >= '07:15:01' AND @Time < '15:45:00' THEN 'SHIFT1'
         WHEN @Time >= '00:15:01' AND @Time < '07:15:00' THEN 'SHIFT3'
         ELSE 'SHIFT2' END;
END

SELECT dbo.GetShift(GETDATE());

db<>fiddle here

1

Why don't you simple enter 2 records for Second shift? It will solve your problem.

insert into #ShiftMaster 
values 
('SHF1', 'Shift A', 'First Shift', '07:15:01', '15:45:00'), 
('SHF2', 'Shift B', 'Second Shift', '00:00:00', '00:15:00'),
('SHF2', 'Shift B', 'Second Shift', '15:45:01', '23:59:59'),
('SHF3', 'Shift C', 'Third Shift', '00:15:01', '07:15:00')

SELECT x.PunchTime, ShiftID, ShiftName
FROM ( VALUES (CONVERT(CHAR(8),GetDate(),108))
    , ('00:00:01'), ('00:00:00'), ('23:17:00')
    , ('01:25:00'), ('08:49:00'), ('13:00:00')
    , ('16:27:00')) x(PunchTime)
INNER JOIN #ShiftMaster as s 
    ON x.PunchTime BETWEEN ShiftStartTime and ShiftEndTime
0

in my ShiftMaster table , I have added one column Shifthr char(5) which will contain hour value in format like `"09:00"'

Suppose Shift hour is common for all shift then this column is not require,storing the value in variable will do.

So,in my script I am not using ShiftEnd column.

I have assume that you will pass parameter in format '02:15:00' or '00:00:00'

So in my sample Ignore ShiftEnd column value,

create table #ShiftMaster(shiftID char(4),ShiftName varchar(20),descriptions varchar(50)
,shiftStart time,shiftEnd time,Shifthr char(5))
insert into #ShiftMaster 
values 
('SHF1', 'Shift A', 'First Shift', '07:15:01', '16:15:01','09:00'), 
('SHF2', 'Break', 'n Shift', '23:30:59', '08:30:59','09:00'),
('SHF2', 'Shift B', 'Second Shift', '15:45:01', '00:45:01','09:00'),
 ('SHF3', 'Shift C', 'Third Shift', '00:15:01', '09:15:01','09:00')

First Manipulate input,

declare @input time='00:00:00' -- pass paramteter

declare @DateTimeinput datetime -- Local paramter to add date in input

declare @CurDate Datetime=cast(getdate() as date)

if( datepart(hour,@input)=0)
    set @DateTimeinput=dateadd(day,1,@CurDate)+cast(@input as DATETIME)
ELSE
    set @DateTimeinput=@CurDate+cast(@input as DATETIME)


select * from #ShiftMaster
where @DateTimeinput
BETWEEN (@CurDate+ cast(shiftStart as DATETIME)) 
and (@CurDate+ cast(shiftStart as DATETIME)+cast(Shifthr as DATETIME))

drop table #ShiftMaster

PLEASE ignore ShiftEnd column or put valid data.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.