如何改进我的SQL函数？有70个条件语句

本文介绍了如何改进我的SQL函数？有70个条件语句的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

此功能适用于我需要更新的系统。以前的系统会为员工手动输入班次调度，但问题是，由于员工人数众多而且这些员工的意愿很耗时几乎每天/每周改变他们的班次，所以他们的日程安排的另一个改变将手动完成，我将要做的这个功能或更新旨在自动分配他们的'时间'或'超时'的比较的转变，实际上这个功能正常工作，因为它会在字面上计算出时间/出时间并且分配得当，但是如果员工在轮班前一小时计时，而且在轮班后一个小时内功能也会很好，但问题是如果一个员工将为他的轮班时间迟到，所以自动另一个班次将从该功能分配给他，如果他/她将工作超时，也会暂停， '这是th我的功能有限，希望你们有更好的想法，因为我正在研究我的想法，并提出解决方案来解决这个问题， 这是我的功能：

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

这是我的shif_codes及其对应的shift_names / Shift_schedule

Here is my shif_codes and its corresponding shift_names/Shift_schedule

Quote：

Shift ID - 班次时间表 1凌晨4点到下午1点 2早上5点到下午2点 3早上6点到下午3点 4早上6点到下午5点 5早上7点到下午2点 6早上7点到下午4点 7早上7点到下午5点 8上午8点到下午5点 9 8 am到7pm 10上午9点到下午6点 11下午2点到晚上9点 12下午2点到晚上11点 13下午4点到凌晨1点 14下午5点到凌晨2点 15 6 pm t o 3 am 16晚上9点至早上6点 17晚上8点至凌晨5点 18 3pm至12am 19 7pm至4am 20 3 am到12pm 23上午10点到晚上7点 24 12pm到9pm 25 3 am到1:30 pm_9 .5小时 26早上5点到下午3点半.9小时 28上午12点到晚上11点_10小时。 32早上6点到下午4点半_9.5小时 33早上7点到下午5点半~9.5小时 34 8 am到6:30 pm_9.5hrs 35 9 am到7:30 pm_9.5hrs 36 10 am到8:30 pm_9.5hrs 37 11 amto9:30pm-9.5hrs 38 12 am到10：30_9.5 39 1 pmto11:30pm_9.5hrs 40 2pm to12：30 am-9.5rs 41 3pm到凌晨1:30 42下午4点到凌晨2:30 _9.5hrs 43下午5点到凌晨3:30 _9.5hrs 44下午6点到4点30分_9 .5小时 45晚上7点至5点半_9.5小时 46晚上8点至早上6:30 _9.5小时 47 9pm至7:30 am_9.5hrs 48晚上10点到8点半_9.5小时 49晚上11点到9点半_9.5小时 50 12pm到10:30 am_9.5hrs 51 1点到12点pm_10小时 52 2点到1点pm_10hrs 53 3 am到2pm_10hrs 54 4 am to3pm_10hrs 55早上5点至下午4点 56早上6点至下午5点 57早上7点至下午6点_10小时 58 8 am至7pm_10hrs 59 9 am到8pm_10hrs 60 10 am到9pm_10h rs 61上午11点到晚上10点_10小时 62 12 am到11am_10hrs 63 1pm到12am_10hrs 64 2pm到1am_10hrs 65 3pm至2am_10hrs 66下午4点至凌晨3点 67 5pm至4am_10hrs 68 6pm至5am_10hrs 69 7pm至6 am_10hrs 70 8pm至7am_10hrs 71 9pm至8am_10hrs 72 10pm至9am_10hrs 73晚上11点至10点10分 21下午1点至晚上10点 22凌晨1点至10点 75晚上10点至早上7点 76上午11点至晚上8点 77上午2点至上午11点 78上午12点至上午9点 79下午4点至晚上11点（6小时） 80早上7点到下午2点（6小时） 81下午2点到晚上9点（6小时） 82晚上9点到凌晨4点（6小时） 83晚上10点至凌晨5点（6小时） 84下午5点至凌晨12点（6小时） 85 8 am至6:30 pm 86 8 amto6:00pmcomp（ 9.5） 27凌晨4点到下午2点半_9.5小时 29凌晨1点到11点9点59分 30 2 amto12：30pm_9.5hrs 31 5 am到3:30 pm_9.5hrs 74 12pm至11pm_10hrs

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,

推荐答案

最简单的方法是使用包含所需值的查找表，然后加入该表。因此，该表将包含登录和注销的离散值，映射到班次代码。 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.

我的第一个问题，我给了你关于代码中出错的提示。 我该怎么办？改进我的SQL函数，它有70多个计算轮班计划的条件语句？ [ ^ ] 我以为你会利用它们。 只要你愉快地混合使用2种不同的格式，你可以做任何你想要的更正或改变，什么都行不了。 - 首先要做的是切换代码使用整个查询的单一时间格式，也可能是您使用的所有其他代码。 SQL案例给出了行军的答案，因此您必须非常谨慎地订购得到正确结果的条件。 所以@login ='3：00：00'和@logout = '13：30：00'n因为第一次得到1而不是25匹配原则。 [更新] 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] 引用：

但是我遇到了另一个问题，比如如果这个他/她的转变，但他会在OverTime上，

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.

引用：

我可以使用循环结构这个案例吗？

can i use a looping structure to this case sir?

可能，但我不知道你想用循环做什么。 当你开始谈论OverTime时，我有这种感觉该查询是一个更大的问题的一部分，并且转移号用于其他东西，我甚至怀疑你是一个糟糕的解决方案。 制作转移不重叠，是好方向。 但这段代码：

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:

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

$ b通过巧妙的重新排序可以简化$ b：

can be simplified by a clever reordering:

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?