How can I improve my SQL function that has 70+ conditional statements that computes shift schedules?

Question

1.00/5 (1 vote)

See more:

SQL-Server-2012

I have a function that computes and outputs shift schedules, in my function I have 70+ conditional statements, but there is an error

String or binary data would be truncated

here is my code:

GO
	ALTER function [dbo].[fn_ComputeShiftCode]
	(
		@login time,
		@logout time
	)
	returns varchar(10)
	as
	begin
	declare @shiftcode int

	set @shiftcode = (select case when @login <= '4:00:00' and @logout >= '13:00:00' then 1

								 when @login <='5:00:00' and @logout >= '14:00:00' then 2
							    when @login <='6:00:00' and @logout >= '15:00:00' then 3
							   when @login <='6:00:00' and @logout >= '17:00:00' then 4
								  when @login <='7:00:00' and @logout >= '14:00:00' then 5
								  when @login <='7:00:00' and @logout >= '16:00:00' then 6
								  when @login <='7:00:00' and @logout >= '16:00:00' then 7
								  when @login <='8:00:00'	and @logout >='17:00:00' Then 8
								  when @login <='8:00:00'	and @logout >='19:00:00' Then 9
								  when @login <='9:00:00'	and @logout >='18:00:00'Then 10
								  when @login <='14:00:00'	and @logout >='21:00:00'Then 11
								  when @login <='14:00:00'	and @logout >='23:00:00'Then 12
								  when @login <='16:00:00'	and @logout >='1:00:00'	Then 13
								  when @login <='17:00:00'	and @logout >='2:00:00'	Then 14
								  when @login <='18:00:00'	and @logout >='3:00:00'	Then 15
								  when @login <='21:00:00'	and @logout >='6:00:00'	Then 16
								  when @login <='20:00:00'	and @logout >='5:00:00'	Then 17
								  when @login <='15:00:00'	and @logout >='0:00:00'	Then 18
								  when @login <='19:00:00'	and @logout >='4:00:00'	Then 19
								  when @login <='3:00:00'	and @logout >='12:00:00'Then 20
								  when @login <='10:00:00'	and @logout >='19:00:00'Then 23
								  when @login <='12:00:00'	and @logout >='21:00:00'Then 24
								  when @login <='3:00:00'	and @logout >='13:30:00'Then 25
								  when @login <='5:00:00'	and @logout >='15:30:00'Then 26
								  when @login <='0:00:00'	and @logout >='23:00:00'Then 28
								  when @login <='6:00:00'	and @logout >='16:30:00'Then 32
								  when @login <='7:00:00'	and @logout >='17:30:00'Then 33
								  when @login <='8:00:00'	and @logout >='18:30:00'Then 34
								  when @login <='9:00:00'	and @logout >='19:30:00'Then 35
								  when @login <='10:00:00'	and @logout >='20:30:00'Then 36
								  when @login <='11:00:00'	and @logout >='21:30:00'Then 37
								  when @login <='12:00:00'	and @logout >='22:30:00'Then 38
								  when @login <='13:00:00'	and @logout >='23:30:00'Then 39
								  when @login <='14:00:00'	and @logout >='0:30:00'	Then 40
								  when @login <='15:00:00'	and @logout >='1:30:00'	Then 41
								  when @login <='16:00:00'	and @logout >='2:30:00'	Then 42
								  when @login <='17:00:00'	and @logout >='3:30:00'	Then 43
								  when @login <='18:00:00'	and @logout >='4:30:00'	Then 44
								  when @login <='19:00:00'	and @logout >='5:30:00'	Then 45
								  when @login <='20:00:00'	and @logout >='6:30:00'	Then 46
								  when @login <='21:00:00'	and @logout >='7:30:00'	Then 47
								  when @login <='22:00:00'	and @logout >='8:30:00'	Then 48
								  when @login <='23:00:00'	and @logout >='9:30:00'	Then 49
								  when @login <='12:00:00'	and @logout >='10:30:00'Then 50
								  when @login <='1:00:00'	and @logout >='12:00:00'Then 51
								  when @login <='2:00:00'	and @logout >='13:00:00'Then 52
								  when @login <='3:00:00'	and @logout >='14:00:00'Then 53
								  when @login <='4:00:00'	and @logout >='15:00:00'Then 54
								  when @login <='5:00:00'	and @logout >='16:00:00'Then 55
								  when @login <='6:00:00'	and @logout >='17:00:00'Then 56
								  when @login <='7:00:00'	and @logout >='18:00:00'Then 57
								  when @login <='8:00:00'	and @logout >='19:00:00'Then 58
								  when @login <='9:00:00'	and @logout >='20:00:00'Then 59
								  when @login <='10:00:00'	and @logout >='21:00:00'Then 60
								  when @login <='11:00:00'	and @logout >='22:00:00'Then 61
								  when @login <='0:00:00'	and @logout >='11:00:00'Then 62
								  when @login <='13:00:00'	and @logout >='0:00:00'	Then 63
								  when @login <='14:00:00'	and @logout >='1:00:00'	Then 64
								  when @login <='15:00:00'	and @logout >='2:00:00'	Then 65
								  when @login <='16:00:00'	and @logout >='3:00:00'	Then 66
								  when @login <='17:00:00'	and @logout >='4:00:00'	Then 67
								  when @login <='18:00:00'	and @logout >='5:00:00'	Then 68
								  when @login <='19:00:00'	and @logout >='6:00:00'	Then 69
								  when @login <='20:00:00'	and @logout >='7:00:00'	Then 70
								  when @login <='21:00:00'	and @logout >='8:00:00'	Then 71
								  when @login <='22:00:00'	and @logout >='9:00:00'	Then 72
								  when @login <='23:00:00'	and @logout >='10:00:00'Then 73
								  when @login <='13:00:00'	and @logout >='22:00:00'Then 21
								  when @login <='1:00:00'	and @logout >='10:00:00'Then 22
								  when @login <='22:00:00'	and @logout >='7:00:00'	Then 75
								  when @login <='11:00:00'	and @logout >='20:00:00'Then 76
								  when @login <='2:00:00'	and @logout >='11:00:00'Then 77
								  when @login <='0:00:00'	and @logout >='9:00:00'	Then 78
								  when @login <='16:00:00'	and @logout >='23:00:00'Then 79
								  when @login <='7:00:00'	and @logout >='14:00:00'Then 80
								  when @login <='14:00:00'	and @logout >='21:00:00'Then 81
								  when @login <='21:00:00'	and @logout >='4:00:00'	Then 82
								  when @login <='22:00:00'	and @logout >='5:00:00'	Then 83
								  when @login <='17:00:00'	and @logout >='0:00:00'	Then 84
								  when @login <='8:00:00'	and @logout >='18:30:00'Then 85
								  when @login <='8:00:00'	and @logout >='18:00:00'Then 86
								  when @login <='4:00:00'	and @logout >='14:30:00'Then 27
								  when @login <='1:00:00'	and @logout >='11:30:00'Then 29
								  when @login <='2:00:00'	and @logout >='12:30:00'Then 30
								  when @login <='5:00:00'	and @logout >='15:30:00'Then 31
								  when @login <='12:00:00'	and @logout >='23:00:00'Then 74
								end
								)		
										
	return @shiftcode
	
	end

What I have tried:

I used this Function from a Stored Procedure That Outputs a Select Statement and then insert it into a derived table

Posted 21-Nov-17 14:01pm

thyknow

Updated 21-Nov-17 18:47pm

v2

Add a Solution

2 solutions

Solution 2

Your procedure is completely wrong.
You are doing comparisons on times with 2 different formats, and this have consequences!
Logically, you want 1:00:00 < 2:00:00 < 3:00:00 < 9:00:00 < 10:00:00 < 19:00:00 < 21:00:00
but you really have 10:00:00 < 1:00:00 < 19:00:00 < 2:00:00 < 21:00:00 < 3:00:00 < 9:00:00
Since you use strings as time values, you need to use a single format: 02:00:00

You will never reach shiftcode 7 because it is same as shiftcode 6 !

SQL

when @login <='7:00:00' and @logout >= '16:00:00' then 6
when @login <='7:00:00' and @logout >= '16:00:00' then 7

Your handling of midnight is wrong too:

SQL

when @login <='0:00:00'	and @logout >='11:00:00'Then 62
when @login <='13:00:00' 	and @logout >='0:00:00'	Then 63

Only @login ='0:00:00' will match, nothing before that, so '23:59:00' will not.
@logout >='0:00:00' Anything will match.

I fear the shifts handling will be a little more complicated than planned.

Posted 21-Nov-17 17:32pm

Patrice T

Updated 21-Nov-17 18:05pm

v2

Comments

thyknow 22-Nov-17 0:50am

Hi, Thank You For your solution, I already solved the error "string binary data would be truncated", this is my problem now the conditional statements, do you have any idea how to have conditional statements? here's a list of shifts with there corresponding shiftcodes/shift_id

Shift ID - Shift Schedules
1 4am to 1pm
2 5am to 2pm
3 6am to 3pm
4 6am to 5pm
5 7am to 2pm
6 7am to 4pm
7 7am to 5pm
8 8am to 5pm
9 8am to 7pm
10 9am to 6pm
11 2pm to 9pm
12 2pm to 11pm
13 4pm to 1am
14 5pm to 2am
15 6pm to 3am
16 9pm to 6am
17 8pm to 5am
18 3pm to 12am
19 7pm to 4am
20 3am to 12pm
23 10am to 7pm
24 12pm to 9pm
25 3am to 1:30pm_9.5hrs
26 5 am to 3:30pm9.5hrs
28 12am to 11pm _10hrs.
32 6am to 4:30pm_9.5hrs
33 7am to 5:30pm_9.5hrs
34 8am to 6:30pm_9.5hrs
35 9am to 7:30pm_9.5hrs
36 10am to8:30pm_9.5hrs
37 11amto9:30pm-9.5hrs
38 12am to 10:30_9.5
39 1pmto11:30pm_9.5hrs
40 2pm to12:30am-9.5hrs
41 3pm to 1:30am
42 4pm to 2:30am_9.5hrs
43 5pm to 3:30am_9.5hrs
44 6pm to 4:30am_9.5hrs
45 7pm to 5:30am_9.5hrs
46 8pm to 6:30am_9.5hrs
47 9pm to 7:30am_9.5hrs
48 10pm to 8:30am_9.5hrs
49 11pm to 9:30am_9.5hrs
50 12pm to 10:30am_9.5hrs
51 1am to 12:00pm_10hrs
52 2am to 1:00pm_10hrs
53 3am to 2pm_10hrs
54 4am to3pm_10hrs
55 5am to 4pm_10hrs
56 6am to 5pm_10hrs
57 7am to 6pm_10hrs
58 8am to 7pm_10hrs
59 9am to 8pm_10hrs
60 10am to 9pm_10hrs
61 11am to 10 pm_10hrs
62 12am to 11am_10hrs
63 1pm to 12am_10hrs
64 2pm to 1am_10hrs
65 3pm to 2am_10hrs
66 4pm to 3am_10hrs
67 5pm to 4am_10hrs
68 6pm to 5am_10hrs
69 7pm to 6 am_10hrs
70 8pm to 7am_10hrs
71 9pm to 8am_10hrs
72 10pm to 9am_10hrs
73 11pm to 10am_10hrs
21 1pm to 10pm
22 1am to 10am
75 10pm to 7am
76 11am to 8pm
77 2am to 11am
78 12am to 9am
79 4pm to 11 pm(6hrs)
80 7am to 2pm(6hrs)
81 2pm to 9pm(6hrs)
82 9pm to 4am(6hrs)
83 10pm to 5am(6hrs)
84 5PM TO 12AM(6hrs)
85 8am to 6:30pm comp
86 8amto6:00pmcomp(9.5)
27 4am to 2:30pm_9.5hrs
29 1am to 11:30am9.5hrs
30 2amto12:30pm_9.5hrs
31 5am to 3:30pm_9.5hrs
74 12pm to 11pm_10hrs

Patrice T 22-Nov-17 1:00am

Use Improve question to update your question.
So that everyone can pay attention to this information.

or open a new question for this.

thyknow 22-Nov-17 1:02am

ok sir thank you :D

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

David_Wimbley · Accepted Answer · 2017-11-21T14:20:00

Solution 1

My recommendation would be to create a ShiftCode table that stores the shift code and has a Min and Max column containing the shift start/stop.

I'd create a table because you are effectively managing a table in your function itself which, to me, is better suited as a lookup table. If the value changes you've got to update/exec function alter scripts where my preference would be to just manage the shift data in a table.

So something like the following would work

SQL

DECLARE @ShiftCodes TABLE (
    ShiftStart time,
	ShiftEnd time,
	ShiftCode int
);

INSERT INTO @ShiftCodes (ShiftStart, ShiftEnd, ShiftCode) VALUES ('22:00:00', '5:00:00', 83)
INSERT INTO @ShiftCodes (ShiftStart, ShiftEnd, ShiftCode) VALUES ('17:00:00', '0:00:00', 84)
INSERT INTO @ShiftCodes (ShiftStart, ShiftEnd, ShiftCode) VALUES ('8:00:00', '18:30:00', 85)

This way you convert your 70 or so case statements to 1 select statement. Would need to account for possibility that your table doesn't have complete data so account for if no records returned...etc.

SQL

DECLARE @UsersShiftCode int = (SELECT ShiftCode FROM @ShiftCodes WHERE '21:01:01' <= ShiftStart AND '21:01:01' >= ShiftEnd)

Posted 21-Nov-17 14:20pm

David_Wimbley

Comments

thyknow 21-Nov-17 21:06pm

Thank You Sir David for your answer,Yes I do have a table for shift settings/ shift schedule, but My aim is to output one shift ID, this is my select statement that uses that function

with cte1 as (
select ROW_NUMBER() over (order by et.Emp_ID) as 'Rownum' , et.Emp_ID,
CONVERT(varchar(50), CAST(et.DatetimeLogin as date)) + ' - ' + CONVERT(varchar(50), CAST(dbo.fn_GetLogout( et.Emp_ID, et.DatetimeLogin, et.Company_Name) as date)) as 'Log Dates',
et.DatetimeLogin, dbo.fn_GetLogout( et.Emp_ID, et.DatetimeLogin, et.Company_Name) as 'LogOut', et.[First BreakIn], et.[First BreakOut], et.[Second BreakIn], et.[Second BreakOut], et.LongBreakIn, et.LongBreakOut,
et.OTBreakIn, et.OTBreakOut, et.PayrollDate,
ISNULL(dbo.fn_GetShortBreakTardiness(et.[First BreakIn], et.[First BreakOut]),0.00) as 'FirstBreak Tardiness' ,
ISNULL(dbo.fn_GetLongBreakTardiness(et.LongBreakIn, et.LongBreakOut),0.00) as 'LongBreak Tardiness' ,
ISNULL(dbo.fn_GetShortBreakTardiness(et.[Second BreakIn], et.[Second BreakOut]),0.00) as 'SecondBreak Tardiness' ,
ISNULL(dbo.fn_GetOTBreakTardiness(et.OTBreakIn, et.OTBreakOut, ed.OTBreak),0.00) as 'OTBreak Tardiness' ,
CAST((DATEDIFF(MINUTE, et.DatetimeLogin, dbo.fn_GetLogout(et.Emp_ID,et.DatetimeLogin,et.Company_Name))/60) as int)/60 as 'Working Hours' ,
0.00
as 'Tardiness' ,
0.00
as 'Overtime' ,
isHalfDay, isUndertime, isRestDayDuty, InOutOnly, isDipping, ed.OTBreak
from Emp_TimeLogs et
inner join Employee_Details ed on et.Emp_ID = ed.Emp_ID
where CAST(et.Date_Time as date) between @datefrom and @dateto and et.Company_Name = @comp --and Processed = 0

),
cte2 as( select convert (int,dbo.fn_ComputeShiftCode(cast( e.DatetimeLogin as time), cast(e.LogOut as time))) as 'ShiftCodes',* from cte1 e),
cte3 as ( select dbo.fn_GetShiftName( ShiftCodes, @comp ) as 'ShiftName', * from cte2)
--insert into table6
insert into @table6(shift,shiftcode,rownum, Empid, logdate, login, logout, breakinAM, breakoutAM, breakinPM, breakoutPM, longbreakin, longbreakout, otbreakin, otbreakout, payrolldate, AMtardiness,
LongBreaktardiness, PMtardiness, OTTardiness, totalworkinghrs, tardiness, Overtime, isHalfDay, isUndertime, isRestDayDuty, InOutOnly, Dipping, otbreak)

select ShiftName,ShiftCodes,Rownum,Emp_ID,[Log Dates],DatetimeLogin,LogOut,[First BreakIn],[First BreakOut],[Second BreakIn],[Second BreakOut],LongBreakIn,LongBreakOut,
OTBreakIn,OTBreakOut,PayrollDate,[FirstBreak Tardiness],[LongBreak Tardiness],[SecondBreak Tardiness],[OTBreak Tardiness],[Working Hours],Tardiness,Overtime,isHalfDay,isUndertime,isRestDayDuty
,InOutOnly,isDipping,OTBreak from cte3

this is from my stored procedure