Find out the shift Time

Question

Find out the shift Time

ashok.theagarajan

SSC-Addicted

Points: 421
More actions
August 22, 2016 at 3:15 am

#312642

I have a main table where I need to map the Time part of Open Time (a date time field) with a shift table containing the shift timings.
Select A.Number, A.OpenTime, C.ShiftName, C.ShiftStart, C.ShiftEnd
from Database..MainTable A Right Outer Join Database..LookupTable B
On A.Field like '%' + B.Field + '%'
Right Outer Join TBL_ShiftDetails C
On (convert(varchar(8), A.OpenTime, 108) >= C.ShiftStart and convert(varchar(8), A.OpenTime, 108) <= C.ShiftEnd)
Where B.Classification = 'XXXX'
And A.OpenTime >= '8-1-2016'
Order by A.OpenTime ASC
My shift table contains three fields
ShiftNameShiftStartShiftEnd
Shift1 15:30:00.000000012:29:00.0000000
Shift2 12:30:00.000000009:29:00.0000000
Shift3 09:30:00.000000015:29:00.0000000
This gives me shift 3 correctly but does not work for shift 1 and shift 2 (they come up NULL)
Any help is very much appreciated.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

Please provide sample table scripts and sample data (scripted as INSERTs in to your tables) along with expected output so we can better help you.

I will note that you are doing something that is not good, namely wrapping a column in a function in a WHERE or JOIN clause. Hopefully we can find a way to avoid that.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

You're looking for times that are between ShiftStart and ShiftEnd. However, if ShiftStart is after ShiftEnd, as it is in your first two rows, that condition will never be fulfilled. You'll need to make your logic more sophisticated, perhaps by adding 24 hours to ShiftEnd if it's less than ShiftStart. But we need a bit more information from you, like Kevin asked for, before we can help you any more than that.

John

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

ashok.theagarajan (8/22/2016)
I have a main table where I need to map the Time part of Open Time (a date time field) with a shift table containing the shift timings.
Select A.Number, A.OpenTime, C.ShiftName, C.ShiftStart, C.ShiftEnd
from Database..MainTable A Right Outer Join Database..LookupTable B
On A.Field like '%' + B.Field + '%'
Right Outer Join TBL_ShiftDetails C
On (convert(varchar(8), A.OpenTime, 108) >= C.ShiftStart and convert(varchar(8), A.OpenTime, 108) <= C.ShiftEnd)
Where B.Classification = 'XXXX'
And A.OpenTime >= '8-1-2016'
Order by A.OpenTime ASC
My shift table contains three fields
ShiftNameShiftStartShiftEnd
Shift1 15:30:00.000000012:29:00.0000000
Shift2 12:30:00.000000009:29:00.0000000
Shift3 09:30:00.000000015:29:00.0000000
This gives me shift 3 correctly but does not work for shift 1 and shift 2 (they come up NULL)
Any help is very much appreciated.

Shifts 1 and 2 are 21 hours long. Is this intentional? Shift 3 is only 6 hours.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

ashok.theagarajan (8/22/2016)
from Database..MainTable A
Right Outer Join Database..LookupTable B On A.Field like '%' + B.Field + '%'
Right Outer Join TBL_ShiftDetails C On (convert(varchar(8), A.OpenTime, 108) >= C.ShiftStart and convert(varchar(8), A.OpenTime, 108) <= C.ShiftEnd)

TheSQLGuru (8/22/2016)
I will note that you are doing something that is not good, namely wrapping a column in a function in a WHERE or JOIN clause. Hopefully we can find a way to avoid that.

Not only are functions used in the JOIN predicate, but the other JOIN is a LIKE with wildcards on both ends. :w00t:

We're going to need the DDL and sample data. Also, is this already in production? Are you able to change the design of the table?

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions