thyknow Ответов: 2

Как я могу улучшить свою SQL-функцию, содержащую более 70 условных операторов, которые вычисляют графики сдвигов?

У меня есть функция, которая вычисляет и выводит графики сдвигов, в моей функции у меня есть 70+ условных операторов, но есть ошибка 
String or binary data would be truncated
вот мой код:

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


Что я уже пробовал:

Я использовал эту функцию из хранимой процедуры, которая выводит оператор Select, а затем вставляет его в производную таблицу

2 Ответов

Рейтинг:
2

Patrice T

Ваша процедура совершенно неверна.
Вы делаете сравнения по времени с 2 различными форматами, и это имеет последствия!
Логично, что вы хотите 1:00:00 < 2:00:00 < 3:00:00 < 9:00:00 < 10:00:00 < 19:00:00 < 21:00:00
но у вас действительно есть 10:00:00 < 1:00:00 < 19:00:00 < 2:00:00 < 21:00:00 < 3:00:00 < 9:00:00
Поскольку вы используете строки в качестве значений времени, вам нужно использовать один формат: 02:00:00

Вы никогда не достигнете shiftcode 7, потому что это то же самое, что shiftcode 6 !

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 <='0:00:00'	and @logout >='11:00:00'Then 62
when @login <='13:00:00' 	and @logout >='0:00:00'	Then 63

Только @login ='0:00:00' будет соответствовать, ничего до этого, так что '23:59:00' не будет.
@logout >='0:00:00' все будет соответствовать.

Боюсь, что работа со сменами будет несколько сложнее, чем планировалось.

thyknow

Привет, спасибо за ваше решение, я уже решил ошибку "строка двоичных данных будет усечена", это моя проблема теперь условные операторы, у вас есть какие-нибудь идеи, как иметь условные операторы? вот список сдвигов с соответствующими кодами сдвига/shift_id


Идентификатор сменности - график сменности
С 1 4 утра до 1 часа дня
С 2 5 утра до 2 вечера
С 3 6 утра до 3 вечера
С 4 6 утра до 5 вечера
С 5 7 утра до 2 часов дня
С 6 7 утра до 4 вечера
С 7 утра до 5 вечера
С 8 утра до 5 вечера
С 9 8 утра до 7 вечера
С 10 9 утра до 6 вечера
11 С 2 вечера до 9 вечера
12 С 2 вечера до 11 вечера
13 С 4 вечера до 1 часа ночи
14 С 5 вечера до 2 часов ночи
15 с 6 вечера до 3 утра
16 9 вечера до 6 утра
17 С 8 вечера до 5 утра
18 С 3 вечера до 12 утра
19 С 7 вечера до 4 утра
20 с 3 утра до 12 вечера
23 С 10 утра до 7 вечера
24 с 12 вечера до 9 вечера
25 3 утра до 1:30pm_9.5hrs
26 5 утра до 3:30pm9.5hrs
28 С 12 утра до 11 вечера _10 часов.
32 С 6 утра до 4:30pm_9.5hrs
33 с 7 утра до 5:30pm_9.5hrs
34 с 8 утра до 6:30pm_9.5hrs
35 с 9 утра до 7:30pm_9.5hrs
36 10 утра до 8:30pm_9.5hrs
37 11amto9:30pm-9.5 hrs
38 С 12 утра до 10:30_9.5
39 1pmto11:30pm_9.5hrs
40 С 2 вечера до 12:30 утра-9,5 часа
41 с 3 вечера до 1:30 утра
42 с 4 вечера до 2:30 утра_9.5hrs
43 с 5 вечера до 3:30 утра_9.5hrs
44 с 6 вечера до 4:30 утра_9.5hrs
45 7 вечера до 5:30 утра_9.5hrs
46 с 8 вечера до 6:30 утра_9.5hrs
47 с 9 вечера до 7:30 утра_9.5hrs
48 с 10 вечера до 8:30 утра_9.5hrs
49 11 вечера до 9:30 утра_9.5hrs
50 с 12 вечера до 10:30 утра_9.5hrs
51 с 1 утра до 12:00pm_10hrs
52 с 2 утра до 1:00pm_10hrs
53 3 часа ночи, чтобы 2pm_10hrs
54 4 утра to3pm_10hrs
55 с 5 утра до 4pm_10hrs
56 с 6 утра до 5pm_10hrs
57 с 7 утра до 6pm_10hrs
58 с 8 утра до 7pm_10hrs
59 с 9 утра до 8pm_10hrs
60 10 утра до 9pm_10hrs
61 с 11 утра до 10 вечера_10 часов
62 12 утра до 11am_10hrs
63 1 вечера до 12am_10hrs
64 с 2 до 1am_10hrs
65 3 вечера до 2am_10hrs
66 4 вечера до 3am_10hrs
67 5 вечера до 4am_10hrs
68 6 вечера до 5am_10hrs
69 с 7 вечера до 6 утра_10 часов
70 с 8 вечера до 7 утра_10 часов
71 9 вечера до 8am_10hrs
72 10 до 9am_10hrs
73 11 вечера до 10am_10hrs
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 утра до 6:30 вечера комп
86 8amto6:00pmcomp(9.5)
27 4 утра до 2:30pm_9.5hrs
29 С 1 утра до 11:30 утра 9.5 часов
30 2amto12:30pm_9.5hrs
31 5 утра до 3:30pm_9.5hrs
74 12 до 11pm_10hrs

Patrice T

Воспользуйся Улучшить вопрос чтобы обновить ваш вопрос.
Чтобы каждый мог обратить внимание на эту информацию.

или откройте для этого новый вопрос.

thyknow

хорошо, сэр, спасибо :D

Рейтинг:
16

David_Wimbley

Моя рекомендация состояла бы в том, чтобы создать таблицу ShiftCode, которая хранит код сдвига и имеет столбец Min и Max, содержащий начало/остановку сдвига.

Я бы создал таблицу, потому что вы эффективно управляете таблицей в самой своей функции, которая, на мой взгляд, лучше подходит в качестве таблицы поиска. Если значение изменяется, вы должны обновить/exec функцию alter scripts, где я бы предпочел просто управлять данными сдвига в таблице.

Таким образом, что-то вроде следующего будет работать

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)


Таким образом, вы преобразуете свои 70 или около того операторов case в 1 оператор select. Вам нужно будет учитывать возможность того, что ваша таблица не содержит полных данных, поэтому учитывайте, если никакие записи не возвращаются...и т. д.

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

thyknow

Спасибо сэру Дэвиду за ваш ответ,Да, у меня есть таблица настроек смены/ расписания смены, но моя цель-вывести один идентификатор смены, это мой оператор select, который использует эту функцию

с cte1 в качестве (
выберите функции row_number() над (приказ по восточному времени.Идентификатор emp_id) как параметр rownum' , ДВ.Идентификатор emp_id,
Конвертировать(тип varchar(50), бросания(по восточному времени.DatetimeLogin как дата)) + ' - ' + конвертировать(тип varchar(50), литой(ДБО.fn_GetLogout( по восточному времени.Идентификатор emp_id, Эт.DatetimeLogin, Эт.Имя_компании) как дата)) как нужные журнал',
Эт.DatetimeLogin, ДБО.fn_GetLogout( по восточному времени.Идентификатор emp_id, Эт.DatetimeLogin, Эт.Имя_компании) как "выход", Эт.[Первый прорыв], et.[Первый прорыв], et.[Второй перерыв], Эт.[Второй прорыв], et.LongBreakIn, et.LongBreakOut,
и.Отбреакин и др.OTBreakOut, et.PayrollDate,
Функция isnull(ДБО.fn_GetShortBreakTardiness(по восточному времени.[Первый прорыв], et.[Первый прорыв]),0.00) как "опоздание на первый прорыв" ,
Функция isnull(ДБО.fn_GetLongBreakTardiness(по восточному времени.LongBreakIn, Эт.LongBreakOut),0.00) как LongBreak опоздание' ,
Функция isnull(ДБО.fn_GetShortBreakTardiness(по восточному времени.[Второй перерыв], Эт.[Второй прорыв]),0.00) как "опоздание на второй прорыв" ,
Функция isnull(ДБО.fn_GetOTBreakTardiness(по восточному времени.Отбреакин и др.OTBreakOut, ed.OTBreak),0.00) как "опоздание OTBreak" ,
Приведение((функция datediff(минуты по восточному времени.DatetimeLogin, ДБО.fn_GetLogout(по восточному времени.Идентификатор emp_id,Эт.DatetimeLogin,et.Company_Name))/60) as int)/60 as 'рабочее время' ,
0.00
как "опоздание" ,
0.00
как "сверхурочная работа" ,
isHalfDay, isUndertime, isRestDayDuty, InOutOnly, isDipping, ed.Отбрейк
из Emp_TimeLogs et
внутреннее соединение Employee_Details Эд по восточному времени.Идентификатор emp_id = Эд.Идентификатор emp_id
где CAST(et.Date_Time as date) между @datefrom и @dateto и et.Company_Name = @comp --and Processed = 0

),
cte2 как( выберите Convert (int и ДБО.fn_ComputeShiftCode(литой( электронная.DatetimeLogin как раз), бросание(электронная.Выход как раз))) as 'ShiftCodes',* from cte1 e),
cte3 as ( select dbo.fn_GetShiftName( ShiftCodes, @comp ) as 'ShiftName', * from cte2)
--вставить в таблицу 6
вставить в @table6(shift,shiftcode,rownum, Empid, logdate, login, logout, breakinAM, breakoutAM, breakinPM, breakoutPM, longbreakin, longbreakout, otbreakin, otbreakout, payrolldate, AMtardiness,
LongBreaktardiness, PMtardiness, OTTardiness, totalworkinghrs, опоздания, сверхурочные работы, isHalfDay, isUndertime, isRestDayDuty, InOutOnly, окунать, otbreak)

выберите ShiftName,ShiftCodes,Rownum,Emp_ID,[Log Dates],DatetimeLogin,LogOut,[First BreakIn],[First BreakOut],[Second Breakout], [Second BreakOut],LongBreakIn,LongBreakOut,
OTBreakIn,OTBreakOut,PayrollDate,[FirstBreak опоздания],[LongBreak опоздания],[SecondBreak опоздания],[OTBreak опоздания],[работы],опоздания,сверхурочные работы,isHalfDay,isUndertime,isRestDayDuty
,InOutOnly,isDipping,OTBreak от cte3



это из моей хранимой процедуры