How can I improved my SQL function? With 70 conditional statements

Question

1.00/5 (1 vote)

See more:

SQL-Server-2012

This function is for the system I debug which is need to be updated.The system before will manually input shift scheduling for the employees, but the problem is, it is time consuming because of the volume of employees, and these employee's will change their shift almost everyday/week, so another change of their schedule will be done manually, this function or update that i will be making is intended to automatically assign the shifts from the comparison of their 'time in' or 'time out', actually the function works properly because it will literally computes the timein/out and were assigned properly but that if the employee will time in an hour before his shift and also an hour after his shift the function will be fine , but the problems is what if an Employee will time in late for his shift so automatically another shift will be assign to him base from the function and also for the timeout if ever he/she will work OverTime,

'this is the limit of my function hopefully you guys have a better idea as i am working on my idea too and come up to a solution that will solve this problem,

Here is my funtion:

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

	end
	)		
										

	return @shiftcode
	
	end

Here is my shif_codes and its corresponding shift_names/Shift_schedule

Quote:
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

What I have tried:

I will try to modify my function that instead of comparing into time-in and time-out i will just compare it to the time-in or the starting time,

Posted 21-Nov-17 19:16pm

thyknow

Updated 22-Nov-17 20:36pm

v5

Add a Solution

Comments

Richard Deeming 22-Nov-17 14:08pm

REPOST
You have already posted this:
https://www.codeproject.com/Questions/1216434/How-can-I-improve-my-SQL-function-that-has-plus-co[^]

Patrice T 23-Nov-17 0:24am

Almost a Repost, The error of first question have been solved.

2 solutions

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)

Pete O'Hanlon · Answer 1 · 2017-11-21T20:53:00

Solution 1

The simplest way to do this would be to have a lookup table that contains the values you want, then you would join on that table. So, that table would contain discrete values for login and logouts, mapped to the shift code.

Posted 21-Nov-17 20:53pm

Pete O'Hanlon

Comments

thyknow 22-Nov-17 3:05am

Thank You for your reply sir, I do have a shift_settings table that has each log in and log out, but still it won't give me the exact code that i want,My table has ShiftCode, Shift schedule, start_time, and end_time, I want is to apply this in order for the shift schedule to automatically assign to the certain employee

Pete O'Hanlon 22-Nov-17 3:09am

That means you have the wrong table to look up against. I talked about adding a lookup table that contains all the conditions that you need to satisfy getting the right shift code.

thyknow 22-Nov-17 3:16am

Can you suggest or give me example how to create the look up table that you suggested sir? or at least give a point of view Thank you so much for your time

Patrice T · Answer 2 · 2017-11-22T09:25:00

Solution 2

I your first question, I gave you hints about what is wrong in your code.
How can I improve my SQL function that has 70+ conditional statements that computes shift schedules?[^]
I thought your would have took advantage of them.
As long as you happily mix 2 different format for time, you can do any corrections or changes you want, nothing will work.
- First thing to do is to switch code to use a single time format for the whole query and probably all other code you use.

The SQL case gives the answer of first march, so you have to very carefully order all the conditions to get correct results.
So with @login ='3:00:00' and @logout ='13:30:00'n you will get 1 instead of 25 because of first match principle.

[Update]

Quote:
but i came up to another problem like what if this his/her shift but he will be on an OverTime,

You need to explain the whole problem you try to solve, context matters.

Quote:
can i use a looping structure to this case sir?

Probably, but I don't know what you want to do with the loop.

as you start to speak about OverTime, I have the feeling that the query is part of a bigger problem, and that the shift number is used for something else, I even suspect that you are on a bad solution.

Making the shifts non overlapping, is the good direction.
but this code:

SQL

set @shiftcode =(select case  
--1								  
	  when @login <='1:00:59'	and @logout >='10:00:00' and @logout < '11:30:00' Then 22
	  when @login <='1:00:59'	and @logout >='11:30:00' and @logout < '12:00:00' Then 29
	  when @login <='1:00:59'	and @logout >='12:00:00'Then 51
--2							
	  when @login <='2:00:59'	and @logout >='11:00:00' and @logout < '12:30:00' Then 77
	  when @login <='2:00:59'	and @logout >='12:30:00' and @logout < '13:00:00' Then 30
	  when @login <='2:00:59'	and @logout >='13:00:00'Then 52

can be simplified by a clever reordering:

SQL

set @shiftcode =(select case  
--1								  
	  when @login <='1:00:59'	and @logout >='12:00:00' Then 51
	  when @login <='1:00:59'	and @logout >='11:30:00' Then 29
	  when @login <='1:00:59'	and @logout >='10:00:00' Then 22
--2							
	  when @login <='2:00:59'	and @logout >='13:00:00' Then 52
	  when @login <='2:00:59'	and @logout >='12:30:00' Then 30
	  when @login <='2:00:59'	and @logout >='11:00:00' Then 77

Did you noticed that if someone leave early, you query fails?

Posted 22-Nov-17 9:25am

Patrice T

Updated 22-Nov-17 19:39pm

v4

Comments

thyknow 22-Nov-17 19:27pm

I see, I thought of that yesterday, but i came up to another problem like what if this his/her shift but he will be on an OverTime, can i use a looping structure to this case sir?

Patrice T 23-Nov-17 0:20am

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

thyknow 22-Nov-17 21:48pm

Hi sir thank you for your answer, I came up to this answer I order the time both start and end then put another comparison

	CREATE function [dbo].[fn_ComputeShiftCode]
	(
		@login time,
		@logout time
	)
	returns int 
		as
	begin
	declare @shiftcode int

	
	set @shiftcode =(select case  
							--1								  
								  when @login <='1:00:59'	and @logout >='10:00:00' and @logout < '11:30:00' Then 22
								  when @login <='1:00:59'	and @logout >='11:30:00' and @logout < '12:00:00' Then 29
								  when @login <='1:00:59'	and @logout >='12:00:00'Then 51
							--2							
								  when @login <='2:00:59'	and @logout >='11:00:00' and @logout < '12:30:00' Then 77
								  when @login <='2:00:59'	and @logout >='12:30:00' and @logout < '13:00:00' Then 30
								  when @login <='2:00:59'	and @logout >='13:00:00'Then 52
							--3							
								  when @login <='3:00:59'	and @logout >='12:00:00' and @logout < '13:30:00' Then 20
								  when @login <='3:00:59'	and @logout >='13:30:00' and @logout < '14:00:00' Then 25
								  when @login <='3:00:59'	and @logout >='14:00:00'Then 53
	                        --4
								  when @login <= '4:00:59' and @logout >= '13:00:00' and @logout < '14:30:00'  Then 1
							      when @login <='4:00:59'	and @logout >='14:30:00' and @logout < '15:00:00' Then 27
								  when @login <='4:00:59'	and @logout >='15:00:00'Then 54
							--5
								 when @login <='5:00:59' and @logout >= '14:00:00' and @logout < '15:30:00'  Then 2
								 when @login <='5:00:59'	and @logout >='15:30:00' and @logout < '16:00:00' Then 26
								 when @login <='5:00:59'	and @logout >='15:30:00' and @logout < '16:00:00' Then 31
							     when @login <='5:00:59'	and @logout >='16:00:00'Then 55
							--6
								when @login <='6:00:59' and @logout >= '15:00:00' and @logout < '16:30:00' then 3
								when @login <='6:00:59'	and @logout >='16:30:00' and @logout < '17:00:00'Then 32
							    when @login <='6:00:59' and @logout >= '17:00:00' then 4
							    when @login <='6:00:59'	and @logout >='17:00:00'Then 56
						     --7
							  	  when @login <='7:00:59' and @logout >= '14:00:00' and @logout < '16:00:00' then 5
								  when @login <='7:00:59'	and @logout >='14:00:00'and @logout < '16:00:00' Then 80
								  when @login <='7:00:59' and @logout >= '16:00:00' and @logout < '17:00:00' then 6
								  when @login <='7:00:59' and @logout >= '17:00:00'and @logout < '17:30:00' then 7	
								  when @login <='7:00:59'	and @logout >='17:30:00'and @logout < '18:00:00'Then 33
								  when @login <='7:00:59'	and @logout >='18:00:00'Then 57
							--8	
								  when @login <='8:00:59'	and @logout >='17:00:00' and @logout <'18:00:00' Then 8
								  when @login <='8:00:59'	and @logout >='18:00:00'and @logout <'18:30:00'Then 86
						 		  when @login <='8:00:59'	and @logout >='18:30:00' and @logout <'19:00:00' Then 34
								  when @login <='8:00:59'	and @logout >='18:30:00'  and @logout <'19:00:00'Then 85
							      when @login <='8:00:59'	and @logout >='19:00:00' Then 9
								  when @login <='8:00:59'	and @logout >='19:00:00'Then 58
							--9
								  when @login <='9:00:59'	and @logout >='18:00:00' and @logout <'19:30:00' Then 10	
								  when @login <='9:00:59'	and @logout >='19:30:00' and @logout <'20:00:00' Then 35						    
								  when @login <='9:00:59'	and @logout >='20:00:00'Then 59
							--10
								  when @login <='10:00:59'	and @logout >='19:00:00' and @logout <'20:30:00' Then 23
								  when @login <='10:00:59'	and @logout >='20:30:00'and @logout <'21:00:00'Then 36
								  when @login <='10:00:59'	and @logout >='21:0

Patrice T 23-Nov-17 0:23am

Indeed, it is an improvement.

thyknow 23-Nov-17 0:43am

but there is still a problem because what if the certain employee will time out late or will be late for time in :)) still needs some brain storming , I want to calculate it using start time or timein time but the problem is there are 1 or more schedules in that certain start time, :(((((

Patrice T 23-Nov-17 0:54am

For good advice, you need to explain the whole problem.

thyknow 23-Nov-17 1:42am

This function is for the system I debug which is need to be updated. before the shift scheduling for the employees will be assign manually, but the problem is, it is time consuming because of the volume of employees and these employee's will change shift almost everyday/week, so another change of their schedule will be done manually, this function or update that i will be making is intended to automatically assign the shifts from the comparison of their time in or time out, actually the function works properly because it will literally computes the timein/out and were assigned properly but that if the employee will time in an hour before his shift and also an hour after his shift the function will be fine , but the problems is what if an Employee will time in late for his shift so automatically another shift will be assign to him base from the function and also for the timeout if ever he/she will work OverTime,

'this is the limit of my function sir hopefully you have a better idea as i am working on my idea too and come up to a solution that will solve this problem, i have one idea like i will compute his shift base from his starting time, but the problem is there are 3 shifts every hour, I hope this is clear to you sir :D

Patrice T 23-Nov-17 2:15am

I fear you can get help only on exact question you ask.
too much detail we don't know.

thyknow 23-Nov-17 2:41am

sorry for my explanation sir,