我有一个返回班次的表,换班了如果轮班超过2天,并且在其中一段时间里有公共假期,我想计算轮班公共假期的时间。结果将是列ph_day
SQL Server 2014
shift_starttime shift_endtime start_weekday end_weekday publicholiday shift_type start_time end_time end_time ph_day
shift_starttime | shift_endtime | start_weekday | end_weekday | publicholiday shift_type start_time end_time end_time ph_day2/01/2017 3/01/2017星期一2/01/2017 PHS 22:00 6:00 226/01/2017 27/01/2017星期四星期五26/01/2017 PHS 23:00 7:00 1
我想使用更新语句使用小时数来更新ph_day列。这个返回错误
更新tbl_employee_hrs
set [ph_day] = count('24:00')) - start_time)
其中shift_type ='phs'and(publicholiday = shift_starttime)
您可以尝试一下。
UPDATE Training.dbo.ShiftTable
SET PH_Day = (
CASE WHEN CONVERT(DATETIME, PublicHoliday, 103) = Shift_StartTime
THEN DATEDIFF(hh, CONVERT(DATETIME, PublicHoliday, 103), (CAST(Shift_StartTime AS datetime) + CAST(Start_Time AS datetime)))
WHEN CONVERT(DATETIME, PublicHoliday, 103) = Shift_EndTime
THEN DATEDIFF(hh, CONVERT(DATETIME, PublicHoliday, 103), (CAST(Shift_EndTime AS datetime) + CAST(End_Time AS datetime)))
ELSE 0
END
)